anyone know a formula?

[bill_robinson]

Hello everyone- i work for a very small non-profit and need some help
calculating some spreadsheet values. if anyone can help with this
problem, i would really appreciate it. here it goes....

i am putting together a list of busnesses and am trying to calculate a
formula based on the number of employees they have and the
classification of the business.. for example... AAA enterprises has 52
employees and a manager.
i need to calculate how much in dues they should pay..

for the manager they pay $200, next 9 people each at $17, next 10
people each $14, next 30 each $9, and next 50 people each and over $7.
the total for AAA would be $777.. is there a way to set up a formula so
i can just put the number of employees and let the spreadsheet do the
work for me???

thanks for your help...

 

[N Harkawat]

Assuming A1 holds the number of employees and cell B1 holds the number of
managers

=MIN(A1,9)*17+MIN(A1-9,10)*14*(A1>9)+MAX(A1-19,0)*7+B1*200

For 52 employees and a manager I get 724 and not 777
9*17 + 10*14 + 33*7 = 524 + 200(for manager) = 724


"bill_robinson" <[email protected]>
wrote in message
news:[email protected]...

 

[JE McGimpsey]

One way:

Assuming that each business has a manager and that cell A1 contains the
total number of employees (including the manager):

=SUMPRODUCT(--(A1>{0,1,10,20,50}),(A1-{0,1,10,20,50}),
{200,-183,-3,-5,-2})

See

http://www.mcgimpsey.com/excel/variablerate.html

for an explanation, and some more flexible alternatives.


bill_robinson