Formula to calculate a formula if another formula produces a certain value?

brickZ52

New Member
Joined
Sep 19, 2019
Messages
3
Hello all,

I am trying to create a formula in which for example, if C5<=0, show the value "N/A". Ff C5>0, then calculate the formula of C5/B5.

Can anyone help?

THanks!
Greg
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Welcome to the Board!

Try:
Code:
=IF(C5 <= 0,"N/A",C5/B5)
 
Last edited:
Upvote 0
I got it to work using this:

=IF(H4<>0,H4/F4,IF(H4=0,"N/A"))
Well, the conditions and cell references are different than your original question, as you had stated that you wanted it to return "N/A" for values less than or equal to zero, and your new equation only returns "N/A" if it is exactly equal to 0.

If you new formula, accomplishes what you are really after, then as long as you only have numeric values in H4, you don't need a nested IF. You can simplify your formula to:
Code:
[COLOR=#333333]=IF(H4<>0,H4/F4,"N/A")[/COLOR]
because if you only have numeric values in H4, then if you check to see if the value is NOT zero, the only other option is that it is zero (so you don't really need to check for that value, you can just say return "N/A" otherwise).
 
Last edited:
Upvote 0
Well, the conditions and cell references are different than your original question, as you had stated that you wanted it to return "N/A" for values less than or equal to zero, and your new equation only returns "N/A" if it is exactly equal to 0.

If you new formula, accomplishes what you are really after, then as long as you only have numeric values in H4, you don't need a nested IF. You can simplify your formula to:
Code:
[COLOR=#333333]=IF(H4<>0,H4/F4,"N/A")[/COLOR]
because if you only have numeric values in H4, then if you check to see if the value is NOT zero, the only other option is that it is zero (so you don't really need to check for that value, you can just say return "N/A" otherwise).


I originally chose the cells C5 and B5 and then when I figured it out, just copied/pasted the formula from a different set of cells. However, I do see your point in that 2 conditions are not needed and your formula gets the same job done in a simpler manner.

Appreciate the help guys!

Greg
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top