IF statement

T

Tom B

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

Ron Coderre

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

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

CLR

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
 
A

anar_baku

Hmm... strange

First try to format the cells that your IF statement returning th
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
 
T

Tom B

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(K21>0, K21<=0.42), 0.4, "")&IF(AND(K21>0.42, K21<=0.66), 0.63,
"")&IF(AND(K21>0.66, K21<=1.05), 1, "")&IF(AND(K21>1.05, K21<=1.68), 1.6,
"")&IF(AND(K21>1.68, K21<=2.625), 2.5, "")&IF(AND(K21>2.625, K21<=4.2), 4,
"")&IF(AND(K21>4.2, K21<=6.615), "6.3", "")&IF(AND(K21>6.615, K21<=10.5),
"10", "")&IF(AND(K21>10.5, K21<=16.8), "16", "")&IF(AND(K21>16.8,
K21<=26.25), 25, "")&IF(AND(K21>26.25, K21<=42), 40, "")&IF(AND(K21>42,
K21<=66.15), 63, "")&IF(AND(K21>66.15, K21<=105), 100, "")&IF(AND(K21>105,
K21<=168), 160, "")&IF(AND(K21>168, K21<=262.5), 250, "")&IF(AND(K21>262.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?
 
J

JE McGimpsey

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(K21>0,VLOOKUP(K21,Sheet2!A:B,2,TRUE),"")
 
S

Sloth

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(K21>0, K21<=0.42), 0.4, "")&IF(AND(K21>0.42, K21<=0.66), 0.63,
"")&IF(AND(K21>0.66, K21<=1.05), 1, "")&IF(AND(K21>1.05, K21<=1.68), 1.6,
"")&IF(AND(K21>1.68, K21<=2.625), 2.5, "")&IF(AND(K21>2.625, K21<=4.2), 4,
"")&IF(AND(K21>4.2, K21<=6.615), "6.3", "")&IF(AND(K21>6.615, K21<=10.5),
"10", "")&IF(AND(K21>10.5, K21<=16.8), "16", "")&IF(AND(K21>16.8,
K21<=26.25), 25, "")&IF(AND(K21>26.25, K21<=42), 40, "")&IF(AND(K21>42,
K21<=66.15), 63, "")&IF(AND(K21>66.15, K21<=105), 100, "")&IF(AND(K21>105,
K21<=168), 160, "")&IF(AND(K21>168, K21<=262.5), 250, "")&IF(AND(K21>262.5,
K21<=420), 400, ""))
 
R

Ron Coderre

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
 
S

Sloth

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.25,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.615,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)
 
R

Ron Coderre

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
 
N

nCage

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

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