1

I have a working formula that could be repeated 1000s of times to return the answers that I require.

=ArrayFormula(if(A2="","",(A2/1000/60/60/24+date(1970,1,1))))

Trouble is the formulas will break as soon as a new row is added or deleted.

The solution to this is to add 'row' to the formula which will allow me to write the formula once and it will return the correct values in every cell in the range.

I tried this formula:

=ArrayFormula(if(ROW(C2:C),(A2="","",(A2/1000/60/60/24+date(1970,1,1)))))

but it returns a formula parse error!

I have seen simple formulas work in this way but I am not sure about the position of the ROW or even if it will work with +date.

Test sheet is here: https://docs.google.com/spreadsheets/d/1tigqy4hKFn0Q7c-3ICyI6WREnsIBFZ2oLO8CEcdxe8w/edit?usp=sharing

Any ideas please?

player0
  • 124,011
  • 12
  • 67
  • 124
Stuart
  • 315
  • 1
  • 8

1 Answers1

2

use in row 1:

={"header"; ARRAYFORMULA(IF(A2:A="",,(A2:A/1000/60/60/24+DATE(1970, 1, 1))))}

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you, this works well, another new way of achieving what I need well – Stuart Aug 21 '22 at 10:41
  • Why wouldn't this be able to be repeated on all formulas? =ArrayFormula(if(iserror(match(F2,Amp_Data!$A$2:$A,0)),"NO","YES")) for example: ={"Date"; ARRAYFORMULA(IF(F2:F="",,(iserror(match(F2,Amp_Data!$A$2:$A,0)),"NO","YES")))} – Stuart Aug 21 '22 at 16:46
  • @Stuart can you share a copy of your sheet where it fails? – player0 Aug 21 '22 at 17:19
  • 1
    thanks for reply, sorry I can't share that sheet, I will try and create a test example – Stuart Aug 21 '22 at 17:29
  • I have added another example also not working in the same setup https://docs.google.com/spreadsheets/d/1tigqy4hKFn0Q7c-3ICyI6WREnsIBFZ2oLO8CEcdxe8w/edit?usp=sharing 2 tests 1 in Col F and the other in Col G – Stuart Aug 22 '22 at 20:38
  • 1
    @Stuart try: `={"Amp. Login"; ARRAYFORMULA(IF(B2:B="",,IF(ISERROR(MATCH(A2:A, Amp.!$A$2:$A, )),"NO", "YES")))}` – player0 Aug 22 '22 at 20:44