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))))))
Improve this question
1

3 Answers 3

Reset to default
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

Improve this answer
5
  • Thank you for the alternative answer, but I am trying to learn what mistake I made with the ISBLANK formula.
    – Finnegan Brubeck
    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
    – user0
    Commented Apr 30, 2019 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
    Commented Apr 30, 2019 at 15:09
  • @FinneganBrubeck also ArrayFormula should be always the first eg. before anything else
    – user0
    Commented Apr 30, 2019 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
    Commented Apr 30, 2019 at 17:07
1

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:

enter image description here

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.

Improve this answer
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)),""))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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