New answers tagged

0

Since you only have 8 entries, I didn't go for a column-length array formula. I replaced your C3 formula with the following and dragged down: =ArrayFormula(SUM(QUERY(TRANSPOSE({$D$2:$2;D3:3}),"Select Col2 Where Col1 > date '"&TEXT(TODAY()-14,"yyyy-mm-dd")&"'"))/10) What this does is form a virtual array between the ...


0

You import data from external Google spreadsheets using IMPORTDATAand use FILTER to combine the results. However you get an #VALUE! error message ("In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.") if/when one or more sheet(s) contains no data. IFNA works well when there is a single set of data, however it fails when ...


1

See sheet 'INVOICE' in File. Since cells B:D are merged, there are 2 formulas in B15 and E15 that return Product name and quantity with price for customer specified in cell B10. =Query( {Arrayformula( Transpose( Split( Rept( Concatenate('Order Data'!$C$3:$C$9&","),5),","))), {'Order Data'!$D$3:$H$9; 'Order Data'!$I$3:$M$9; ...


0

See if this works for you: =IF((WEEKDAY(TODAY(),2)<=4)+((WEEKDAY(TODAY(),2)=5)*(NOW()-TODAY()<TIMEVALUE("9:30 PM")))=1,TODAY()-(WEEKDAY(TODAY(),2)-1),TODAY()+(8-WEEKDAY(TODAY(),2))) NOTES: The second argument of the WEEKDAY functions, which I have set to 2 each time in the formula, tells the formula which day of the week to consider "...


0

Try this: =if((WEEKDAY(today(),2))>=5,today()+8-(WEEKDAY(today(),2)),if((WEEKDAY(today(),2))<=4,today()-(WEEKDAY(today(),2))+1,"")) Explanation WEEKDAY(today(),2) - creates a number between 1 and 7; If the date is a Monday, then tit will return 1, Tuesday = 2, etc. if the day is greater than or equal to 5 (i.e. the day = Friday, Saturday, or ...


0

You can wrap your current formula with the INDEX function: =INDEX(QUERY(IMPORTHTML("https://shop.tcgplayer.com/magic/darksteel/echoing-truth?xid=pid0b5fc15-f2ac-4f33-ae86-a3ed6d38bb44","table",2),"select Col2"),1) Using =INDEX(...,1) you get the first row, while using =INDEX(...,2) the second.


0

while it does work to remove the quote marks so that the formula is now: =IF(G15=TRUE,1,0) It is better to change it to: =IF(G15,1,0) The first parameter in the IF() function is evaluated as either being true or false. That evaluation determines which of the next two parameters is used as the value of the cell where the formula is located.


1

The correct formula is: =IF(G15=TRUE,1,0) That is, drop the quotes around TRUE.


0

The answer was simple: use the built-in functions ROWS() and COLUMNS(). Range | The needed formula | Needed | Notes | | formula | | | result | ----------------------------------------------------------------------- A1:C3 | =ROWS(A1:C3)*COLUMNS(A1:C3) | 9 | 3*3=9 A:A | ...


2

Use INDIRECT to reference a specific cell. You can write the sheet/cell references longhand in the formula, or build it using variables. Let's say that you have a sheet "Working", and a sheet "Main". "Working" has a new row inserted every month; "Main" must maintain a link to "Working" Cell E13 despite the ...


0

Here are two options. Note that it is advisable to treat the opening balance as a transaction in the "Credits/Deposits" column Assumption - "Total" is Column G Option #1: Enter these formulas in the nominated cells =F2 - enter in Cell G2. =G2-E3+F3 - enter in Cell G3. Then copy cell down as many rows as necessary Option#2: Enter this ...


0

You would need 3 distinct formula alongside some data readjustment Formula to calculate the daily average =QUERY(A2:F,"select avg(D) where A is not null group by A label avg(D) ''") Formula to calculate the day-of-week average =QUERY(A2:D151,"select B, avg(D) group by B label avg(D) ''") In this formula we return both the day of the ...


2

You can use the following formulas Closest minimum value =MINIFS(A2:E2, A2:E2, ">=" & G2) Closest maximum value =MAXIFS(A2:E2, A2:E2, "<=" & G2) Functions used: MINIFS MAXIFS


0

You have a series of Class sheets in which the student name may be formatted by the teacher. You also have a Master sheet where the names of the students in each class are linked by formula. Your goal is to apply any formatting from the Class sheet to the equivalent student/class on the Master sheet. There are many ways in which your scenario might be ...


1

The key is FORMULATEXT, which will return the formula used in a cell. =FORMULATEXT(A17) This value will then need to be parsed into a cell address. As the formula is simply a reference, removing the leading = is sufficient. RIGHT, in its ltr-centric way, can be used to get the trailing portion of a string, though this approach is a little verbose, as it ...


Top 50 recent answers are included