Use of IF function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Avinash - 15 Jun 2006 06:57 GMT

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

Reply to this Message

VN - 15 Jun 2006 07:42 GMT

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,

Reply to this Message

Bermie66 - 15 Jun 2006 14:34 GMT

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(H8=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

> Suppose , you will check this following details:
>
[quoted text clipped - 30 lines]
>
> VN,

Reply to this Message

David Biddulph - 15 Jun 2006 15:18 GMT

>> Suppose , you will check this following details:
>>
[quoted text clipped - 30 lines]
>>
>> VN,

> Hi VN,
>
[quoted text clipped - 15 lines]
>
> 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.

Signature

David Biddulph

Reply to this Message

ilmahy - 16 Jun 2006 01:10 GMT

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.

> Hi VN,
>
[quoted text clipped - 50 lines]
>>
>> VN,

Reply to this Message