0

I have a table which looks like this:

1

And formula in cell B3: =IF(A3>5, B2, B2+1) and on every next row a corresponding variation of this formula.

I would like to know how to create ArrayFormula variant of this formula:

=IF(A3>5, B2, B2+1)

spreadsheet sample here

Improve this question
10
  • Allen, in the meantime, your sample sheet is "View Only." Please change the permissions to "Anyone with the link can Edit." – Erik Tyler Jan 24 '19 at 14:21
  • 1
    @AllenSierra LEN counts how many characters are in cell. =ARRAYFORMULA(IF(LEN(A1:A), true, false)) checks every row in given range if its empty/not-empty. in other words this works like some check-in restriction. kinda same as =ARRAYFORMULA(IF(A1:A<>"", true, false)) – user0 Jan 24 '19 at 21:01
  • 2
    As a fail safe, I've gone ahead and uploaded a formula into your sample sheet, Sheet2!B1: =ArrayFormula({"Result";IF(A2:A="","",COUNTIFS(ROW(A2:A),"<="&ROW(A2:A),A2:A,"<=5"))}) – Erik Tyler Jan 24 '19 at 21:41
  • 1
    @Allen, my formula results in a match to the image you posted in the original post. I'll wait for a reply to this point before I explain the formula. – Erik Tyler Jan 26 '19 at 4:07
  • 1
    @Allen, in addition to my formula producing results that match the image in your original post, the formula in your original post says (in English), "If the value in A is greater than five, don't add anything to the previous value; otherwise, add one." – Erik Tyler Jan 26 '19 at 4:50
1

=ArrayFormula({"Result";IF(A2:A="","",COUNTIFS(ROW(A2:A),"<="&ROW(A2:A),A2:A,"<=5"))})

Improve this answer
0

probably not the answer OP is looking for, but here are some examples how to construct ARRAYFORMULA from =IF(A3>5, B2, B2+1)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.