New answers tagged

0 votes
Accepted

Google Sheets - Is there a way to filter values but keep them in the respective rows they came from?

Try: =ARRAYFORMULA(IF(C2:C="Sample1",B2:B,)) We are essentially saying: if C2:C is equal to "Sample1" return the corresponding value in B2:B, otherwise, leave the cell blank.
user avatar
  • 213
0 votes

Google sheet, from 2 lists to flattened table

The outcome is obtained by an SQL CROSS JOIN: The Formulas by Top Contributors Addon provides that. Disclaimer: I've created and maintain the Addon
user avatar
0 votes

Google sheets function to filter out lowest associated cells

My sheet. First column uses: =UNIQUE(A:A) Second column uses: ="test" & SORT(VALUE(REGEXREPLACE(FILTER(B:B,A:A=G1),"test","")),1,FALSE) To quickly explain, the first ...
user avatar
  • 303
0 votes
Accepted

Google sheets function to filter out lowest associated cells

Delete everything in your sample sheet's Sheet1 Cols D and E. Then place the following formula in D1: =ArrayFormula(SORT({UNIQUE(FILTER(A:A,A:A<>"")),IFERROR(VLOOKUP(UNIQUE(FILTER(A:A,...
user avatar
  • 4,442
1 vote
Accepted

Google sheet, from 2 lists to flattened table

See my comment to your post above for a tip regarding future posts. That said, suppose that your "A" from the first image is in cell A2 and your "1" from the first image is in cell ...
user avatar
  • 4,442
-2 votes

Can I have a drop-down list appear in a cell only if another cell contains a specific value?

Sounds like you are looking to create a dependent drop down list. If you are, this video will help you create what you are looking for: https://trumpexcel.com/dependent-drop-down-list-in-excel/
user avatar
  • 1
1 vote
Accepted

Match value of cell if in between range and text match

You can now check out the sheet: It is possible but you have to make some changes to the data placement for it to work. Also the number should not have the string '£', but instead apply a number ...
user avatar
  • 136
0 votes

Google sheets/Excel: Rolling average for the past three months on different sheets

Choose Insert > Sheet, name the new sheet Helper, and put this { array expression } in cell A1 of the new sheet: ={ indirect('My report sheet'!AD3 & "!A3:K"); indirect('My ...
user avatar
  • 5,864
-1 votes

COUNTIFS in Google Sheets

below the formula gives a calculation of 22 but should actually be 16. i am definitely still doing something wrong. it first gave me an error "Iterative calculation" so i switched on under ...
user avatar
1 vote

COUNTIFS in Google Sheets

Isn't that difficult ! :) Try: =COUNTIFS(A2:A,">="&B2 , A2:A,"<="&C2 )
user avatar
  • 570
0 votes
Accepted

What formula can I use in order to calculate Z-Score within a specific rolling range?

The first thing to do is to make a column containing the relevant year for each team, which is stored in the first four characters of your "Team" column. You could use =VALUE(LEFT(A2, 4)) ...
user avatar
0 votes

Simplify formula that checks student year level and mark to a letter grade

link to my sheet. I basically used VLOOKUP on its true state. According to the Documentation: If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned. If ...
user avatar
  • 303
0 votes

How do I automatically record finish position?

Instead what you can do is, you can enter the time at what time this task was completed After the entries are made, you can you the "=rank()" function to rank based on the time field Hope ...
user avatar
0 votes

How can I seed a sequence of random numbers in Google Sheets?

I found I needed persisted random numbers all the time. Turns out this is possible using only standard spreadsheet formulas. Wrote the method up in this tutorial (it's similar to the one suggested by ...
user avatar
0 votes

How to make column go reversibly blank with custom validation and conditional format rules

You could do this by conditionally formatting column B, based on the values ​​in column D See if this sheet might suit you
user avatar
  • 570
0 votes

Extending a formula (VLOOKUP) to the whole column

First Solution: ARRAYFORMULA This modified formula should work: =ARRAYFORMULA(VLOOKUP(A:A;$B$2:$C$4;2;0)) Second Solution: Fill Down Select the entire column you wish this formula to be in, make sure ...
user avatar
  • 303
0 votes

How to add month in Sheets cell that corresponds with formula in below adjacent cell?

I don't know exactly what this part is for: (COLUMN (A1) -1) * 7 because I believe that instead of A1 you have to insert a range, however try with this formula which has the same reference =...
user avatar
  • 570
1 vote
Accepted

Formula to get the data from a source and use it while leaving some defined patterns as spaces

I'm not sure whether this answer your question, but I think this should do it. https://docs.google.com/spreadsheets/d/1QZJODzDyA8RgM_Skh0Kor7O1r4-7dtmU1eROtAfIwzA/edit?usp=sharing =IF(MOD(ROW()-1,5) = ...
user avatar
  • 303
0 votes
Accepted

How do I automatically record finish position?

You seem to expect a spreadsheet to magically recognize the order in which "Win" was entered by the three users. That is not how spreadsheets work. The easiest solution would probably be to ...
user avatar
  • 5,864
0 votes
Accepted

Have imported spreadsheet from Excel to Sheets but this formula doesn't work

Both sum() and indirect() seem superfluous here. Use this simpler formula: =countif( { D6:D37, I6:I37, N6:N37 }, "peter" )
user avatar
  • 5,864
2 votes

SWITCH statement in Google Sheets Doesn't Compare Strings Properly?

SWITCH function in google sheets. The switch function uses the following syntax: SWITCH(expression, case1, value1, [case2, value2, ...], [default]) So remove your extra D2 in the function and it ...
user avatar
  • 303
0 votes

Create a dependent drop-down menu for an entire column

Drop-down lists can be made dynamic by setting their options to refer to a range that is filled with a spreadsheet formula, but it is usually easier to define the list programmatically with something ...
user avatar
  • 5,864
1 vote

Google Sheets: Sum till particular date

This is my try, hope I understand you well test sheet
user avatar
  • 570
1 vote

SUM Function Works Fine in One Sub-Sheet(?) but Not in the Other?

Column K looks like this: PA Score 14.625 14.985 - - - - The majority of the values in the column consist of the text string -. The query() function only allows one data type per column, and ...
user avatar
  • 5,864
1 vote
Accepted

Google Sheets: Sum till particular date

https://docs.google.com/spreadsheets/d/1jQ4CFfk0J0QjKLUmRYtQZms4Of9jaFDAeWNW56FeBF0/edit?usp=sharing My solution works by using SUMIF. I'm not sure whether you want that day to be included in the ...
user avatar
  • 303

Top 50 recent answers are included