Jun 18 2021 10:09 AM
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!
Jun 18 2021 10:29 AM
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.
Jun 20 2021 12:10 PM
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
Jun 21 2021 08:32 AM
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!
Jun 21 2021 09:13 AM - edited Jun 21 2021 09:14 AM
SolutionNever mind! I figured it out:
=IFERROR(IF(ISBLANK(Worksheet!B7),ROUNDUP(D55*0.03,0),ROUNDUP((D55+F55)*0.03,0)),"")
Thanks!
Jun 21 2021 09:52 AM
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:
Jun 21 2021 09:13 AM - edited Jun 21 2021 09:14 AM
SolutionNever mind! I figured it out:
=IFERROR(IF(ISBLANK(Worksheet!B7),ROUNDUP(D55*0.03,0),ROUNDUP((D55+F55)*0.03,0)),"")
Thanks!