SOLVED

# ISBLANK formula with ROUNDUP and IFERROR

Occasional Contributor

# 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!

5 Replies

# Re: ISBLANK formula with ROUNDUP and IFERROR

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.

# Re: ISBLANK formula with ROUNDUP and IFERROR

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

# Re: ISBLANK formula with ROUNDUP and IFERROR

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!

best response confirmed by juliejo (Occasional Contributor)
Solution

# Re: ISBLANK formula with ROUNDUP and IFERROR

Never mind! I figured it out:

=IFERROR(IF(ISBLANK(Worksheet!B7),ROUNDUP(D55*0.03,0),ROUNDUP((D55+F55)*0.03,0)),"")

Thanks!

# Re: ISBLANK formula with ROUNDUP and IFERROR

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: