Search Results
| Search type | Search syntax |
|---|---|
| Tags | [tag] |
| Exact | "words here" |
| Author |
user:1234 user:me (yours) |
| Score |
score:3 (3+) score:0 (none) |
| Answers |
answers:3 (3+) answers:0 (none) isaccepted:yes hasaccepted:no inquestion:1234 |
| Views | views:250 |
| Code | code:"if (foo != bar)" |
| Sections |
title:apples body:"apples oranges" |
| URL | url:"*.example.com" |
| Saves | in:saves |
| Status |
closed:yes duplicate:no migrated:no wiki:no |
| Types |
is:question is:answer |
| Exclude |
-[tag] -apples |
| For more details on advanced search visit our help page | |
Results tagged with google-sheets
Search options not deleted
user 239790
For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.
1
vote
Generate random value from list if adjacent cell is empty
See Test sheet
Formula in C1 returns random value from list of items with no order number
=INDEX(
FILTER(A:A,B:B<>""),
RANDBETWEEN(1,COUNTA(FILTER(A:A,B:B<>""))))
FILTER() part filters items with n …
- 781
1
vote
Google Sheets: How to compare multiple rows/data ranges AND display the MATCHING/duplicate v...
See sheet "Duplicates" in File.
Cell A1 contains following formula:
=QUERY(
QUERY({Data!$A$2:$A;Data!$D$2:$D;Data!$G$2:$G},
"Select Col1, COUNT(Col1)
Group by Col1
Label COUNT(Col1) ''"),
"Select …
- 781
0
votes
Accepted
How to query a dataset while calculating cumulative values (and filtering by thresholds)
See Test File
Formula in Q2 returns check to threshold conditions, also I separated those conditions in table in range S2:U4.
Formula calculates aggregate shares for current row
SUMIF(SEQUENCE(COUNT($ …
- 781
1
vote
Sum of products in a single row
In File in cell C2 there is following formula:
=ARRAYFORMULA(
SUMPRODUCT(
FILTER($D2:$AA2,ISODD(SEQUENCE(1,COUNT($D2:$AA2),1,1))=TRUE),
FILTER($D2:$AA2,ISODD(SEQUENCE(1,COUNT($D2:$AA2),1,1))=F …
- 781
0
votes
Accepted
How to have a continuous calculation in google spreadsheets=
If you have to divide by the same number all the time simply go with power i.e. 666/3^6
If you also need all steps of calculation use this formula
=ARRAYFORMULA(42/2^(SEQUENCE(5,1,1,1)))
where 42 is …
- 781
0
votes
Accepted
How Can I Exclude Vacation Weeks From A Week Number Count?
See testSheet
You can put Week count start date in cell B1.
Then you can specify vacation start and end.
Idea is to calculate weeks omitting vacations.
For that purpose we use NETWORKDAYS.INTL() formu …
- 781
1
vote
Query is putting the result into the row below the formula—how to put in the same cell as th...
Query returns result with column name
Use this formula if you need only value in that cell
QUERY(Backlinking!A2:Z1000, "Select SUM(R) where F='performance management tools' label SUM(R) ''"))
or
= …
- 781
0
votes
How to do, If checkbox is checked it will uncheck the other checkbox?
You can use following formula for "In Progress" column
=if(C2=true,FALSE,true)
It will make only "Done" tick box active and "In Progress" depending on it.
- 781
1
vote
How to combine IFS and ISBLANK
Try simply to start with blank case as Google Sheet identifies it as less than 18
=IFS(C4="","",C4<18,"youth",C4>=18,"adult")
- 781
1
vote
Accepted
Problems with IF-OR-ISBLANK Functionality
There was a problem with brackets
={"Consultant Turn Around";
ArrayFormula(IF(OR(ISBLANK(J1:J4),ISBLANK(K1:K4)),,NETWORKDAYS(J1:J4,K1:K4,Data!B2:B)))}
However, it will return blank if there is at l …
- 781
0
votes
Rounding Negative Numbers in G Sheets
Use following formula:
=-ROUND(-A1-0.5,0)
And don't call it wrong, it's just different method of rounding.
- 781
1
vote
Accepted
Use sumproduct to produce text with row and column reference in Google Sheets
Error occurs as you are trying to multiply text values.
If you need to get respective data from Comment column use following INDEX and MATCH formulas.
=INDEX($A$1:$H$6,MATCH($B9,$A$1:$A$6,0),MATCH(C$ …
- 781
1
vote
Accepted
How to find corresponding cells in row based on lookup?
In Test Sheet
A10 is your "ABC" cell
and below find the formula that returns Values from columns 4,5,6 (or any other - just add)
=IFERROR(QUERY({Sheet1!$A1:$J6},
"select Col4, Col5, Col6
where Col …
- 781
0
votes
Sheets Chart with 5 data ranges - plots not lining up with X axis
In your file sheet test_chart1 contains a bit rearranged data so that chart could see all dates (2019 and 2020).
Sheet test_chart2 is variation of chart which contains all days of the year and shows c …
- 781
1
vote
Accepted
How can I score an assessment in Google Sheets where the answers to each question are weight...
Use following INDEX and MATCH formula:
=INDEX('answer-scoring'!$A:$G,
MATCH($A2,'answer-scoring'!$A:$A,0),
MATCH($C2,'answer-scoring'!$A$1:$G$1,0))
where index returns values from score table tha …
- 781