Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,840
questions
0
votes
1
answer
8
views
Google Sheets formula to format substrings of column concatenation?
If I have a spreadsheet with columns for author, year, title, publication, etc, could I add a column that concatenates and formats those as a citation for a bibliography?
It would be something like ...
- 255
1
vote
1
answer
13
views
Regexextract to get a text in the middle of string
SAMPLE SHEET
I have data in a column in this format:
1 P01 - Product Name here - PAID IN FULL
2 P02 - Product2 Name here - PARTIALLY PAID 20
1 P03 - Product2 Name here
I've been trying to ...
- 191
1
vote
1
answer
8
views
How to return a value from a row from the last column a text appears
I am having problems returning the correct value i need.
I enter data on this table.
And i want to return some values on the other table.
In that second table i want to return the last date his name ...
- 11
0
votes
1
answer
19
views
How to find the next lowest available number from a list on Google Sheets
I'm looking to find a formula which I can use, to show, the next lowest available number for a range of Asset Tag 1 - 500
As an example if 1 has been used the next available number will be 2.
However, ...
0
votes
1
answer
20
views
Combine an array formula with query function
I want to know if there is a way that I can combine any of these 3 formulas.
Sample Sheet
I have in col. A3, this extracts the info in Data tab, and splits into rows: =arrayformula(substitute(...
- 191
1
vote
0
answers
12
views
Why 2 columns of the same value give different total?
Sample Data in Result Sheet
I have a formula in A2 that extracts data & splits it into unit & product (A & B).
I used the SUMIF formula to give the the total of each separate set of ...
- 191
0
votes
1
answer
19
views
Why is COUNTIF returning a 0? It should be returning a value of 8
I have a fairly simple countif function that counts how many items are 90 days old or more.
=COUNTIF(PROCFilter!H2:H,">=90")
But all this is returning is a value of 0, when I know that ...
- 1
2
votes
1
answer
254
views
Text result of JOIN is longer than the limit of 50000 characters
Sample Sheet
I want to split a column of data, where there are multiple lines in a single cell. I want these split into rows with a formula.
I've got the JOIN formula set up for that and it works ...
- 191
0
votes
1
answer
24
views
How to make formula based off a withholding table?
I have been attempting to make a formula to easily calculate federal income tax rates. The picture attached is the withholding table that I've transferred onto a sheet. These are the thresholds I am ...
- 1
0
votes
1
answer
22
views
Is there a way to return more than one row from multiple matches in Google Sheets?
I'm trying to find a way to fill two columns in one table with two rows of data from another table based on matched criteria.
To be more specific, A3:D contains the data I'd like to pull from. I'm ...
- 5
0
votes
1
answer
42
views
Google Sheets: Making a function to find a number and impute it once
I am trying to make it possible to find the highest number under 30, and than place it in another cell. Then that cell will not change when another number becomes higher. For example if the highest ...
- 1
1
vote
1
answer
29
views
How to assign cell values based on options from another list
I am sure there is a simple formula for this but I have not been able to find a solution anywhere.
I am trying to fill a cell based on a list in a different tab.
In my main sheet, each row has a ...
- 11
2
votes
1
answer
21
views
Show sorted elements based on priority
can you please help?
I would like to autopopulate column C with values in column B sorted by the priority in column A.
Column A: Project names manually ordered by priority. Most important in top.
...
- 23
0
votes
0
answers
24
views
Google Sheets - Autosort Script issues
I am trying to sort data descending based upon column B (2) which has different numbers in each cell. EG: B8 = 99, B14 = 70, etc. I'm intending for it to autosort and do it onEdit. I'm not good at ...
- 1
0
votes
1
answer
32
views
Is there a way to create a list of values from a certain column based on referenced criteria in two rows?
I'm trying to find a formula that will fill in two columns with data from another sheet based on values from a dropdown that correspond to header rows from the original dataset.
I have Sheet1 that ...
- 5
0
votes
0
answers
8
views
Sum by Date but Ignore Timestamp
I am trying to auto sum day by day as data is automatically added to the sheets doc via Google Forms but keep getting errors and I think the time stamps are throwing the calculation off, so I'm trying ...
- 1
1
vote
0
answers
13
views
Formula to automatically sort range of data into groups in different columns by colour?
I am an educator trying to find a way to automatically group students into broad groups of 'below' 'at' and 'above'.
My data is more specific (i.e. numerical value between 0 and 30) and I would like ...
- 11
0
votes
0
answers
42
views
Sheets: Collaborators inserting their names as values into selected cells
Previous post marked closed due to being unfocused, so I pared it down. Not sure if this is now good enough, if not please can someone comment to explain so I can correct it?
I'm trying to find a way ...
- 1
0
votes
1
answer
15
views
Counting different types of text and producing specific text outcome
So I'm trying to create a tracker where I can gauge a pass/warning/fail status for each category. Under "Report Status", I wanted a function that can put out the text "Fail" if one ...
1
vote
1
answer
41
views
How to make a formula expand to new rows when new data is added?
I've got a set of data, a column of numbers in this case, that I want to automatically calculate moving averages for when new data is entered. Currently I'm doing this by using =TRIMMEAN($A$2:A2, 0.15)...
- 25
0
votes
1
answer
22
views
How to create a linked sheet that checks a box for a day if found and inserts text from another column
I'm trying to create a sheet in Google Sheets for my team that shows me what runners have run on what day of the week. Their activities are automatically imported via Zapier with dates from Strava ...
- 1
0
votes
1
answer
22
views
Flattening the values (not formulae) of a summary sheet
I work at a company that owns a lot of film gear that they bring on freelance shoots. I'm trying to create a simple, streamlined way for them to notate what's brought on each shoot so that they don't ...
3
votes
1
answer
126
views
Formula for matrix (dates in descending order)
As a sequel to this question Formula for matrix (dates in ascending order) how would the formula (=IF($B2="Apple",A2-SORT(FILTER(A:A,B:B="Pear", A:A<$A2),1,FALSE),"") ...
- 199
3
votes
2
answers
127
views
Formula for matrix (dates in ascending order)
Firstly, apologies for this question but I couldn't really wrap my head on how to do this (ultimate aim is to put it in app scripts)
Let's say I have a table that is updated daily showing the fruit ...
- 199
0
votes
0
answers
18
views
How to sum a column of variable length, starting at the first cell below the nearest blank cell
Can anybody help with this please?
I need to sum a column of figures. The number of rows in the column will be variable, but will be a contiguous range, with no gaps.
The range for the sum will start ...
0
votes
1
answer
18
views
Google Sheets - Is there a way to filter values but keep them in the respective rows they came from?
I'm looking for a way to filter values based on another column but keep them in the same rows they came from. Here's an example I created manually of the correct output:
The goal here is to have a ...
- 25
1
vote
2
answers
32
views
Google sheet, from 2 lists to flattened table
I have two independent columns with data.
How could I use that data to then produce a flattened table like this:
- 113
1
vote
2
answers
30
views
Google sheets function to filter out lowest associated cells
I was wondering if there's a way to use a function (maybe Filter?) in order to sort this data such that column K only contains one of each number, and the highest test associated with it in column L. ...
- 25
0
votes
1
answer
32
views
Match value of cell if in between range and text match
Desired outcome:
I would like to return a value in column C (column D is the values I'd like the formula to return) - the value in column C should return on the criteria it matches the product name in ...
- 75
0
votes
0
answers
21
views
Why does my formula stop working after 1000 rows in Google Sheets?
I am working in Google Sheets and have the following formula:
=ArrayFormula(IF(ROWS(A$5:A5)>$C$2,"",INDEX('Annual Schedule'!$A$1:J$1852,SMALL(IF(('Annual Schedule'!$A$1:J$1852>=$A$2)*('...
- 1
0
votes
2
answers
29
views
COUNTIFS in Google Sheets
I have been battling to get the COUNTIFS formula right for the below. I need to count the number of events per day. How many times did the event happen between 2021-04-20 14:24:54 and 2021-04-20 19:16:...
0
votes
1
answer
38
views
What formula can I use in order to calculate Z-Score within a specific rolling range?
Doing some sports statistical analysis for fun. Wonder if any of you guys can help me.
This is the sheet I'm working with. I'm sure you can see how I'm already experimenting with this in the Z-Score ...
- 3
1
vote
1
answer
20
views
Simplify formula that checks student year level and mark to a letter grade
I am writing a Google Sheet that checks a student's year level, then assigns a letter grade based on their mark in an assessment. If there is no mark, it is designed to leave the cell blank.
The ...
0
votes
1
answer
17
views
How to make column go reversibly blank with custom validation and conditional format rules
I was able to use scripts editor to make some conditional formatting. Would like the option of covering up the answer column, column B reversibly. Would love some help with a script or how to do this. ...
0
votes
1
answer
15
views
Extending a formula (VLOOKUP) to the whole column
I created a VLOOKUP formula inside a Google Sheet, I have 130000 lines and need to extend the formula to the end of the column.
- 1
0
votes
1
answer
24
views
How to add month in Sheets cell that corresponds with formula in below adjacent cell?
I am making a cash flow projection worksheet in Google Sheets. I am using this formula to give me sequential weeks (greatly relieving the tedious entry)
=ARRAYFORMULA(JOIN("-",TEXT({"5/...
0
votes
1
answer
23
views
Formula to get the data from a source and use it while leaving some defined patterns as spaces
I'm trying to create a formula so that Column E will output the Employee names on Column A. I've provided an image below as my example. As you can see, there is a pattern of 4 vacant after every ...
- 381
0
votes
1
answer
23
views
Have imported spreadsheet from Excel to Sheets but this formula doesn't work
I have text in three columns and need to know how many times a particular name crops up
=SUM(COUNTIF(INDIRECT({"d6:d37","I6:I37","n6:n37"}),"peter"))
any ideas?
0
votes
2
answers
34
views
How do I automatically record finish position?
I've created a spreadsheet to keep track of the progress that my friends are making in a competition we're doing. I would like google sheets to automatically update which one of us comes in 1st, 2nd, ...
1
vote
1
answer
29
views
SWITCH statement in Google Sheets Doesn't Compare Strings Properly?
I have a very simple SWITCH statement in Google Sheets:
=switch(
D2,"",B2,
D2,"A","A",
D2,"B","B",
"Default"
)
If I put ...
0
votes
1
answer
33
views
Create a dependent drop-down menu for an entire column
The aim: For my work, I would like to create a google spreadsheet where we can document our errors in processing the order transactions. For this I need two drop-down menus; the first shows the ...
0
votes
2
answers
33
views
Google Sheets: Sum till particular date
I have time-series data that follows no predictable entries. For example as below.
Date
Units
01 Jan 2022
1
05 Jan 2022
2
08 Jan 2022
1
30 Jan 2022
1
04 Feb 2022
1
10 Feb 2022
1
13 Feb 2022
2
...
- 105
1
vote
1
answer
15
views
SUM Function Works Fine in One Sub-Sheet(?) but Not in the Other?
I have a SUM function that works fine in one sub-sheet(the tabs default titled sheet1, sheet2, etc. on the bottom of the screen) but decides not to work in the other. This is what the function looks ...
- 11
0
votes
0
answers
57
views
Help with SUMIFS formula with horizontal and vertical criteria + REGEXMATCH
I need some help trying to sum totals for different events based on horizontal and vertical criteria. I've tried doing different combinations of SUMIFS + INDEX + MATCH formulas, including transposing ...
0
votes
1
answer
27
views
"Multiply" (Concatenate) Text Columns in Google Sheets
Simple question that I can't seem to find a solution for. I have text data in two columns like
Name
Num
a
1
b
2
and I would like to produce a single formula if possible to output a 3rd column
...
- 1
0
votes
1
answer
41
views
Google Sheets - countif with dynamic range
In sheet1 have a column with a list of IDs, each with a set of statuses:
ID Apr May Jun
1 Load Load Complete
2 Load Complete NA
3 Load Load Load
In a separate sheet, sheet2 I ...
- 101
0
votes
0
answers
6
views
I need help how to calculate estimated hours spent per article by the total number of working hours and total numbers of articles completed
This is what my sheet looks like.
I need to get the average mins spent by the editor per article. I have the total number of working hours and the total number of articles completed.
I hope someone ...
0
votes
1
answer
20
views
How can i make a formula to determine payment types
I am having trouble nesting and using multiple criteria. I want a column to determine if payment type was CASH, CHECK or WAITING depending on if the cells for Check# and Payment are containing data.
...
0
votes
0
answers
14
views
How to trace cells from a formula when the formula uses cells form a different sheet?
I have a complicated budget spreadsheet which I update regularly. I have one sheet which is essentially just my bank ledger where I categorize all transactions & purchases. And then I have another ...
- 1
0
votes
1
answer
28
views
How do I find the appearance rate of one list of cells in another list of cells?
I imported all of the data from my iTunes library into a Google sheet for my own amusement, and I want to know by album how many songs from each album I thought were good enough to put in my "...
