Excel HELP!!!

[neil]

As an exercise in learning to use Excel, I am putting together a simple Nitrox calculator. It's all fun and I'm learning quite a bit but am baffled by the syntax for the following problem:

I have this formula (yes, it works): =((D11-0.21)/(B13-0.21))*D10

I want this to show nothing or N/A if the value is less or equal to zero. It seems from the help file that IF(ISERROR) may be the key? Anyway, if anyone can help, I'd really appreciate it.
Neil

 

[raypau38]

It is true that you would in fact have to use an IF statment. I don't know what version of excel you are using (2000, or XP). There are several ways that you can do this. Personally, the way that I would do it is to have something that looks like this

=IF(((D11-0.21)/(B13-0.21))*D10 >= 1,A1,"NA")

The A1 that you see present is where it would output the answer that is grater then 0 or NA if it is less. I hope that this helps and if you ned any more assistance just PM me

This was done in excel 2000

 

[DivingGal]

concur with the forumla -- except if you want to show nothing or NA then the last bit would read >=1," ", "NA")

Translation ... if the results of this calculation is equal to or greater than 1 show a blank, otherwise show NA

 

[raypau38]

That is what it is doing it is just placing the output of the formula in the cell A1.

 

[neil]

Thanks folks, I'll give it a try tonight after Christmas cheer has been metabolized!
Neil

 

[rjens]

Here's another variant...

=IF(((D11-0.21)/(B13-0.21))*D10 >= 1,(((D11-0.21)/(B13-0.21))*D10),"NA")


This one actually prints the result or "NA" without needing to reference another cell. The trick is to test the equation, then if it meets the condition you want, do it again in the first response, if not show the end result.

To truly get it correct for all situations you need a nested if to make sure that B13 isn't = .21 (which will get you a divide by 0). so use this formula. A bit more complex but will work correctly for all situations.

=IF(B13<>0.21,IF(((D11-0.21)/(B13-0.21))*D10 >= 1,(((D11-0.21)/(B13-0.21))*D10),"NA"),"NA")

Hope this helps.
Rj

 

[DivingGal]

gad... definately need less of the Christmas cheer, but with a bit of mental exercising... good solution (obviously done with a bit more thought than I put in)

J

 

[neil]

Rj
Actually I did one as in your first example, and another using IF(ISERRORxxxxxx). Definitely another language. I'll have to ponder the second example. Thanks again.
Neil

 

[XJae]

just curious what that formula is calculating.

it doesn't look like equivalent air depth, o2 partial pressure or max / contingency depth?

 

[neil]

just curious what that formula is calculating.
it doesn't look like equivalent air depth, o2 partial pressure or max / contingency depth?

It's calculating partial pressure mixing with pre-mixed Nitrox instead of pure O2.


Neil