CODEWITHSUNDEEP
google-sheetsarray-formulas
Andy
Andy

Reputation: 820

Arrayformula If And

I'm trying to apply an array formula to one of my sheets and it is not working - the formula itself works however it isnt being applied to all the cells (the continue bit)

This is my formula.

if(H2-F2<14,1,if(and(H2-F2>14,H2-F2<168),14,if(H2-F2>168,42,"")))

When I add array formula

=ARRAYFORMULA(
  if(H2-F2<14,1,
    if(and(H2-F2>14,H2-F2<168),14,
      if(H2-F2>168,42,"")
    )
   )
)

It works but doesn't apply it to all cells, why is this and how do I go about making it work?

EDIT: New formula with cell ranges

=ARRAYFORMULA(
  if(H2:H-F2:F<14,1,
    if(and(H2:H-F2:F>14,H2:H-F2:F<168),14,
      if(H2:H-F2:F>168,42,"")
    )
  )
)

Upvotes: 2

Views: 4589

Answers (1)

Elm
Elm

Reputation: 1407

I'm not sure why it doesn't work either, but the work around is to break each of the conditions in the 'and' statement into separate nested 'if' statements. So if you have three conditions, you would end up with three if statements one nested inside the other:

if(condition_1, if(condition_2, if(condition_3,then X,else Y),else Y),else Y)

it's equivalent to the if + and statement

Upvotes: 5

Related Questions