IF function problem

[LdoubleE80]

I've read through some other 'IF' problems but I'm still confused, s
here goes.

I've used up my 7 IF statements, but have not gotten through all th
data that I need to. What I need it to do is this:

Any number <=12 =12

12<=24 = 24
24<=30 = 30
30<=36 = 36
36<=40 = 40
40<=42 = 42
42<=48 = 48
48<=54 = 54
54<=60 = 60
60<=72 = 72
72<=96 = 96
96<=120 = 120

That is the first limitation. The second one is a bit more complex.

There are 12 different combinations that I have to take into account
and each combination needs to show a different value.

What I tried to d
was=IF(AND(D27<=0.5,H27<=29.9),0.067,IF(AND(D27>0.5,H27<=29.9),0.1,IF(AND(D27<=0.5,H27<=35.9),0.09,IF(AND(D27>0.5,H27<=35.9),0.135,IF(AND(D27<=0.5,H27<=39.9),0.116,IF(AND(D27>0.5,H27<=39.9),0.175,IF(AND(D27<=0.5,H27<=41.9),0.129,)))))))

However, I ran out. As you can see there are 2 different possibilitie
for variable A, and after that is identified 6 possible variables of
of each.

AA BA
AB BB
AC BC
AD BD
AE BE
AF BF

How do I get these 12 combinations to show 12 different variables, whe
the variables are ranges of number (ie. 30-35.9)

I hope I explained that enough.

Thanks guys

 

[Frank Kabel]

Hi
to be honest i could not get your parts together
For both a lookup table and using eithr VLOOKUP (for the first part) or
a combination of MATCH/INDEX (for the second part with two conditions)
will do.

and after this you have lost me with your 'AB', etc. combinations. Sou
you may post some sample data (text only - no attachment please) and
explain the logic of your conditions/IF functions based on this example
data


--
Regards
Frank Kabel
Frankfurt, Germany

I've read through some other 'IF' problems but I'm still confused, so
here goes.

I've used up my 7 IF statements, but have not gotten through all the
data that I need to. What I need it to do is this:

Any number <=12 =12

That is the first limitation. The second one is a bit more complex.

There are 12 different combinations that I have to take into account,
and each combination needs to show a different value.

What I tried to do

