View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default IF statements with more than 7 variables

If your costs per extra user always decrease by the same amount you can
create a single formula that does not rely on a look up table
harking back to school days and with a little bit of help from Dr Maths
http://mathforum.org/dr.math/

1+2+3+4+.....+n
=n((n+1)/2)

http://mathforum.org/library/drmath/view/56073.html
for the proof

It the case of
150 + 145 + 140 +...+ (150-5*(n-1))
=n(150+(150-5*(n-1)))/2

or generaly
=Users(First+(First-Step*(Users-1)))/2

You can simplify this down. I have lest it expanded to make it easier to
addapt.

On a lighter note - if the rule stands - more than 61 users and you will
pay them to take it.

******************************
Alternatively
If you would prefer to use a lookup type table I would use the Offset
function
It is a Volatile function so could affect calculate speed. But good if
this is not an issue.

List of prices in A1:A11 including a header
B1 location of number of users

=SUM(OFFSET(A1:A11,1,0,B1,1))
******************************

hth RES