Help with a #NUM! error please

[msampson]

I have a dataset with a variety of values. One of them is an integer T that also happens to be in column T. I have an equation that will switch between two options based on the value of T. It's easy to do with an if statement, but I want to build it into this equation so that it can easily be ported to other systems that won't be able to use conditional formulas.

This is the equation

=1/(1+(EXP(10*(T2-400))))

For values of T < 400 the equation returns the value of 1. For values of exactly 400 the equation returns the value of 0.5. For values of T > 400 the equation returns the value of 0.

It works fine until I get to 471 (These values will extend from 5-3000). For 470 I get a 0 but for every number greater than that I get a value of #NUM !

Any ideas?
thanks, Maureen

 

[joeu2004]

=1/(1+(EXP(10*(T2-400))))
For values of T < 400 the equation returns the value of 1. For values of exactly 400 the equation returns the value of 0.5. For values of T > 400 the equation returns the value of 0.

Not really. Up to 396 does indeed return 1.

For 397 to 399, it returns 0.999999999999907 to 0.999954602131298.

For 400, it does indeed return exactly 0.5.

But for 401 to 470, it returns 0.0000453978687024344 to 9.85967654375977E-305, not exactly zero.

For 470 I get a 0 but for every number greater than that I get a value of #NUM !

For 470, EXP returns 1.014232054735E+304.

For 471, EXP exceeds the internal 64-bit binary floating-point calculation limit of about 1.79769313486232E+308 (exactly 10*1.79769313486231E+307 + 5.79E+293).

The largest value of T2 is 470.978271289338.

Why not IF(T2<400, 1, IF(T2=400, 0.5, 0)) ? Or (T2<400)+(T2=400)*0.5 ?