a lookup function might be better like the others suggested. One thing to
keep in mind though, every "<=" symbol will be interpreted as "<". For
instance 0.42 returns .4 in your function, but .63 in the lookup function,
and .41 returns .4 in both. If you don't want to make a table, then use
something like these functions.

=VLOOKUP(K21,{0,0.4;0.42,0.63;0.66,1;1.05,1.6;1.68 ,2.5;2.625,4;4.2,6.3;6.615,10;10.5,16;16.8,25;26.2 5,40;42,63;66.15,100;105,106;168,250;262.5,400;420 ,400},2,TRUE)

or

=HLOOKUP(K21,{0,0.42,0.66,1.05,1.68,2.625,4.2,6.61 5,10.5,16.8,26.25,42,66.15,105,168,262.5,420;0.4,0 .63,1,1.6,2.5,4,6.3,10,16,25,40,63,100,160,250,400 ,400},2,TRUE)

"Sloth" wrote:

The formula outputs a text string. This was probably a work-around, to get
around the limit for nested if's. You need to encapsulate the whole function
in the VALUE function.

=VALUE(IF(AND(K210, K21<=0.42), 0.4, "")&IF(AND(K210.42, K21<=0.66), 0.63,
"")&IF(AND(K210.66, K21<=1.05), 1, "")&IF(AND(K211.05, K21<=1.68), 1.6,
"")&IF(AND(K211.68, K21<=2.625), 2.5, "")&IF(AND(K212.625, K21<=4.2), 4,
"")&IF(AND(K214.2, K21<=6.615), "6.3", "")&IF(AND(K216.615, K21<=10.5),
"10", "")&IF(AND(K2110.5, K21<=16.8), "16", "")&IF(AND(K2116.8,
K21<=26.25), 25, "")&IF(AND(K2126.25, K21<=42), 40, "")&IF(AND(K2142,
K21<=66.15), 63, "")&IF(AND(K2166.15, K21<=105), 100, "")&IF(AND(K21105,
K21<=168), 160, "")&IF(AND(K21168, K21<=262.5), 250, "")&IF(AND(K21262.5,
K21<=420), 400, ""))

"Tom B" wrote:

Thank you for replying so quickly. It seems my IF statements do not give me
a zero valve (if false) and the MAX statement isn't liking it.
eg:
IF(AND(K210, K21<=0.42), 0.4, "")&IF(AND(K210.42, K21<=0.66), 0.63,
"")&IF(AND(K210.66, K21<=1.05), 1, "")&IF(AND(K211.05, K21<=1.68), 1.6,
"")&IF(AND(K211.68, K21<=2.625), 2.5, "")&IF(AND(K212.625, K21<=4.2), 4,
"")&IF(AND(K214.2, K21<=6.615), "6.3", "")&IF(AND(K216.615, K21<=10.5),
"10", "")&IF(AND(K2110.5, K21<=16.8), "16", "")&IF(AND(K2116.8,
K21<=26.25), 25, "")&IF(AND(K2126.25, K21<=42), 40, "")&IF(AND(K2142,
K21<=66.15), 63, "")&IF(AND(K2166.15, K21<=105), 100, "")&IF(AND(K21105,
K21<=168), 160, "")&IF(AND(K21168, K21<=262.5), 250, "")&IF(AND(K21262.5,
K21<=420), 400, "")

As you can see, I am testing a value in a cell to give me another value but
not returning a zero value (someone else wrote this, i'm just
troubleshooting). Anyone know of an easier way to do this?





"Tom B" wrote:

In an IF statement, if either the logical test is either true or false and a
value is entered into the cell, how is that value different than if the user
entered the value themselves (using general formatting)

I have a MAX statement what won't pick up the IF statement's 63 but works
with the user entered 63.