I have a complex formula that I can't seem to find the right combinations of
If/then and lookup statements. I also am having difficulting searching for
the right solution as to what I am trying to accomplish.

I need to be able to give discounts to volume users of a copy machine and
give them 4 levels of volumn discount costs.
Example of Table:
0 to 100 = 0.08
101 to 200 = 0.06
201 to 500 = 0.05
500= 0.04

User A had 750 copies in the month, so 1st 100 would cost .08, then the next
100 would cost .06, then the next 300 would cost .05, and the last 250 would
cost .04.

User B had 450 copies in the month, so 1st 100 would cost .08, then the next
100 would cost .06, then the next 250 would cost .05.

User C had 1200 copies in the month, so 1st 100 would cost .08, then the
next 100 would cost .06, then the next 300 would cost .05, and the remaining
700 would cost .04.

I have this Tiered Table in a separate Worksheet and I want to be able to
adjust these four costs or four tier totals without changing the formula in
each cell for each user. User Totals are listed in each row and the formula
will be in the last column for their total cost for all copies based on
volumn discounts.

Can you direct me to the best location for searching for a solution? Thanks.

Hello Rick, look here

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



"Rick" wrote:

I have a complex formula that I can't seem to find the right combinations of
If/then and lookup statements. I also am having difficulting searching for
the right solution as to what I am trying to accomplish.

I need to be able to give discounts to volume users of a copy machine and
give them 4 levels of volumn discount costs.
Example of Table:
0 to 100 = 0.08
101 to 200 = 0.06
201 to 500 = 0.05
500= 0.04

User A had 750 copies in the month, so 1st 100 would cost .08, then the next
100 would cost .06, then the next 300 would cost .05, and the last 250 would
cost .04.

User B had 450 copies in the month, so 1st 100 would cost .08, then the next
100 would cost .06, then the next 250 would cost .05.

User C had 1200 copies in the month, so 1st 100 would cost .08, then the
next 100 would cost .06, then the next 300 would cost .05, and the remaining
700 would cost .04.

I have this Tiered Table in a separate Worksheet and I want to be able to
adjust these four costs or four tier totals without changing the formula in
each cell for each user. User Totals are listed in each row and the formula
will be in the last column for their total cost for all copies based on
volumn discounts.

Can you direct me to the best location for searching for a solution? Thanks.

Yes! That's perfect!
Thanks!

"daddylonglegs" wrote:

Hello Rick, look here

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



"Rick" wrote:

I have a complex formula that I can't seem to find the right combinations of
If/then and lookup statements. I also am having difficulting searching for
the right solution as to what I am trying to accomplish.

I need to be able to give discounts to volume users of a copy machine and
give them 4 levels of volumn discount costs.
Example of Table:
0 to 100 = 0.08
101 to 200 = 0.06
201 to 500 = 0.05
500= 0.04

User A had 750 copies in the month, so 1st 100 would cost .08, then the next
100 would cost .06, then the next 300 would cost .05, and the last 250 would
cost .04.

User B had 450 copies in the month, so 1st 100 would cost .08, then the next
100 would cost .06, then the next 250 would cost .05.

User C had 1200 copies in the month, so 1st 100 would cost .08, then the
next 100 would cost .06, then the next 300 would cost .05, and the remaining
700 would cost .04.

I have this Tiered Table in a separate Worksheet and I want to be able to
adjust these four costs or four tier totals without changing the formula in
each cell for each user. User Totals are listed in each row and the formula
will be in the last column for their total cost for all copies based on
volumn discounts.

Can you direct me to the best location for searching for a solution? Thanks.

This formula:
=IF(N9=0,"",IF(AND(N9Tier!$A$2,N9<Tier!$B$2),N9*T ier!$C$2,IF(AND(N9Tier!$B$2,N9<Tier!$B$3),SUM((Ti er!$B$2*Tier!$C$2)+((N9-Tier!$B$2)*Tier!$C$3)),IF(AND(N9Tier!$B$3,N9<Tier !$B$4),SUM((Tier!$B$2*Tier!$C$2)+((SUM(Tier!$B$3-Tier!$B$2)*Tier!$C$3)+((N9-Tier!$B$3)*Tier!$C$4))),IF(N9Tier!$B$4,SUM((Tier! $B$2*Tier!$C$2)+(SUM(Tier!$B$3-Tier!$B$2)*Tier!$C$3)+(SUM(Tier!$B$4-Tier!$B$3)*Tier!$C$4)+(SUM(N9-Tier!$B$4)*Tier!$C$5)))))))

Is reduced to this formula:
=SUMPRODUCT(--(N9Tier),(N9-Tier),dRate)

WOW! Many thanks.

"Rick" wrote:

Yes! That's perfect!
Thanks!

"daddylonglegs" wrote:

Hello Rick, look here

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



"Rick" wrote:

I have a complex formula that I can't seem to find the right combinations of
If/then and lookup statements. I also am having difficulting searching for
the right solution as to what I am trying to accomplish.

I need to be able to give discounts to volume users of a copy machine and
give them 4 levels of volumn discount costs.
Example of Table:
0 to 100 = 0.08
101 to 200 = 0.06
201 to 500 = 0.05
500= 0.04

User A had 750 copies in the month, so 1st 100 would cost .08, then the next
100 would cost .06, then the next 300 would cost .05, and the last 250 would
cost .04.

User B had 450 copies in the month, so 1st 100 would cost .08, then the next
100 would cost .06, then the next 250 would cost .05.

User C had 1200 copies in the month, so 1st 100 would cost .08, then the
next 100 would cost .06, then the next 300 would cost .05, and the remaining
700 would cost .04.

I have this Tiered Table in a separate Worksheet and I want to be able to
adjust these four costs or four tier totals without changing the formula in
each cell for each user. User Totals are listed in each row and the formula
will be in the last column for their total cost for all copies based on
volumn discounts.

Can you direct me to the best location for searching for a solution? Thanks.