Thread: Use of IF function

I want to use the IF function more than 7 times. Can anybody tell me how to
do it ?

Suppose , you will check this following details:

1. If A1 = 1 then 1
2. If A1 = 2 then 2
3. If A1 = 3 then 3
4. If A1 = 4 then 4
5. If A1 = 5 then 5
6. If A1 = 6 then 6
7. If A1 = 7 then 7
8. If A1 = 8 then 8
9. If A1 = 9 then 9
10. If A1 = 10 then 10
11. If A1 = 11 then 11
12. If A1 = 12 then 12
13. If A1 = 13 then 13
14. If A1 = 14 then 14
15. If A1 = 15 then 15



Define this formula as OneToSix:

Example:
=IF($A$1=1,1,IF($A$1=2,2,IF($A$1=3,3,IF($A$1=4,4,IF($A$1=5,5,IF
($A$1=6,6,IF($A$1=7,7,IF($A$1=8,8,FALSE))))))))

and your formula as SevenToThirteen:

=IF($A$1=9,9,IF($A$1=10,10,IF($A$1=11,11,IF($A$1=12,12,IF($A$1=13,13,
IF($A$1=14,14,IF($A$1=15,15,"NotFound")))))))

The combined formula looks like this:

=IF(OneToSix,OneToSix,SevenToThirteen)


VN,

Hi VN,

I have a 19 item formula. Someone suggested that I use the VLookUp but that
is not working for me. I see your example below and am wondering if you can
help me with my formula below.

=IF(H8=1,150),IF(H8=2,300),IF(H8=3,450),IF(H8=4,600),IF(H8=5,750),IF(H8=6,900),IF(H8=7,1015),IF(H8=8 ,1130),IF(H8=9,1335),IF(H8=10,1385),IF(H8=11,1435),IF(H8=12,1485),IF(H8=13,1525),IF(H8=14,1565),IF(H 8=15,1605),IF(H8=16,1635),IF(H8=17,1665),IF(H8>17,1695),IF(H8=" "," ")

I don't understand how you are saying to make it into two or three formulas.
I am going to work on understanding your explanation while I wait for your
answer to my problem.

Thanks.

Bermie66

"VN" wrote:

>
> Suppose , you will check this following details:
>
> 1. If A1 = 1 then 1
> 2. If A1 = 2 then 2
> 3. If A1 = 3 then 3
> 4. If A1 = 4 then 4
> 5. If A1 = 5 then 5
> 6. If A1 = 6 then 6
> 7. If A1 = 7 then 7
> 8. If A1 = 8 then 8
> 9. If A1 = 9 then 9
> 10. If A1 = 10 then 10
> 11. If A1 = 11 then 11
> 12. If A1 = 12 then 12
> 13. If A1 = 13 then 13
> 14. If A1 = 14 then 14
> 15. If A1 = 15 then 15
>
>
>
> Define this formula as OneToSix:
>
> Example:
> =IF($A$1=1,1,IF($A$1=2,2,IF($A$1=3,3,IF($A$1=4,4,IF($A$1=5,5,IF
> ($A$1=6,6,IF($A$1=7,7,IF($A$1=8,8,FALSE))))))))
>
> and your formula as SevenToThirteen:
>
> =IF($A$1=9,9,IF($A$1=10,10,IF($A$1=11,11,IF($A$1=12,12,IF($A$1=13,13,
> IF($A$1=14,14,IF($A$1=15,15,"NotFound")))))))
>
> The combined formula looks like this:
>
> =IF(OneToSix,OneToSix,SevenToThirteen)
>
>
> VN,
>
>

"Bermie66" <Bermie66@discussions.microsoft.com> wrote in message
news:14BA091B-1423-4A39-A8F9-9082068E6EAB@microsoft.com...

> "VN" wrote:
>
>>
>> Suppose , you will check this following details:
>>
>> 1. If A1 = 1 then 1
>> 2. If A1 = 2 then 2
>> 3. If A1 = 3 then 3
>> 4. If A1 = 4 then 4
>> 5. If A1 = 5 then 5
>> 6. If A1 = 6 then 6
>> 7. If A1 = 7 then 7
>> 8. If A1 = 8 then 8
>> 9. If A1 = 9 then 9
>> 10. If A1 = 10 then 10
>> 11. If A1 = 11 then 11
>> 12. If A1 = 12 then 12
>> 13. If A1 = 13 then 13
>> 14. If A1 = 14 then 14
>> 15. If A1 = 15 then 15
>>
>>
>>
>> Define this formula as OneToSix:
>>
>> Example:
>> =IF($A$1=1,1,IF($A$1=2,2,IF($A$1=3,3,IF($A$1=4,4,IF($A$1=5,5,IF
>> ($A$1=6,6,IF($A$1=7,7,IF($A$1=8,8,FALSE))))))))
>>
>> and your formula as SevenToThirteen:
>>
>> =IF($A$1=9,9,IF($A$1=10,10,IF($A$1=11,11,IF($A$1=12,12,IF($A$1=13,13,
>> IF($A$1=14,14,IF($A$1=15,15,"NotFound")))))))
>>
>> The combined formula looks like this:
>>
>> =IF(OneToSix,OneToSix,SevenToThirteen)
>>
>>
>> VN,

