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...


--
bill_robinson
------------------------------------------------------------------------
bill_robinson's Profile: http://www.excelforum.com/member.php...o&userid=25397
View this thread: http://www.excelforum.com/showthread...hreadid=389886

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

=MIN(A1,9)*17+MIN(A1-9,10)*14*(A19)+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"
wrote in message
news:bill_robinson.1sqdn3_1122304029.6614@excelfor um-nospam.com...

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...


--
bill_robinson
------------------------------------------------------------------------
bill_robinson's Profile:
http://www.excelforum.com/member.php...o&userid=25397
View this thread: http://www.excelforum.com/showthread...hreadid=389886

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.


In article
,
bill_robinson
wrote:

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...