software

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 amp; 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 gt;11,E23,0))))))))

How can I build this function combining criteria as a range for a common
result?

--
Thank you,

Trying Hard

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-Proquot;Trying Hardquot; wrote:

gt; The function below is a partial version of the correct one; I will exceed the
gt; allowed characters for the field (too many arguments). I notice I am using
gt; different arguments which could be combined as a range: C16=5:7 for the same
gt; results, in other words 5, 6 amp; 7 all = E20 as a result.
gt;
gt; =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 gt;11,E23,0))))))))
gt;
gt; How can I build this function combining criteria as a range for a common
gt; result?
gt;
gt; --
gt; Thank you,
gt;
gt; Trying Hard

Ron,

I messed up and posted twice, I ended up with:

=INDEX(E19:E23,MATCH(C16,{0,5,7,10,15}))--
Thank you,

Trying Hardquot;Ron Coderrequot; wrote:

gt; Here are a couple things you might try:
gt;
gt; If there won't be a huge number of options AND the pattern of referenced
gt; cells may vary:
gt; =CHOOSE(INT((C16-2)/3),C20,C21,C22,C23,C24,C25,C26,C27,C28)
gt; (you can add other cell references to that list)
gt;
gt; Or, the reference pattern is consistent:
gt; =INDEX(C:C,20 INT((C16-5)/3))
gt;
gt;
gt; Does that give you something to work with?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Trying Hardquot; wrote:
gt;
gt; gt; The function below is a partial version of the correct one; I will exceed the
gt; gt; allowed characters for the field (too many arguments). I notice I am using
gt; gt; different arguments which could be combined as a range: C16=5:7 for the same
gt; gt; results, in other words 5, 6 amp; 7 all = E20 as a result.
gt; gt;
gt; gt; =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 gt;11,E23,0))))))))
gt; gt;
gt; gt; How can I build this function combining criteria as a range for a common
gt; gt; result?
gt; gt;
gt; gt; --
gt; gt; Thank you,
gt; gt;
gt; gt; Trying Hard