Questions tagged [formulas]
Formulas used in spreadsheets edited by web applications like Excel Online, Google Sheets, among others.
1,839
questions
0
votes
0
answers
13
views
Array formula giving error of different sizes
I am trying to create a formula to count based on two different set of or conditions. If I remove the second array ({BB$3,BB$4,BB$5}), then the formula works but when I add the second condition it ...
- 1
0
votes
1
answer
24
views
Tournament table Google Sheets
I want to make a tournament table and I need to:
make A1 cell type amount of points based on score (B1 is player 1 score, C1 is player 2 score)
I want it to automatically detect wins, draws and loses ...
0
votes
1
answer
61
views
Google Sheets - efficiently aggregating one column for grouping, based on minimum value in another column?
I have a sheet with upward of 1000 rows with the following schema:
GroupCol | NameCol | NumberCol
Example spreadsheet (much smaller data)
In the dataset, any given value in GroupCol is repeated 1-10 ...
0
votes
1
answer
22
views
How do I make text in a column appear after the rows have been filled?
I have made this spreadsheet to help our mechanical workshop track labour. I have a problem on my spreadsheet where column K displays "follow up". The end users (technicians and foreman) ...
- 3
0
votes
0
answers
19
views
Calculating total number of VISIBLE checked (=TRUE) boxes in a range
I have a range of cells containing checkboxes that are manually ticked based on criteria met and applied to a total value calculation (TVC).
The current formula =countifs(C16:16,TRUE) worked for what ...
- 1
0
votes
1
answer
15
views
I Need Help with Formula
Cell A1 displays the current date, A2 is a date 6 months in the future. I need a formula for A3 to display A2 when A1 is 3 months away.
I'm new to making any kind of formulas, I have copied and pasted ...
- 1
1
vote
1
answer
73
views
sumif not working sometimes
I have read similar questions but unfortunately didn't help me.
Here is a copy (with sensitive info redacted) of my data.
Google Sheet
In column U and V I am trying to sum columns D to O if T is "...
- 13
0
votes
0
answers
37
views
Average value by highest sums
I'm having trouble with writing the logic for the formula
The results should be as above.
Markdown table
Closing TEU
Average per day
23/3/2022
62
no
120
24/3/2022
49
no
120
25/3/2022
168
48
120
...
1
vote
1
answer
30
views
Highlighting a single cell when partial text shows up in a range of other cells
I work for a physician office and we keep track of which patients are ready in a room and which doctor goes in to see that patient. We use a google spreadsheet to track this information. I want to ...
- 13
0
votes
1
answer
25
views
Conditional formatting in Google sheets to indicate overages
To help identify issues faster I am trying to add conditional formatting for column Z to turn red if the amount in that column is greater than the amount in the columns AA or AB and only if column AA ...
- 3
-1
votes
1
answer
49
views
Count all cells with specific data between certain dates
I have a schedule of a team planned on multiple projects on certain dates.
I'm looking for a formula that retrieves the amount of days (cells) on PROJECT-A until TODAY().
0
votes
1
answer
66
views
ArrayFormula COUNTUNIQUE
How would I turn this formula into an array formula so it automatically populated when a new row is created? =countunique(Z23:AC23)
I need the formula to count the unique properties in a row between ...
- 1
0
votes
0
answers
35
views
Referencing text in a specific cell in =SUMIFS formula
Very new to Sheets so forgive any non-clear terminology. How can I use text from one cell, as a criteria value in a =SUMIFS formula?
i.e.
=SUMIFS('2021 Data'!D:D,'2021 Data'!B:B,"ABC Florist*&...
- 1
0
votes
0
answers
7
views
Formula for - Throughput (copy per hour) totals, from start finish times and total copies
I am struggling to find a formula to give me total copies per hour throughput from the times on this sheet with no decimals. I cant change the format of the time as the sheet is locked in that format.
...
- 1
0
votes
1
answer
23
views
How to write multiple IF statements in 1 cell based on multiple dropdown choices?
L16-L19 are Yes/NO dropdowns
How can I use multiple IF statements in L20?
For example:
yes yes yes no, “proceed”
No no yes yes, “don't proceed”
Yes no yes yes, “cannot move forward”
- 1
0
votes
2
answers
26
views
How to create Google Sheet app that retrieves a mix of random words
I have a Google Sheet with a lot of columns, each of them containing words from a category. I would love to create a formula that can retrieve random words and put them together.
For example:
Colum 1 [...
- 1
1
vote
2
answers
57
views
How Google Sheets ArrayFormula consider other column
In Row 1 of this sheet I have formulas like =ArrayFormula(sum(if(and(not(ISBLANK(C$4:C$49)),$A$4:$A$49>=$A$1),1,0))).
What am I doing wrong?
How can I change the formula such that the values in ...
- 602
1
vote
2
answers
40
views
How to sort a list-like text in a cell?
Suppose I have in a cell the following unsorted list-like text:
- VAL03
- VAL04
- VAL01
- VAL05
- VAL10
- VAL07
- VAL08
- VAL06
- VAL02
- VAL09
I would like to sort that list-like text. That is, in ...
- 113
0
votes
1
answer
36
views
Allocate an amount in one cell amongst or between several cells based on the value of each of the several cells
Here is the link that relates to my question:
https://docs.google.com/spreadsheets/d/1dnWuREv3Rf2EWyDQDnyHyZWZ8ItbyKt_EfxPQwWhTnA/edit?usp=sharing
Cell C13 is a Retirement Annuity contribution of my ...
- 1
0
votes
1
answer
47
views
Google Sheets – Dynamic Calculation of Data Validation
My use case is to select between two times within a single day.
The first cell ("from" time) has list of items data validation set up, where the options are as follows:
12:00 am,1:00 am,2:...
0
votes
1
answer
39
views
Can I use SORTN to pull top 5 values from multiple subsets of data at one time?
I have subsets of data for stock symbols combined onto one spread sheet. Symbols are Column A and Performance is Column B. I want to choose the top 5 symbols of each subset based on performance, and ...
- 1
0
votes
1
answer
17
views
Google Sheets: Is it possible to retain an IF/ELSE formula *AND* enable custom number entry into a cell?
I'm using the IF/ELSE formula in a cell:
=IF(logical_expression, "TRUE", "")
My use case is the following:
If the logical_expression is TRUE: A logic-derived value is shown in the ...
0
votes
1
answer
22
views
Google Sheets link row [duplicate]
I am trying to set up a project tracker for my team at work to use. We want to have a Master sheet that contains all of the jobs, and then have individual sheets for each member of the team where ...
0
votes
1
answer
23
views
How to fill in cells with data from two separate cells?
I have a big Google Sheet and I need to do something like this:
x A x B x C
A AA BA CA
B AB BB CB
C AC BC CC
What formula would allow me to copy the info from the above and right cells into one cell?
(...
0
votes
0
answers
16
views
Changing Future Monthly Sales Goals Based On Previous Monthly Sales
What I have is a spreadsheet which needs to calculate future monthly required sales goals, based on an annual sales goal, and previous monthly sales.
EXAMPLE: My annual sales goal is $1,200,000. This ...
0
votes
1
answer
164
views
Add Check box in Google Sheets based on value of another cell
What formula would I use in Google Sheets to insert a check box into cell A2 if the value of cell B2 is "Signing"?
I only need the check box if B2 is signing
I can get column A to have all ...
- 1
0
votes
2
answers
30
views
Extract original text from cell with LEFT formula
I'm using the LEFT function like this:
LEFT("Lorem Ipsum", 5)
Is it possible to get hold of the original text ("Lorem Ipsum") from a cell in which the formula above is used? If I ...
- 167
0
votes
1
answer
15
views
How to import data from one sheet to another based on 2 cell criteria
I am trying to manage my sheet by using filter formula using 2 criteria is this possible or do I do I need to use another formula.
what I am using is
=Filter('Form Responses 1'!B2:U,'Form Responses 1'!...
- 1
1
vote
1
answer
433
views
IMPORTHTML stuck on loading
Here's my problem, I've got an html page from my own web site which contains a lot of tables. I'm using IMPORTHTML in Google Sheets to import some of them in different spreadsheets. There's 4 ...
0
votes
1
answer
21
views
How to make an alternating value in a single cell?
I want to make a cell swap between two values every couple of seconds, but I don't know how.
EX: A1=1, then a second later A1=0, then a second later A1=1, and it repeats like that, does anyone know ...
0
votes
0
answers
17
views
Given a table of csv values is it possible to receive suggested formulas? Alternatively is it possible to "solve" a complicated formula given a table?
i.e. Given the csv table with A1=1,A2=2,A3=3,A4=4,B1=10,B2=5,B3=10/3,B4=2.5,C1=0.48,C2=0.24,C3=0.16,C4=0.12,C5=1
Row
A
B
C
1
1
10
0.48
2
2
5
0.24
3
3
0.333
0.16
4
4
2.5
0.12
5
1
Only A1:A4 are ...
- 1
0
votes
1
answer
37
views
I am trying to find a custom function to find the min value of coloured cells ONLY for a range where some cells have a specific color
I am trying to find a custom function to find the min value of coloured cells ONLY for a range where some cells have a specific color.
The best I could find is to count the cells with a certain color.
...
- 1
0
votes
1
answer
31
views
Tie value from one cell to value of another
I need to find a way to assign the value of the highlighted portion to the value of the cell to the left of it, so that I can drag the url down and autogenerate the values. Is there a way to assign ...
0
votes
1
answer
30
views
How to find duplicate entries based on 3 different columns in Google Sheets
I have this dummy sheet with data in Columns A through G. I have colour coded it so I can reference it in my explanation below:
In column H I want to to show the word "Duplicate" whenever ...
- 7
0
votes
1
answer
25
views
Conditional Formatting in Google Sheets doesn't work in certain cells with custom formula
Custom Formula doesn't work on certain cells.
A little background, I am making a timezone sheet for a discord where someone input the time they wanted and checks who is and is not available at that ...
- 1
0
votes
1
answer
45
views
I need a formula to reverse the order of a column of variable length in Google sheets
The formula and custom function (created by doubleunary here) below repeat rows in an alternating pattern the number of times given in Column B.
=arrayformula({RepeatRows($A$2:$A$6, B2:B6); If(SUM(B7,...
- 3
2
votes
1
answer
84
views
Add iferror() for every error that occurs in sheet but keeps formula in this cell
I have a sheet with a lot of formulas. some of them gives me an error. I'd like to make something similar to ctrl+h and replace values but I'd love to find every cell that is giving an error and ...
- 23
0
votes
1
answer
19
views
What's the syntax when using a formula instead of a value as the logical expression?
My SWITCH function returns false when the logical expression is a formula in a cell.
Here's what works as expected:
=SWITCH(B3,12,"DEC","NONE")
output is DEC
where B3 contains the ...
0
votes
0
answers
7
views
Update a list based on data from corresponding cell
I've got a google form that updates a sheet for locker reservations, on the reservation sheet I have to manually add the locker number and assigned key status. I've got two columns on the main sheet '...
0
votes
0
answers
23
views
Duplicate formula not working
My formula "=COUNTIF($C$2:$C , C2) > 1" is not catching all the duplicates while also highlighting not duplicate values. I don't know how to fix it.
0
votes
0
answers
10
views
Google spreadsheet help for putting correct formula to automate operations
Your help can save my lots of time & energy,
I am new to google sheet & don't know much abount coding, i have made a google sheet to automate our therapy clinic operations by internet learning....
user291081
0
votes
1
answer
19
views
Formula for Daily Cash Balance Sheet
I have created a Google Form for cashiers to balance out their cash drawers at the end of each day. Currently I have the form set so that they only have to count the amount of each coin/bill and not ...
0
votes
1
answer
27
views
Special function in Google Sheet
I can't come up with a combination of functions (MATCH,LOOKUP,etc.) in Google Sheets that would return the row number of the cell that matches the given rule in the given range. Moreover, I need to ...
0
votes
3
answers
79
views
Google Sheets: keep track of days spent in different countries
I have prepared a Google Sheet that lists all flights I have taken in recent years, alongside the departing and arriving country:
Date & time
From
To
Outbound country
Inbound country
Days in ...
0
votes
1
answer
24
views
How to add a total from a column in another sheet if it falls between 2 dates
I am trying to add a total from a column in another sheet if it falls between 2 dates and it is not working. Any help would be so much appreciated!
Here is what I have:
=SUMIFS(’2022’!AB:AB,‘2022’!A:A,...
0
votes
1
answer
69
views
A Master Sheet / Template for Google Sheets similar to a Slide Master for Slides/Powerpoint
(updated to hopefully better explain the problem)
How do I create a Master Sheet with formulas that, whenever those formulas are updated, updates those formulas in sheets made by copying it. So ...
- 1
1
vote
1
answer
63
views
Google Sheets UNIQUE not removing all duplicates
I have a table in which the headers should be all the unique start and end times that are in another table. However, when I use the UNIQUE function, the result still contains duplicates. Does anyone ...
- 13
0
votes
0
answers
17
views
What formula do I need to use to determine the % of events someone has attended since their initial date attending?
I have a spreadsheet with 2 columns; A has names and B has the date they attended the event. I created a pivot table with the data; the rows have the names and the columns have various date data.
...
- 1
0
votes
1
answer
51
views
Automatically apply a formula and a when a new row gets added via a form API
Hi I have a wordpress form, linked to a google sheet that transfers over entries as they come in, now I have some formulas applied, but I don't want to have to add the formula to each new row that ...
- 3
0
votes
1
answer
47
views
Averaging win/loss data by day in Google Sheets
There's been similar questions answered here but I've been unable to apply those tips to my scenario. I keep data on gambling wins and loses. I want to calculate averages based on the day of the week. ...
- 3