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.

Can you give any examples of the formulas that are involved?

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


"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.

Could be maybe that the "63" that is not being detected by the MAX function
is either inputted as TEXT, or is out of range of the MAX statement.

Vaya con Dios,
Chuck, CABGx3



"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.

Hmm... strange

First try to format the cells that your IF statement returning the
values from to numbers.

If that doesn't work, try =VALUE(IF(your if statement))

if neither work, why don't you post your formulas.


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

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.

Yes, use a lookup. Enter your values in a table, perhaps on Sheet2:

A B
1 0 0.4
2 0.420000001 0.63
3 0.660000001 1.05
....

then use

=IF(K210,VLOOKUP(K21,Sheet2!A:B,2,TRUE),"")


In article ,
"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?

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.

Try this:

On another sheet, I'll assume Sheet2, create this table in A1:B18...
0.00 0.40
0.42 0.40
0.66 0.63
1.05 1.00
1.68 1.60
2.63 2.50
4.20 4.00
6.62 6.30
10.50 10.00
16.80 16.00
26.25 25.00
42.00 40.00
66.15 63.00
105.00 100.00
168.00 160.00
262.50 250.00
420.00 400.00
10000 0.00

Then, on Sheet1 (where I'll assume your values are in Col_A):
A1: (some value)
B1: =VLOOKUP(A1,Sheet2!$A$1:$B$18,2,1)

Does that help?

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


"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.

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

Thanks for all your help, the VLOOKUP worked great.

"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.