| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use of IF function
I want to use the IF function more than 7 times. Can anybody tell me how to
do it ? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use of IF function
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,I F($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=1 2,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, |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use of IF function
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,60 0),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(H817, 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,I F($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=1 2,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, |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use of IF function
"Bermie66" wrote in message
... "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,I F($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=1 2,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,60 0),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(H817, 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="",H818),"",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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use of IF function
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,"")&I F(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,160 5,"")&IF(H8=16,1635,"")&IF(H8=17,1665,"")&IF(H817 ,1695,"") Tell me if this is what you wanted. "Bermie66" wrote in message ... 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,60 0),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(H817, 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,I F($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=1 2,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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
| Date & Time | New Users to Excel | |||
| Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
| Conversion | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Linear Mode
