New answers tagged

0

I've realised there's a better way to determine if a time is before or after midnight - just test the time component using HOUR. If the hour is between 0 and 6, the time can be considered to be after midnight. Otherwise it's before. =AND(HOUR(C2)>=0, HOUR(C2)<=6)


0

This is now possible by inserting a formula instead of a static number into the box for min/mid/max amount.


0

OK it's pretty nasty but I was able to get it done. Assume the first row we start from is row 2. The date column is calculated like this with 43831 being 1/1/2020 =-366*FLOOR((ROW()-2)/366,1)+(ROW()-2+43831) You copy this field infinitely downwards. What happens here is that each January 1st the date is reset so the next client will stay with the same ...


0

You can use the following query formula: =QUERY(Q2:S11, "select sum(Q) where R matches '(.*green.*)|(.*toy.*)' OR S matches '(.*green.*)|(.*toy.*)' label sum(Q)''") About QUERY


0

The issue to your question has nothing to do with the IMPORTRANGE function. It is about your reference to the sheet. You need to enclose the name of the sheet in single quotes, because the name of the sheet itself has a blank space between the words. As from the official help site on "Get data from other sheets in your spreadsheet": If a sheet ...


0

The formula you would use is: =ArrayFormula(SUM(IF($B2:$B27=K2:K27,$B2:$B27,""))) Functions used: ARRAYFORMULA SUM IF


0

You want to insert a formula into Column R when a form submission is received. As outlined in the comments: Use an onformSubmit trigger to take action when a new submission is received. This is installable Trigger, so you will need to ... install it. Take advantage of Event Objects to get the row number of the submission. In the script you'll note the ...


1

If you're trying to SUM() a column based on criteria, then SUMIF() or SUMIFS() would get you what you want. In this example you could use SUMIFS() =SUMIFS(P:P, A:A, "PG", F, "Indexed") - this gives you a SUM of values in P where Column A = "PG" and Column F = "Indexed". You could extend these criteria for as many filters as you need. You'd then change this ...


0

Best way is to send an email using Code.gs from the Google Sheet to automatically send the student an email with the pass/fail result.


0

This function gets rid of all non-digit characters from cell A1 with REGEXREPLACE(). To avoid error when there's no non-digit character, the formula is wrapped with a fall-back IFERROR(). Output strings are then cast as a number with VALUE(). =VALUE(IFERROR(REGEXREPLACE(A1,"[^0-9]",""),A1) The output for the sample from above is: 4237004000 9002570000 ...


1

Since you use a single cell to input both your data and formula, you should change your "formula" from =HOUR("17:30:00") - Hour("9:00:00") to simply =("17:30:00" - "9:00:00")*24 The above formula will give you the desired result 8.5. IMPORTANT. Make sure you format your cells as Number ( by going to: Format > Number > Number)


0

You can't pass a range to ISBLANK just as you did isblank(B:B) because if you want to pass a range, it actually does need the same ARRAYFORMULA function. If you want to make it work, you can do it this way: =ARRAYFORMULA(IF(ISBLANK(B4:B),"",IF(B4:B>=900,B4:B*0.2,IF(B4:B>=400,B4:B*0.15,IF(B4:B>=200,B4:B*0.1,IF(B4:B<=199,B4:B*0,FALSE)))))) Check ...


1

In my test, changing the row reference to a column reference (column U in this case), the above formula returns the NEXT-to-last non-zero value. I was able to get the LAST value as follows, but I don't know whether this formula would work generally or there is something particular about my spreadsheet that requires the "+1," so I hesitate to recommend this ...


0

Can you share what you've tried so far? Screenshots/formula would be helpful. Here is my attempt to answer your question based on the available information: =A2+'Copy of Sheet1'!$D$2 Add $ to reference what you want to keep. $ will retain either column, row, or both.


0

So... I've been already trying to fix this a couple of times before and I could never find a solution, but when you finally post a question here, you somehow find the answer in 5 minutes on your own. So here's solution: Select cells you want to format as link, click FORMAT menu, NUMBER submenu and even though AUTOMATIC is already selected with check sign, ...


Top 50 recent answers are included