0

I am looking to calculate pricing and tax between 3 cells. Though if 1 of the cells is blank, I want the end result cell to be blank.

In this instance I have the: (order total * the tax rate) + the shipping quote.
But if there is no shipping quote, it is still populating with the first part.

I would like to set it so if there is not shipping quote, it stays blank, or even has a dash there instead.

This is the formula right now: =(F4*0.25)+G4

0

You need to inclose your formula within the IF function:

=IF(G4<>"", (F4*0.25)+G4,"-")

(Of course you can change the dash to something more meaningful, like No shipping quote)

As suggesteded at the comment the formula could also use ISBLANK

=IF(NOT(ISBLANK(G4)), (F4*0.25)+G4,"NO shipping quote")

  • IMHO it's better to use ISBLANK(reference) to test if a cell is blank because and empty string is not the same that an empty cell. – Rubén Nov 22 at 2:35
  • @Rubén Correct. Could be used as well. I updated the answer. – marikamitsos Nov 22 at 2:46
  • I found cases where ISBLANK() would return false when the value is "", but never found a case where A1="" returned false when empty.Didn't reaserch, just used the one that worked and moved on – Manuel Ramirez Dec 1 at 17:42
  • @ManuelRamirez Thanks for the heads up. Please remember that by upvoting an answer others benefit as well. – marikamitsos Dec 2 at 17:24
0
  1. Consider to use the IF function.
  2. To return a blank, leave the IF corresponding parameter blank, i.e.:

    =IF(ISBLANK(G4),,F4*0.25+G4)
    
0

=iferror(if(G4="","-",(F4*0.25)+G4),"invalid shipping quote")

Dash when no quote, and message when quote has a value but is not a number (just in case there is an incorrect entry)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.