SOLVED

ISBLANK formula with ROUNDUP and IFERROR

Copper Contributor

So, I have this lovely little formula that works great, but I need it to do a little bit more than it does now.

 

Screenshot 2021-06-18 115550.jpg

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

@juliejo 

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.

 

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.

Screenshot 2021-06-21 102757.jpg

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 (Copper Contributor)
Solution

Never mind! I figured it out:

 

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

 

Thanks!

@juliejo 

 


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.

Screenshot 2021-06-21 102757.jpg


Not in my worksheet:

techcommunity_2463911M103765.gif

 

1 best response

Accepted Solutions
best response confirmed by juliejo (Copper Contributor)
Solution

Never mind! I figured it out:

 

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

 

Thanks!

View solution in original post