693 questions
Filter by
Sorted by
Tagged with
64 views

### How to shorten Excel OR function for many conditions?

As shown in the data, I want to determine whether a student passed (failing if s/he gets less than 40% in any subject). I can do this using many OR's inside IF. My formula: =if(or(b3/b\$2<0.4,c3/c\$...
14 views

### Clear direct formatting does not work, how do I treat the cell with a date in it as date and no as text

A cell that contains a date, 02/30/2020, is treated as text by my formula datedif from another cell whereas other cells with similar dates are treated as dates. Deleting the content of the cell does ...
• 132
64 views

### How do I create a formula that will count all the "x" in a list, then give me a total for amount paid based on amount of "x"?

I have a list aside for a staff party where we have a running total of amount paid at the bottom, and are indicating who is attending by placing an X next to their name. I would like it to be so that ...
54 views

### Get unique IDs with date 1 or date 2. For IDs with both date, obtain row with most recent of the 2 dates

I have been trying to retrieve the row in the following sheet (sheet1) where the dates are matching dates in sheet2 without duplicating the IDs in Sheet1 (Column B) Sheet1 Sheet2 In other words, I ...
181 views

### How can I evenly auto-distribute values across 12 columns in a spreadsheet so that their sums are as close to equal as possible?

I'm trying to distribute a bunch of annual recurring business costs across the span of 12 months so as to make each month's total expenditure as close to each other as possible. Doing this manually is ...
28 views

### Is there a formula for retrieving data from multiple cells on a different spreadsheet and adding to one single cell on another spreadsheet?

I am working on a project in which I need to retrieve the Parent Items for a list of scrap material. The problem is that most of this material is used in multiple Items. When I run a separate report ...
• 13
1 vote
64 views

### replace comma separated values in a sheet uses SORT formula to get sorted results from another sheet

I have a sheet called "Form Responses 1" connected with a Google Form, and I made another sheet/tab and used this formula: =SORT('Form Responses 1'!A2:M, 4, TRUE) to sort the responses with ...
68 views

### Can I have excel automatically swap text in a column from one word to another without using another column?

I'm fairly new to Excel so excuse my noobish question. I am working on a roster spreadsheet and have a bunch of shop codes, essentially abbreviations of the shop name in a column, and I have been ...
91 views

### Return First Column Header of Instance of String

I have a collection of speakers who have spoken various dates. Date,1/2/2023,1/2/2022,1/2/2021,1/2/2020,1/2/2019,1/2/2018 1st-Speaker,Jim,John,Fred,Sam,Scott,Riley 2nd-Speaker,...
• 113
1 vote
91 views

### How can I stop libre office sheets jumping when scrolling wide columns?

Long time sufferer of Windows, moved to Linux Mint around a month ago and I am still getting used to the changes. One very annoying thing that I have Googled, looked at the questions here marked libre ...
• 59
299 views

### Copy output from Google Colaboratory and Paste in Excel

Given a Colab notebook with the following cell url = 'https://files.grouplens.org/datasets/movielens/ml-latest-small.zip' extract_zip(download_url(url, '.'), '.') movies_path = './ml-latest-small/...
• 661
38 views

### Find stdev of values in col B where col A matches criteria

I have a sheet where column A is dates and column B is values. I want to find the stdev of values in B where the date in A is within the last 30 days. I am able to do sums with a sumifs which works on ...
• 101
99 views

### Can I use Excel to solve a quadratic based on a table of x and y values? (without using a graph)

I have the following data sets where the top bold row are my y-values and the following rows (%) are my x-values. Each row is a different set of x-values. I'd like to plug in 80% for x for each set ...
138 views

### How to set a whole row equal to a value, using ARRAYFORMULA() ? (in Google Sheets)

I would like to set the contents of all cells in a particular row to have the same value, sort of like having a banner that runs across the entire width of the spreadsheet. I'm working in Google ...
• 23
1 vote
290 views

### Turn cell references in Excel hyperlinks into the text in the referenced cells