> Hi VN,
>
> I have a 19 item formula. Someone suggested that I use the VLookUp but
> that
> is not working for me. I see your example below and am wondering if you
> can
> help me with my formula below.
>
> =IF(H8=1,150),IF(H8=2,300),IF(H8=3,450),IF(H8=4,600),IF(H8=5,750),IF(H8=6,900),IF(H8=7,1015),IF(H8=8 ,1130),IF(H8=9,1335),IF(H8=10,1385),IF(H8=11,1435),IF(H8=12,1485),IF(H8=13,1525),IF(H8=14,1565),IF(H 8=15,1605),IF(H8=16,1635),IF(H8=17,1665),IF(H8>17,1695),IF(H8="
> "," ")
>
> I don't understand how you are saying to make it into two or three
> formulas.
> I am going to work on understanding your explanation while I wait for your
> answer to my problem.
>
> Thanks.
>
> Bermie66

I don't know why VLOOKUP isn't working for you.

Try the formula =IF(OR(H8="",H8>18),"",VLOOKUP(H8,Sheet2!A1:B19,2))
and put your lookup on Sheet 2 (or somewhere else convenient) as follows:
1 150
2 300
3 450
4 600
5 750
6 900
7 1015
8 1130
9 1335
10 1385
11 1435
12 1485
13 1525
14 1565
15 1605
16 1635
17 1665
18 1695
19

You may need to think about whether H8 can be non-integer, or less than 1.

You might, of course, be able to split the formula, such as 1 to 6 where the
answer =6*H8, but it's probably not worth doing that.
--
David Biddulph

I thought this was one of those well known 'workarounds' of the Excel 7 if
limit.
Use &IF after first IF. I believe the use of &IF is unlimited, correct me if
I am wrong. Note that I have taken off the last IF(H8=" "," ") as the rest
of the formula should take care of it.
Try this formula instead.

=IF(H8=1,150,"")&IF(H8=2,300,"")&IF(H8=3,450,"")&IF(H8=4,600,"")&IF(H8=5,750,"")&IF(H8=6,900,"")&IF( H8=7,1015,"")&IF(H8=8,1130,"")&IF(H8=9,1335,"")&IF(H8=10,1385,"")&IF(H8=11,1435,"")&IF(H8=12,1485,"" )&IF(H8=13,1525,"")&IF(H8=14,1565,"")&IF(H8=15,1605,"")&IF(H8=16,1635,"")&IF(H8=17,1665,"")&IF(H8>17 ,1695,"")

Tell me if this is what you wanted.


"Bermie66" <Bermie66@discussions.microsoft.com> wrote in message
news:14BA091B-1423-4A39-A8F9-9082068E6EAB@microsoft.com...
> Hi VN,
>
> I have a 19 item formula. Someone suggested that I use the VLookUp but
> that
> is not working for me. I see your example below and am wondering if you
> can
> help me with my formula below.
>
> =IF(H8=1,150),IF(H8=2,300),IF(H8=3,450),IF(H8=4,600),IF(H8=5,750),IF(H8=6,900),IF(H8=7,1015),IF(H8=8 ,1130),IF(H8=9,1335),IF(H8=10,1385),IF(H8=11,1435),IF(H8=12,1485),IF(H8=13,1525),IF(H8=14,1565),IF(H 8=15,1605),IF(H8=16,1635),IF(H8=17,1665),IF(H8>17,1695),IF(H8="
> "," ")
>
> I don't understand how you are saying to make it into two or three
> formulas.
> I am going to work on understanding your explanation while I wait for your
> answer to my problem.
>
> Thanks.
>
> Bermie66
>
> "VN" wrote:
>
>>
>> Suppose , you will check this following details:
>>
>> 1. If A1 = 1 then 1
>> 2. If A1 = 2 then 2
>> 3. If A1 = 3 then 3
>> 4. If A1 = 4 then 4
>> 5. If A1 = 5 then 5
>> 6. If A1 = 6 then 6
>> 7. If A1 = 7 then 7
>> 8. If A1 = 8 then 8
>> 9. If A1 = 9 then 9
>> 10. If A1 = 10 then 10
>> 11. If A1 = 11 then 11
>> 12. If A1 = 12 then 12
>> 13. If A1 = 13 then 13
>> 14. If A1 = 14 then 14
>> 15. If A1 = 15 then 15
>>
>>
>>
>> Define this formula as OneToSix:
>>
>> Example:
>> =IF($A$1=1,1,IF($A$1=2,2,IF($A$1=3,3,IF($A$1=4,4,IF($A$1=5,5,IF
>> ($A$1=6,6,IF($A$1=7,7,IF($A$1=8,8,FALSE))))))))
>>
>> and your formula as SevenToThirteen:
>>
>> =IF($A$1=9,9,IF($A$1=10,10,IF($A$1=11,11,IF($A$1=12,12,IF($A$1=13,13,
>> IF($A$1=14,14,IF($A$1=15,15,"NotFound")))))))
>>
>> The combined formula looks like this:
>>
>> =IF(OneToSix,OneToSix,SevenToThirteen)
>>
>>
>> VN,
>>
>>