ArrayFormula combined with ISBLANK

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)

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))))))
``````

``````=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, )))), ))``````

• Thank you for the alternative answer, but I am trying to learn what mistake I made with the ISBLANK formula. Commented Apr 30, 2019 at 14:57
• @FinneganBrubeck did you see the 2nd picture? your mistake is that you use different range sizes in ISBLANK and ArrayFormula Commented Apr 30, 2019 at 15:06
• ahh... the my problem was I didn't understand the second picture! This does make sense, thank you! solved. Commented Apr 30, 2019 at 15:09
• @FinneganBrubeck also ArrayFormula should be always the first eg. before anything else Commented Apr 30, 2019 at 15:15
• 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 Commented Apr 30, 2019 at 17:07

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 it out here, copied from your sample file.

And a picture for you to be worth a thousand words:

Also as a side note, instead of multiple IF statements it might be a better idea to use IFS, will look much cleaner and easier to follow and edit.

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