Need an excel formula that will calculate CNS%

[medcop]

I am trying to write a excel spreadsheet that will figure CNS calculations based on an entered time & PO2.

However, I am not having any luck making it work.

I need a formula that will look at the PO2 column make a decision based on the PO2 then take the time from the time column and divide the time by the maxim exposure for that PO2.

example... if the PO2 in the column is 1.5 and the Time in the TIME column is 20 then I need the formula to divide 20 by 150.

example... if the PO2 in the column is 1.6 and the TIME column is 20 I need 20/45.

example....if the PO2 in the column is 1.4 and the Time column is 15 I need 15/150.

example... if the PO2 is 1.5 and the Time is 30 then I need the formula to divide 30 by 120.

and so on....

thanks!

 

[grazie42]

Why don´t you just make 4 columns?
PO2, Exp time, time and cns%.
Then you just divide "time" by "exp. time" in the formula for the "cns%"-column?
Or am I misunderstanding your intended usage?

 

[medcop]

Why don´t you just make 4 columns?
PO2, Exp time, time and cns%.
Then you just divide "time" by "exp. time" in the formula for the "cns%"-column?
Or am I misunderstanding your intended usage?

I want to be able to change the form on the fly. I have it set up now that it plugs in my PO2 and when I put my stop times or times at certain depths in I want it to auto calculate my CNS%.

 

[Deefstes]

It shouldn't be too difficult by usng the IF() worksheet function of Excel. I could give you an example but I don't know squat about PO2 and NCS so if you could explain it a little bit better to me I'm sure I can help you. For instance, how do you decide the factor by which you divide the time? Your exampled probably makes sense to someone with rebreather knowledge but just elaborate a little please?

 

[viceroy]

I've quickly done a quick spreadsheet (metric), but will change the formulas to imperial if required.

It's really simple. You enter depth (msw), O2%, and time (minutes), and it displays PO2, CNS%, EAD, as well as your Max single exposure and Max 24hr exposure.

PM me with your email address and specify if you would prefer metric or imperial, and I'll happily forward it onto you.

ps. did you know there is a limitation to the number of nested IF statements you can use in a formula in Excel 2003

 

[dannobee]

I did a bunch of financial modeling years ago and got to thinking about this. I'm still quite rusty with Excel, but I'm pretty sure you can use a IF(AND...) function. Enter the cell appropriate cells, i.e. =IF (C:4<1.5(AND(C:5=20),A:2/A:5,... or something like that, where A:2 is your 20 and A:5 is your 150. I could be missing some () or something, but you should get the idea.

Like I said, it's been a few years since I've done this, but I did a LOT of Excel stuff in the past. If you send me a link with what you've done so far, I can take a look at it and clean it up.

 

[brutus_scuba]

does anyone know how NOAA came up with those limits?

 

[Blackwood]

I can get you kinda close with a high order polynomial. Unfortunately it drops off where we really care (high PO₂).

CNS% = 100/(-515.43*PO₂⁴+1207.1*PO₂³+454.1*PO₂²-3042*PO₂¹+2189.7)

 

[medcop]

I ended up writing one using a VLookup statement....thanks everyone who replied!

 

[Blackwood]

hrm, this one is closer:

CNS% = 100/(-1260*PO₂⁵+6256.9*PO₂⁴-12936*PO₂³+14761*PO₂²-10035*PO₂¹+3508.6)

For the record, these are the values it returns (with NOAA's data for reference):

PO2     NOAA    CALC
0.55	0.12	0.12
0.60	0.14	0.14
0.65	0.16	0.16
0.70	0.18	0.18
0.75	0.20	0.20
0.80	0.22	0.22
0.85	0.25	0.25
0.90	0.28	0.27
0.95	0.30	0.31
1.00	0.33	0.34
1.05	0.37	0.37
1.10	0.42	0.41
1.15	0.44	0.44
1.20	0.48	0.48
1.25	0.51	0.52
1.30	0.56	0.55
1.35	0.61	0.59
1.40	0.67	0.65
1.45	0.72	0.72
1.50	0.83	0.86
1.55	1.11	1.14
1.60	2.22	2.08