was=IF(AND(D27<=0.5,H27<=29.9),0.067,IF(AND(D27>0.5,H27<=29.9),0.1,IF(A
ND(D27<=0.5,H27<=35.9),0.09,IF(AND(D27>0.5,H27<=35.9),0.135,IF(AND(D27<

 

[Peo Sjoblom]

For your first problem you can use

=INDEX({0;12;24;30;36;40;42;48;54;60;72;96;120},MATCH(MIN(IF({0;12;24;30;36;
40;42;48;54;60;72;96;120}>=D27,{0;12;24;30;36;40;42;48;54;60;72;96;120})),{0
;12;24;30;36;40;42;48;54;60;72;96;120},0))

entered with ctrl + shift & enter

for the second you can use

=IF(H27>42,0,--(IF(AND(D27<=0.5,H27<=30),0.067,"")&IF(AND(D27>0.5,H27<=30),0
..1,"")&IF(AND(D27<=0.5,H27>30,H27<=36),0.09,"")&IF(AND(D27>0.5,H27>30,H27<=3
6),0.135,"")&IF(AND(D27<=0.5,H27>36,H27<=40),0.116,"")&IF(AND(D27>0.5,H27>36
,H27<=40),0.175,"")&IF(AND(D27<=0.5,H27>40,H27<42),0.129,"")))

or

=IF(D27<=0.5,VLOOKUP(H27,{0,0.067;30,0.09;36,0.116;40,0.129},2),VLOOKUP(H27,
{0,0.1;30,0.135;36,0.175},2))

 

[Stacy Haskins]

Try creating a function in VB using a case statement
instead. [Look below.] You could also nest other case
statements for addtional conditions. In the Excel cell,
you would type "=NumberCheck(D27)" - or whatever cell you
wanted to check.

Function NumberCheck (Number)
Select Case Number
Case Is <= 12
NumberCheck = 12
Case 13 to 24
NumberCheck = 24
Case 25 to 36
NumberCheck = 36
Case 37 to 40
NumberCheck = 40

 

[Guest]

HI Ldouble80

For your first problem you can set up a simple VLOOKUP. Set up a table using just the low number in each range in the first column, and the desired return value in the second column. Then a formula similar to

=VLOOKUP(A1,Sheet2!A1:B13,2

will return the correct value. VLOOKUP will look in the first column for the closest value which is smaller then the lookup value

You could set up something smilar for the second problem by putting 2 VLOOKUPS inside an IF statement

=IF(D27<=0.5,VLOOKUP(A1,Sheet2!D1:E6,2),VLOOKUP(A1,Sheet2!G1:H6,2)

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- LdoubleE80 > wrote: ----

I've read through some other 'IF' problems but I'm still confused, s
here goes

I've used up my 7 IF statements, but have not gotten through all th
data that I need to. What I need it to do is this

Any number <=12 =1

12<=24 = 2
24<=30 = 3
30<=36 = 3
36<=40 = 4
40<=42 = 4
42<=48 = 4
48<=54 = 5
54<=60 = 6
60<=72 = 7
72<=96 = 9
96<=120 = 12

That is the first limitation. The second one is a bit more complex

There are 12 different combinations that I have to take into account
and each combination needs to show a different value

What I tried to d
was=IF(AND(D27<=0.5,H27<=29.9),0.067,IF(AND(D27>0.5,H27<=29.9),0.1,IF(AND(D27<=0.5,H27<=35.9),0.09,IF(AND(D27>0.5,H27<=35.9),0.135,IF(AND(D27<=0.5,H27<=39.9),0.116,IF(AND(D27>0.5,H27<=39.9),0.175,IF(AND(D27<=0.5,H27<=41.9),0.129,))))))

However, I ran out. As you can see there are 2 different possibilitie
for variable A, and after that is identified 6 possible variables of
of each.

AA B
AB B
AC B
AD B
AE B
AF B

How do I get these 12 combinations to show 12 different variables, whe
the variables are ranges of number (ie. 30-35.9

I hope I explained that enough

Thanks guys

 

[Peo Sjoblom]

Mark,

I believe the OP wanted the other way around in his/her first problem, if
less than 12 then 12, if less than 24 but greater than 12 then 24
as an example vlookup would return 12 for 15 and not 24.

--

Regards,

Peo Sjoblom

HI Ldouble80,

For your first problem you can set up a simple VLOOKUP. Set up a table

using just the low number in each range in the first column, and the desired
return value in the second column. Then a formula similar to:

=VLOOKUP(A1,Sheet2!A1:B13,2)

will return the correct value. VLOOKUP will look in the first column for

the closest value which is smaller then the lookup value.

You could set up something smilar for the second problem by putting 2

VLOOKUPS inside an IF statement:

 

[Guest]

Hi Peo,
The way I set it up a look_up value of 15 will return 24. If I wasn't clear, the table should be set up as follows.

Range Bottom Return Value
0 12
12 24
24 30
30 36
36 40
40 42
..... .....

However, since you brought it up, I took a closer look and realized that this is still wrong. In this setup a look_up value of 12 will return a value of 24, but the OP stills wants 12. In order to get this to work one could use the following

=INDEX(B1:B12,MATCH(D1,A1:A12,-1))

D1 is the look_up value
A1:A12 is the HIGH end number of the range.
B1:B12 is the return value
The table needs to be in DESCENDING order

120 120
96 96
72 72
... ...

And now that I actually typed this, I see that since the return value is equal to the high end value of the range you could just use:

=INDEX(A1:A12,MATCH(D1,A1:A12,-1))

Now if you enter 12 you will get 12.

I've been off the board for a while and I'm a little slow getting started again.

Regards,
Mark Graesser
(e-mail address removed)
Boston MA



----- Peo Sjoblom wrote: -----

Mark,

I believe the OP wanted the other way around in his/her first problem, if
less than 12 then 12, if less than 24 but greater than 12 then 24
as an example vlookup would return 12 for 15 and not 24.

--

Regards,

Peo Sjoblom

HI Ldouble80,

using just the low number in each range in the first column, and the desired
return value in the second column. Then a formula similar to:

 

[LdoubleE80]

I got it figured out. I used VLOOKUP on both. For the first, the tabl
just looked like this

0 12
12 12
12.1 24
24 24
24.1 30
30 30
30.1 36


and so on.

For the second problem I also used a VLOOKUP within an if statement. I
worked perfectly. Thanks

 

[Guest]

Glad to hear you got what you needed. The 12-12, 24-24, 30-30,... lines aren't really needed. You could remove them for a cleaner table and still get the same results

Regards
Mark Graesse
(e-mail address removed)
Boston MA