Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Function too Large
The function below is a partial version of the correct one; I will exceed the
allowed characters for the field (too many arguments). I notice I am using different arguments which could be combined as a range: C16=5:7 for the same results, in other words 5, 6 & 7 all = E20 as a result. =IF(C16=5,E20,IF(C16=6,E20,IF(C16=7,E20,IF(C16=8,E 21,IF(C16=9,E21,IF(C16=10,E22,IF(C16=11,E22,IF(C16 11,E23,0)))))))) How can I build this function combining criteria as a range for a common result? -- Thank you, Trying Hard |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Function too Large
Here are a couple things you might try:
If there won't be a huge number of options AND the pattern of referenced cells may vary: =CHOOSE(INT((C16-2)/3),C20,C21,C22,C23,C24,C25,C26,C27,C28) (you can add other cell references to that list) Or, the reference pattern is consistent: =INDEX(C:C,20+INT((C16-5)/3)) Does that give you something to work with? *********** Regards, Ron XL2002, WinXP-Pro "Trying Hard" wrote: The function below is a partial version of the correct one; I will exceed the allowed characters for the field (too many arguments). I notice I am using different arguments which could be combined as a range: C16=5:7 for the same results, in other words 5, 6 & 7 all = E20 as a result. =IF(C16=5,E20,IF(C16=6,E20,IF(C16=7,E20,IF(C16=8,E 21,IF(C16=9,E21,IF(C16=10,E22,IF(C16=11,E22,IF(C16 11,E23,0)))))))) How can I build this function combining criteria as a range for a common result? -- Thank you, Trying Hard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF Function too Large
Ron,
I messed up and posted twice, I ended up with: =INDEX(E19:E23,MATCH(C16,{0,5,7,10,15})) -- Thank you, Trying Hard "Ron Coderre" wrote: Here are a couple things you might try: If there won't be a huge number of options AND the pattern of referenced cells may vary: =CHOOSE(INT((C16-2)/3),C20,C21,C22,C23,C24,C25,C26,C27,C28) (you can add other cell references to that list) Or, the reference pattern is consistent: =INDEX(C:C,20+INT((C16-5)/3)) Does that give you something to work with? *********** Regards, Ron XL2002, WinXP-Pro "Trying Hard" wrote: The function below is a partial version of the correct one; I will exceed the allowed characters for the field (too many arguments). I notice I am using different arguments which could be combined as a range: C16=5:7 for the same results, in other words 5, 6 & 7 all = E20 as a result. =IF(C16=5,E20,IF(C16=6,E20,IF(C16=7,E20,IF(C16=8,E 21,IF(C16=9,E21,IF(C16=10,E22,IF(C16=11,E22,IF(C16 11,E23,0)))))))) How can I build this function combining criteria as a range for a common result? -- Thank you, Trying Hard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA function for "Mean" using Array as argument | Excel Worksheet Functions | |||
creating function (vba) with range arguments | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Help on Large Function | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |