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.

You're right about the <= being evaluated as <.

I compensate for that by offsetting the first column of the lookup table up
one position.
The table, initially, looks out of sync, but it compensates for the way
VLOOKUP evaluates the arguments and the table. The alternative is to add some
infinitesimal value to the table values (like 6.1500000001).

(I should point that out when I do that.)

***********
Regards,
Ron


"Sloth" wrote:

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.

You can replace all "" with 0 (zeroes) and all & with +.
This will keep the formula as it is (I mean without enclosing it in
VALUE function).

I'm just not sure if this solution meets all possible outcomes you may
encounter. Check it out :)


--
nCage
------------------------------------------------------------------------
nCage's Profile: http://www.excelforum.com/member.php...o&userid=29605
View this thread: http://www.excelforum.com/showthread...hreadid=493025