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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...

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

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


In article
<[email protected]>,
bill_robinson
<[email protected]> 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...