I need a formula to put in cell C3 that says: If cell A1 > 0, then it equals cell B2 (but if it's blank, it equals 0)

[thefreestove]

I hope this makes sense?

I'm creating a budget for a tour company that occasionally use bus drivers. I want the formula in C3 to reference cells A1 and B2. If the number of bus drivers (cell A1) is greater than 0 - then I need to add the accommodation costs (from cell B2) to the vehicles budget.

Hope this helps:

A1 = Number of Bus drivers
B2 = Price of Accommodation
C3 = Formula I'm seeking (I only want the price of accommodation to be included IF we are using a bus driver - otherwise the cell should remain blank)

Many thanks in advance - I've been trying for ages without any luck.

 

[inactiveuserps07]

@thefreestove, unless I'm misunderstanding you somehow, C3 formula should just be this:

=IF(A1>0,B2,"")

 

[Aladin Akyurek]

Something like:

=IF(ISNUMBER(1/A1),B2,"")

 

[thefreestove]

Thank you ErikTyler - I'm a complete newbie and I appreciate you replying with a solution!

 

[inactiveuserps07]

@thefreestove, we all learned whatever we learned at some point. Keep asking. And glad it worked out.

Aladin's suggested formula will also work; and, if you have any reason to believe that someone might enter a space into A1 rather than deleting it or putting a zero, his formula would take care of those instances too.

 

[thefreestove]

Thank You, Aladin. ErikTyler just informed me that your formula would also work if someone accidentally hit a space bar... so with that in mind - is there a formula prefix/extension that I can add to all of my other formulas that would also address this problem?

For example, I have a simple formula: =C34*(C35*C36) But if there was a space bar in C34, then it returns a #VALUE ! (whereas the cells containing your super-formula just show 0) - this could be a lifesaver as the main user of my spreadsheet is someone not very experienced with Excel, and I could just imagine them hitting a space bar and destroying formulas

Thank you

 

[Aladin Akyurek]

Thank You, Aladin. ErikTyler just informed me that your formula would also work if someone accidentally hit a space bar... so with that in mind - is there a formula prefix/extension that I can add to all of my other formulas that would also address this problem?

For example, I have a simple formula: =C34*(C35*C36) But if there was a space bar in C34, then it returns a #VALUE ! (whereas the cells containing your super-formula just show 0) - this could be a lifesaver as the main user of my spreadsheet is someone not very experienced with Excel, and I could just imagine them hitting a space bar and destroying formulas

Thank you

You can either

=IF(ISNUMBER(C34*(C35*C36)),C34*(C35*C36),"no value")

or

=IFERROR(C34*(C35*C36),"no value")

It might be the case that the first one is a tad faster.

By the way, destroying a formula by hitting a space cannot be counteracted by a formula. Rather have look at the protection functionality under the Review tab of Excel.

 

[thefreestove]

Aladin, you are a genius. Thank you!

 

[thefreestove]

Hello Aladin,

I received an email that you tried to send me a message, but it was not delivered to my inbox as I'm only allowed 1 pm.

The request was:

If there's an error in C37 and/or $E$7 replace with "$0" and also divide E44 by $E$7 (all in the one formula) - is this possible?

Thank you

 

[Aladin Akyurek]

Hello Aladin,

I received an email that you tried to send me a message, but it was not delivered to my inbox as I'm only allowed 1 pm.

The request was:

If there's an error in C37 and/or $E$7 replace with "$0" and also divide E44 by $E$7 (all in the one formula) - is this possible?

Thank you

Replace what with 0 when C37 and/or $E$7 contain an error?

Must C27 be included in the final formula which meeds to divide E44 by $E$7? If yes, how?