Home |
Search |
Today's Posts |
#1
|
|||
|
|||
nesting 18 x functions
Help, anyone?
I am trying to nest 18 x IF functions to automatically pick up a text value for a numeric result. I have ranked 18 x actual values in 3 x cell in a column for my top 3 x performers (value of sales). what I now want to do is on the next row have the name of that person appear under that specific ranking number according to the actual sales (so I don't need to scroll up to see who produced that value). Nesting only allows me 7 x IF's. Help anyone ??? |
#2
|
|||
|
|||
Hi - whilst you can only nesti 7 IF statements, you CAN do this by NOT
nesting eg if your IF statement with nesting was like this: =IF(A1=1,2,if(A1=2,3 etc etc etc this can also be written as: =IF(A1=1,2,"") & IF(A1=2,3,"") & IF(A1 etc etc etc to return a VALUE, you would need to enclose the above with VALUE() eg =VALUE(IF(A1=1,2,"") & IF(A1=2,3,"") & IF(A1=3,4,"")) This type of formula has no limit to the number of functions but IS restricted by the length of text a formula can hold (but you should be ok here) However, this type of formula is inefficient and from what you describe, you may be better off investigating VLOOKUP or a combination of INDEX / MATCH HTH Rgds Geoff "Jenny" wrote: Help, anyone? I am trying to nest 18 x IF functions to automatically pick up a text value for a numeric result. I have ranked 18 x actual values in 3 x cell in a column for my top 3 x performers (value of sales). what I now want to do is on the next row have the name of that person appear under that specific ranking number according to the actual sales (so I don't need to scroll up to see who produced that value). Nesting only allows me 7 x IF's. Help anyone ??? |
#3
|
|||
|
|||
You wouldn't use IF for this you would use something like VLOOKUP, eg:-
assuming your table was in say A1:B9 A B 1 101 abc 2 102 bcd 3 103 cde 4 104 def 5 105 efg 6 106 fgh 7 107 ghi 8 108 hij 9 109 ijk and in say cell D1 you had the value of 104 and in E1 you wanted the corresponding value from col B that matched that value in Col A. In cell E1 you would simply use the formula =VLOOKUP(D1,A1:B9,2,0) which says, take the value in D1, look it up in the leftmost column of the range A1:B9 and then when you match it exactly (0 at end denotes exact match) give me the value from the 2nd column (Hence the 2), in that range. Regards Ken................... "Jenny" wrote: Help, anyone? I am trying to nest 18 x IF functions to automatically pick up a text value for a numeric result. I have ranked 18 x actual values in 3 x cell in a column for my top 3 x performers (value of sales). what I now want to do is on the next row have the name of that person appear under that specific ranking number according to the actual sales (so I don't need to scroll up to see who produced that value). Nesting only allows me 7 x IF's. Help anyone ??? |
#4
|
|||
|
|||
Hi
Another option, when the number of possible values doesn't exceed 24 and the lookup table doesn't change in futu =(CHOOSE(MATCH(D1,{101,102,103,104,105,106,107,108 ,109},1),"abc","bcd","cde" ,"def","efg","fgh","ghi","hij","ijk") -- Arvi Laanemets (When sending e-mail, use address arvil<Attarkon.ee) "Ken Wright" wrote in message ... You wouldn't use IF for this you would use something like VLOOKUP, eg:- assuming your table was in say A1:B9 A B 1 101 abc 2 102 bcd 3 103 cde 4 104 def 5 105 efg 6 106 fgh 7 107 ghi 8 108 hij 9 109 ijk and in say cell D1 you had the value of 104 and in E1 you wanted the corresponding value from col B that matched that value in Col A. In cell E1 you would simply use the formula =VLOOKUP(D1,A1:B9,2,0) which says, take the value in D1, look it up in the leftmost column of the range A1:B9 and then when you match it exactly (0 at end denotes exact match) give me the value from the 2nd column (Hence the 2), in that range. Regards Ken................... "Jenny" wrote: Help, anyone? I am trying to nest 18 x IF functions to automatically pick up a text value for a numeric result. I have ranked 18 x actual values in 3 x cell in a column for my top 3 x performers (value of sales). what I now want to do is on the next row have the name of that person appear under that specific ranking number according to the actual sales (so I don't need to scroll up to see who produced that value). Nesting only allows me 7 x IF's. Help anyone ??? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable canned functions | Excel Discussion (Misc queries) | |||
Changing the language of built-in functions? | New Users to Excel | |||
How to load Engineering Functions into the Fx function wizard? | Excel Worksheet Functions | |||
AND and IF Functions | Excel Worksheet Functions | |||
Where can I see VBA code for financial functions? | Excel Worksheet Functions |