Forum Discussion
ISBLANK formula with ROUNDUP and IFERROR
So, I have this lovely little formula that works great, but I need it to do a little bit more than it does now.
The 3% cell features this formula: =IF(ISBLANK(Worksheet!B7),D55*0.03,(D55+F55)*0.03)
Worksheet!B7=the rush percentage, which is initially filled out on another sheet and will usually be blank.
D55=the base price shown above
F55=the rush price shown above
There are two issues I have. The first is that I need the 3% cell to round up to the nearest whole dollar (in this example, $10). The second is that my formula is returning an errant #VALUE! error, so I'd like to use an IFERROR formula to make that disappear, since my formula works just fine.
Can I combine ISBLANK, ROUNDUP, and IFERROR in a single formula? Or is there a better way to accomplish all these things?
Thanks!
Never mind! I figured it out:
=IFERROR(IF(ISBLANK(Worksheet!B7),ROUNDUP(D55*0.03,0),ROUNDUP((D55+F55)*0.03,0)),"")
Thanks!
5 Replies
Hi juliejo ,
format your cells in currency so when you do a calculation of:
The 3% cell features this formula: =IF(ISBLANK(Worksheet!B7),D55*0.03,(D55+F55)*0.03)
Worksheet!B7=the rush percentage, which is initially filled out on another sheet and will usually be blank.
D55=the base price shown above
F55=the rush price shown above
it won't error out since if D55=0, and F55=0 will be D55+F55=0*0.03= $0.00
so there will be no iferror or isblank.
cheers
I should have been more clear. The #VALUE! error is showing up BEFORE any values have been filled in. It's distracting and may confuse users, so I'm just trying to hide it.
But my far bigger issue is getting the resulting value to round up. I can live with the value error, but the rounding up is a deal breaker. I need to figure that one out.
Thanks!
I should have been more clear. The #VALUE! error is showing up BEFORE any values have been filled in. It's distracting and may confuse users, so I'm just trying to hide it.Not in my worksheet:
There are only two cases which create #VALUE!.
1. F55 contains text and Worksheet!B7 is not empty.
2. D55 contains text.
Neither would make sense.