I have a column of bare URLs in Excel. Say it's Column A. I want to replace each URL with a friendly text link, like "here." Suppose A1 contains the link https://www.website.com/content/...
• 23
43 views

### Excel opens at top of sheet only

How can I get Excel (using Office 365) to open where I left off (usually the bottom) and not the top of the sheet? I have a large spreadsheet and usually need to add new information at the bottom. ...
• 1
35 views

### How can I extract two or three "parallel" XPaths on a web page to/in a format that can be imported into a spreadsheet?

How can I extract two or three "parallel" XPaths on a web page to/in a format that can be imported into a spreadsheet? Here is an example: on https://www.amazon.co.uk/s?rh=n%3A20606777031&...
• 726
80 views

### Spreadsheet: View the entire (long) text of selected cell

I am not using spreadsheets very often and I am not sure whether this is a feature of common software (I'm using libreoffice calc). I have a spreadsheet in which 50 to 200 word texts are entered in ...
• 1,030
1k views

### convert excel spreadsheet with format to markdown

I want to convert a spreadsheet table with basic formatting (bold, script) to MD (markdown) format. I know howto convert a table to Markdown but it losses the basic format and i need to preserve it. ...
• 428
63 views

### Sequential numbering of codes

I have a sheet where as I enter details of a product, an internal 4 character code is generated...typically 4 letters (e.g. ABCD or BADC). This is just based on simple VLOOKUP and CONCAT functions. ...
• 1
1 vote
68 views

### reference range in different sheet with Indirect column and row

I'd like to reference a range in a separate worksheet using indirect relative row and column notation, something like this: =INDIRECT("'dataset'!R[0]C1:R[0]C4", FALSE) instead of =dataset!a1:...
• 113
107 views

### How to stop Excel from combining Conditional Formatting rules into 1 broken rule

I understand that Conditional Formatting is a horrendous tool, much like Excel itself, but it's the only realistic option so I'm stuck with using it. However, I'm unable to grasp how painfully stupid ...
• 144
1 vote
78 views

### What's the mathematical, =SUM() equivalent of the =NPV() formula, in Excel or Google Sheets?

Is there an Excel/Google Sheet SUM formula equivalent to the NPV formula, ie. one that produces an exact same answer given the same input figures? The question might be similar to these ones but the ...
42 views

### Concatenate data (matrices) in separate sheets (Gnumeric)

I need to "concatenate" data from two sheets into a third sheet. I need to do this in Gnumeric, but presumably it should be doable with other spreadsheet programs as well. Sheet 1: | A ...
• 781
1 vote
65 views

### Drag to fill duplicates values

I want to create a sequence of dates that populate a column. A 2023-12-31 2023-12-30 2023-12-29 ... Format Cell is set to Date. Calculation Options is set to Automatic Enable fill handle and ...
• 363
79 views

### Find next cell within column that is a number, starting from reference cell in another sheet

I think this sounds simple but I'm having a bit of trouble figuring it out. I have 2 sheets, the Sample Sheet attached and an Input Sheet. In the Input Sheet, I input one of the green shaded values (...
12 views

I have a spreadsheet. I want to create separate spreadsheets for each of the person listed on the column C that pulls new rows from the Master Sheet, only when their name appears in column C. So the ...
• 1
1 vote
53 views

### Custom Information Message for user based on another cell value

I am busy with an optimization spreadsheet which needs to display an information message, maybe similar to the Input Message for data validation (but I am open to suggestions). This message will be ...
• 21
16 views

### Dynamically changing content of a cell dropdown for validation because of a huge list

I have a huge lookup list of items, each with a conversion factor that I wish to look up. The lookup list is too big to be practical for a validation dropdown - this is how it looks: Here's the ...
• 188
40 views

### Spreadsheet: store multiple entries for one "field" per row

I have a spreadsheet that inventories our sheet music collection with multiple fields like title, composer, etc: Octavo # Title - original language Composer Arranger Editor Poet Translator Voicing ...
50 views

### Print Excel Spreadsheet using Template?

So Let's say I have a spreadsheet No Name Class Gender 1 Jack Science Male 2 Rose Science Female 3 Kevin Social Male 4 Olive Social Female and I have template No Name Gender 1 2 3 4 How to ...
1 vote
62 views

### How to draw empty cell values in LibreOffice Calc

my problem is the following (didn't find info how to do it in the official documentation) - recorded ~108k ping results to LibreOffice table with columns DATE (letter A), LATENCY (letter B), STATUS (...
• 177
36 views

### Microsoft excel formula to connect codes of smaller areas with those of the bigger ones and sum up their values

I am working with geographic information and I have bigger areas called Regso that concist of several smaller areas called Deso. The data I have is for the smaller Deso areas but I would like to ...
• 103
28 views

### Check row for a specific string and get the value of a column cell where the initial parameter was located

I am trying to create a calendar checker that checks if a value is detected on a specific date and return the month it was detected at: In the image I am looking for the string "FN" ...
44 views

### How can I combine two Excel formulas into one?

In a spreadsheet, I have five columns (total) and four of them have the components of the students' grade. The fifth has the final grade. Now, I need to combine two formulas so that when the four ...
• 13
5k views

### Formula to see if a series of numbers is increasing in value?

I have a spread sheet with thousands of sets of numbers. That is too say, there are ten columns and each column has a number - and there are thousands of rows of these numbers. What I want to do is ...
• 225
33 views

### Excel - The most spending occurred around this date

I feel like as a human I can understand this, but I can't figure out how to get it into an Excel formula. Given the dates and amounts spent on those dates below, how can I find a date on which the ...
35 views

### How can I copy a larger number of cells into a smaller number of cells that are already feeding a graph?

I have two 10-cell columns that currently feed a graph (one for the x-axis, the other for the y-axis). I have now doubled the discretization of my dataset, so instead of the original 10 data points, I ...
263 views

### Excel: How to count how many times a consecutive text value appears

I'm making a work schedule and I have to pay attention to a specific rule that, no worker should be scheduled to work more than 6 days in a row. I got this spreadsheet where I make those schedules but ...
1k views

### Excel: How To Transpose Every Random amount of N Rows From One Column To Multiple Columns?

I have a set of multiple choices questions with ABCD as answers, all answers are stacked on a column, but not every question has the same amount of answers. Instead of transposing every nth value, my ...
1 vote
286 views

### How can I find which cells in a column (or at least the first one) are greater than a threshold in a LibreOffice spreadsheet.?

I have a LibreOffice spreadsheet (actually a CSV file open in spreadsheet). It has several columns. Something like this: 1,3,4 2,4,6 1,3,4 10,3,4 2,4,5 0,3,4 -10,3,4 I want to find in a column (say ...
• 824
307 views

### How to highlight all cells that contain formulas

In LibreOffice Calc, I want to be able to quickly distinguish between cells that contain input data and formulas. Enabling "View → Value Highlighting" changes the font color in cells such ...
• 209
318 views

### Automating a repeating pattern across multiple worksheets

TL;DR: I need to automate a cyclical pattern that repeats over intervals that span blocks of cells across multiple sheets. So... I have a spreadsheet that I maintain for our work group that we use for ...
• 400
39 views

• 11
354 views

### How can I convert time values with a letter suffix like (30m, 2h, 30s) into an actual time format?

I have decimal values followed by a letter to indicate the time unit (hours/minutes) values like the following: 60m 1.5h 15m 20h How can I convert these to a numeric value of minutes or time value? ...
307 views

### Parse dates like "20220928" in Excel

I have an input column which contains an id + datetime: id12345!20220926_100911 My goal is to get the date and I have already successfully extracted the date into text with a formula involving LEFT, ...
• 1,748
1 vote
79 views

### More advanced filtering with text in Excel

I would like to filter columns in Excel with use of Text Filters based on multiple criteria in text search. Is it possible? ] ] how I would like to filter it based on words "medium" and &...
• 11
29 views

I have an excel sheet having 4 different spreadsheets. I need description of column header of three spreadsheets in 4th spread sheet. Example: Suppose I have sheet 1 with 6 headers for 6 columns, ...
• 11
1 vote