I have a spreadsheet set up that calculates my total production volume and is
entered in a cell for Totals. I want to take that total number and calculate
commissions using the following formula:
if volume 0-500000, multiply by .0045
if 500001-750000, multiply by .0050
if 750001-1250000, multiply by .0055
if 1250001-2000000, multiply by .0060
if 2000000 and above, multiply by .0065

and return the answer to the cell labeled "Commission"
--
Rick

Create a table as below say in Sheet2 A1 to B5 with Volumes & Commission rates:

A B
0 0.0045
500001 0.0050
750001 0.0055
1250001 0.0060
2000001 0.0065

With production volume in A1 of Sheet1

in "Commission" (assuming it is on Sheet1 also) cell put

=A1*VLOOKUP(A1,Sheet2!$A$1:$B$5,2,1)

Alternative options:

=A1*LOOKUP(A1,Sheet2!$A$1:$A$5,Sheet2!$B$1:$B$5)

or (but less flexible than using a table):

=A1*LOOKUP(A1,{0,500001,750001,1250001,2000001},{0 .0045,0.005,0.0055,0.006,0.0065})


HTH


"Rick" wrote:

I have a spreadsheet set up that calculates my total production volume and is
entered in a cell for Totals. I want to take that total number and calculate
commissions using the following formula:
if volume 0-500000, multiply by .0045
if 500001-750000, multiply by .0050
if 750001-1250000, multiply by .0055
if 1250001-2000000, multiply by .0060
if 2000000 and above, multiply by .0065

and return the answer to the cell labeled "Commission"
--
Rick

=IF(A302000000,A30*0.0065,IF(A30=1250001,A30*0.0 06,IF(A30=750001,A30*0.0055,IF(A30=500001,A30*0. 005,IF(A30=0, A30*0.0045,"")))))

computed for total in A30, adjust as necessary.

"Rick" wrote:

I have a spreadsheet set up that calculates my total production volume and is
entered in a cell for Totals. I want to take that total number and calculate
commissions using the following formula:
if volume 0-500000, multiply by .0045
if 500001-750000, multiply by .0050
if 750001-1250000, multiply by .0055
if 1250001-2000000, multiply by .0060
if 2000000 and above, multiply by .0065

and return the answer to the cell labeled "Commission"
--
Rick

=IF(D12=2000000,D12*0.0065,IF(D12=1250001,D12*0. 006,IF(D12=750001,D12*0.0055,IF(D12=500001,D12*0 .005,D12*0.0045))))

or should it be

