SOLVED

ISBLANK formula with ROUNDUP and IFERROR

%3CLINGO-SUB%20id%3D%22lingo-sub-2463911%22%20slang%3D%22en-US%22%3EISBLANK%20formula%20with%20ROUNDUP%20and%20IFERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2463911%22%20slang%3D%22en-US%22%3E%3CP%3ESo%2C%20I%20have%20this%20lovely%20little%20formula%20that%20works%20great%2C%20but%20I%20need%20it%20to%20do%20a%20little%20bit%20more%20than%20it%20does%20now.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-06-18%20115550.jpg%22%20style%3D%22width%3A%20216px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F289985iE87A0CAE3D5893C8%2Fimage-dimensions%2F216x89%3Fv%3Dv2%22%20width%3D%22216%22%20height%3D%2289%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-06-18%20115550.jpg%22%20alt%3D%22Screenshot%202021-06-18%20115550.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%203%25%20cell%20features%20this%20formula%3A%26nbsp%3B%3CSTRONG%3E%3DIF(ISBLANK(Worksheet!B7)%2CD55*0.03%2C(D55%2BF55)*0.03)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EWorksheet!B7%3C%2FSTRONG%3E%3Dthe%20rush%20percentage%2C%20which%20is%20initially%20filled%20out%20on%20another%20sheet%20and%20will%20usually%20be%20blank.%3C%2FP%3E%3CP%3E%3CSTRONG%3ED55%3C%2FSTRONG%3E%3Dthe%20base%20price%20shown%20above%3C%2FP%3E%3CP%3E%3CSTRONG%3EF55%3C%2FSTRONG%3E%3Dthe%20rush%20price%20shown%20above%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20two%20issues%20I%20have.%20The%20first%20is%20that%20I%20need%20the%203%25%20cell%20to%20round%20up%20to%20the%20nearest%20whole%20dollar%20(in%20this%20example%2C%20%2410).%20The%20second%20is%20that%20my%20formula%20is%20returning%20an%20errant%20%23VALUE!%20error%2C%20so%20I'd%20like%20to%20use%20an%20IFERROR%20formula%20to%20make%20that%20disappear%2C%20since%20my%20formula%20works%20just%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20combine%20ISBLANK%2C%20ROUNDUP%2C%20and%20IFERROR%20in%20a%20single%20formula%3F%20Or%20is%20there%20a%20better%20way%20to%20accomplish%20all%20these%20things%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2463911%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2463976%22%20slang%3D%22en-US%22%3ERe%3A%20ISBLANK%20formula%20with%20ROUNDUP%20and%20IFERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2463976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1071925%22%20target%3D%22_blank%22%3E%40juliejo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20only%20two%20cases%20which%20create%20%23VALUE!.%3C%2FP%3E%3CP%3E1.%20F55%20contains%20text%20and%20Worksheet!B7%20is%20not%20empty.%3C%2FP%3E%3CP%3E2.%20D55%20contains%20text.%3C%2FP%3E%3CP%3ENeither%20would%20make%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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