1

Kind of new here. Tried to look for a similar answer but not quite getting it to work.

I made a formula - it worked. I turned it into an array formula - it worked.

But it was returning 0's in the column where the reference cell was blank. I looked up ISBLANK and that seems like the right answer... I combined them - not broken, but not working. (so ArrayFormula is still giving the correct answer, but 0's are all still there)

Any ideas? I've worked my way to this point - made a sample file https://docs.google.com/spreadsheets/d/19h9OOShLHYV8qDhTl3wrWSPfnxpsXBI3kvN8BXYUOFw/edit?usp=sharing

I get the I needed to put the IF(ISBLANK) _before the ArrayFormula, and if you look at my example it works..until you fill in cell B4, then all the zeros show up. I'm pretty sure I'm making a pretty basic mistake at this point. thanks in advance - if you don't look at the file, here's the formula:

 =if(isblank(B:B),"",ArrayFormula(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))))))
0

=ARRAYFORMULA(IF(LEN(O4:O),
 IF(O4:O>=900, O4:O*0.2,
 IF(O4:O>=400, O4:O*0.15,
 IF(O4:O>=200, O4:O*0.1,
 IF(O4:O<=199, O4:O*0, )))), ))

0


0


0


0

  • Thank you for the alternative answer, but I am trying to learn what mistake I made with the ISBLANK formula. – Finnegan Brubeck Apr 30 at 14:57
  • @FinneganBrubeck did you see the 2nd picture? your mistake is that you use different range sizes in ISBLANK and ArrayFormula – user0 Apr 30 at 15:06
  • 1
    ahh... the my problem was I didn't understand the second picture! This does make sense, thank you! solved. – Finnegan Brubeck Apr 30 at 15:09
  • @FinneganBrubeck also ArrayFormula should be always the first eg. before anything else – user0 Apr 30 at 15:15
  • 1
    hey user0 - I just wanted to say thanks again. I know this didn't get a lot of views, but now that I actually understand.. I actually learned how it works - and that was my goal all along. Thanks for coming back to explain - my problem is solved, I feel like I'm writing cleaner formulas, my formatting is better.. many thanks – Finnegan Brubeck Apr 30 at 17:07
0

Please try, in C4:

=ArrayFormula(if(len(B4:B),B4:B*(choose(match(B4:B,{0,200,400,900}),0,0.1,0.15,0.2)),""))

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.