=IF(D12=2000001,D12.......................
just curious why only the top range starts at 2000000

--
Greetings from New Zealand

"Rick" wrote in message
...
I have a spreadsheet set up that calculates my total production volume and
is
entered in a cell for Totals. I want to take that total number and
calculate
commissions using the following formula:
if volume 0-500000, multiply by .0045
if 500001-750000, multiply by .0050
if 750001-1250000, multiply by .0055
if 1250001-2000000, multiply by .0060
if 2000000 and above, multiply by .0065

and return the answer to the cell labeled "Commission"
--
Rick

Another idea
=LOOKUP(F14/10000,{0,50,75,125,200},{0.0045,0.005,0.0055,0.006 ,0.0065})*f14

--
Don Guillett
SalesAid Software

"Rick" wrote in message
...
I have a spreadsheet set up that calculates my total production volume and
is
entered in a cell for Totals. I want to take that total number and
calculate
commissions using the following formula:
if volume 0-500000, multiply by .0045
if 500001-750000, multiply by .0050
if 750001-1250000, multiply by .0055
if 1250001-2000000, multiply by .0060
if 2000000 and above, multiply by .0065

and return the answer to the cell labeled "Commission"
--
Rick

Thanks. this was a big help to a novice like me.
--
Rick


"Toppers" wrote:

Create a table as below say in Sheet2 A1 to B5 with Volumes & Commission rates:

A B
0 0.0045
500001 0.0050
750001 0.0055
1250001 0.0060
2000001 0.0065

With production volume in A1 of Sheet1

in "Commission" (assuming it is on Sheet1 also) cell put

=A1*VLOOKUP(A1,Sheet2!$A$1:$B$5,2,1)

Alternative options:

=A1*LOOKUP(A1,Sheet2!$A$1:$A$5,Sheet2!$B$1:$B$5)

or (but less flexible than using a table):

=A1*LOOKUP(A1,{0,500001,750001,1250001,2000001},{0 .0045,0.005,0.0055,0.006,0.0065})


HTH


"Rick" wrote:

I have a spreadsheet set up that calculates my total production volume and is
entered in a cell for Totals. I want to take that total number and calculate
commissions using the following formula:
if volume 0-500000, multiply by .0045
if 500001-750000, multiply by .0050
if 750001-1250000, multiply by .0055
if 1250001-2000000, multiply by .0060
if 2000000 and above, multiply by .0065

and return the answer to the cell labeled "Commission"
--
Rick

Thanks again, This was a big help
--
Rick


"Bill Kuunders" wrote:

=IF(D12=2000000,D12*0.0065,IF(D12=1250001,D12*0. 006,IF(D12=750001,D12*0.0055,IF(D12=500001,D12*0 .005,D12*0.0045))))

or should it be

=IF(D12=2000001,D12.......................
just curious why only the top range starts at 2000000

--
Greetings from New Zealand

"Rick" wrote in message
...
I have a spreadsheet set up that calculates my total production volume and
is
entered in a cell for Totals. I want to take that total number and
calculate
commissions using the following formula:
if volume 0-500000, multiply by .0045
if 500001-750000, multiply by .0050
if 750001-1250000, multiply by .0055
if 1250001-2000000, multiply by .0060
if 2000000 and above, multiply by .0065

and return the answer to the cell labeled "Commission"
--
Rick

Thank you. This was a big help.
--
Rick


"BoniM" wrote:

=IF(A302000000,A30*0.0065,IF(A30=1250001,A30*0.0 06,IF(A30=750001,A30*0.0055,IF(A30=500001,A30*0. 005,IF(A30=0, A30*0.0045,"")))))

computed for total in A30, adjust as necessary.

"Rick" wrote:

I have a spreadsheet set up that calculates my total production volume and is
entered in a cell for Totals. I want to take that total number and calculate
commissions using the following formula:
if volume 0-500000, multiply by .0045
if 500001-750000, multiply by .0050
if 750001-1250000, multiply by .0055
if 1250001-2000000, multiply by .0060
if 2000000 and above, multiply by .0065

and return the answer to the cell labeled "Commission"
--
Rick

Thanks alot. This was a big help.
--
Rick


"Don Guillett" wrote:

Another idea
=LOOKUP(F14/10000,{0,50,75,125,200},{0.0045,0.005,0.0055,0.006 ,0.0065})*f14

--
Don Guillett
SalesAid Software

"Rick" wrote in message
...
I have a spreadsheet set up that calculates my total production volume and
is
entered in a cell for Totals. I want to take that total number and
calculate
commissions using the following formula:
if volume 0-500000, multiply by .0045
if 500001-750000, multiply by .0050
if 750001-1250000, multiply by .0055
if 1250001-2000000, multiply by .0060
if 2000000 and above, multiply by .0065

and return the answer to the cell labeled "Commission"
--
Rick

can you send me a copy of your sheet? Thanks LMM


"Rick" wrote:

I have a spreadsheet set up that calculates my total production volume and is
entered in a cell for Totals. I want to take that total number and calculate
commissions using the following formula:
if volume 0-500000, multiply by .0045
if 500001-750000, multiply by .0050
if 750001-1250000, multiply by .0055
if 1250001-2000000, multiply by .0060
if 2000000 and above, multiply by .0065

and return the answer to the cell labeled "Commission"
--
Rick