incremental charges

[jp17]

would like to calculate charges @ stages. I can't the formulas to work

Range Rate Billable 65000
0 10,000 10,000 0.05 $500.00
10,001 20,000 9,999 0.06 $599.94
20,001 30,000 9,999 0.07 $699.93
30,001 40,000 9,999 0.08 $799.92
40,001 50,000 9,999 0.09 $899.91
50,001 15,004 0.10 $1,500.40
65,000 $5,000.10

Range Rate Billable 15000
0 10,000 10,000 0.05 $500.00
10,001 20,000 5,000 0.06 $300.00
20,001 30,000 0.07 $-
30,001 40,000 0.08 $-
40,001 50,000 0.09 $-
50,001 0.10 $-
15,000 $800.00

Range Rate Billable 3000
0 10,000 3,000 0.05 $150.00
10,001 20,000 0.06 $-
20,001 30,000 0.07 $-
30,001 40,000 0.08 $-
40,001 50,000 0.09 $-
50,001 0.10 $-
3,000 $150.00

 

[mintjulep]

I would like to win tomorrow's PowerBall lottery. I don't know what numbers to pick.

Please read your post and ask yourself "If there is a kind member of Tek-Tips that would like to help me, is there anyway he or she could possibly understand what it is that I need to do, what I've tried, which of the random numbers that I listed need to be used in the calculation or what the calculation needs to be?

 

[SkipVought]

Help yerself out, jp. We need to know what's in your head.

Please be very detailed and specific in your description.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jp17]

Sorry, let me try again

Volume 65,000.00
.....Range........................Monthly Totals..... ...........Rate...............Billable
........0 ………….. 10,000 ………….. 10,000 …………. 0.05 ………….. $500.00 …………..
10,001 ………….. 20,000 ………….. 9,999 ………….. 0.06 ………….. $599.94 …………..
20,001 ………….. 30,000 ………….. 9,999 ………….. 0.07 ………….. $699.93 …………..
30,001 ………….. 40,000 ………….. 9,999 ………….. 0.08 ………….. $799.92 …………..
40,001 ………….. 50,000 ………….. 9,999 ………….. 0.09 ………….. $899.91 …………..
50,001 ………….. .........………….. 15,004 ………….. 0.10 ………….. $1,500.40 …………..
...............………….. Total ………….. 65,000 ………….. ....………….. $5,000.10 Total Billable

 

[SkipVought]

Still don't know what your specific question is???

What is your SOURCE data?

Exactly what do you need to do?

DETAILS PLEASE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

=(SUM(A2:B6)/AVG(D7))*VLOOKUP(B2:E17,A3,1)

 

[jp17]

it's based on volume, so in this scenario this month the volume is 65k and for the 1st 10k I need to calculate the right amount within the ranges @ .05 and the next range to calaculate @ .06 but I can't get the formula to work.

 

[mintjulep]

10000 * .05 = 500
9999 * .06 = 599.94
9999 * .07 = 699.93

 

[jp17]

how do I get the right amount to populate in the monthly total when the amounts change? if total is 20k
then range 1 would be 10,000
and range 2 would 9,999
and range 3 would be 1

 

[mintjulep]

1 * .07 = 0.07

 

[SkipVought]

Please post the formula that you are using, explaining 1) what each reference refers to in detail, 2) the expected result and 3) the actual result.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

Let me guess: I think what you want to do is calculate a cost of, say, 34000 units of something where the first 1000 units cost price X, the next 2000 units cost price Y, the 5000 units after that cost price Z etc., so the total price is actually a sum of a whole series of prices multiplied by numbers of units.

If this is what you're doing, there are two ways to do it.

Simple
======

If you do it fairly rarely, and you want something simple, you can add two columns next to your price-list. The first is the cost associated with the current range. The second is the items remaining.

In the first column, put a formula that looks at the items remaining from the line above, and tests if this is bigger or smaller than the current range. If it's smaller, multiply it by the current range's price. If it's bigger, multiply the current range-size by the current range's price. If the items remaining is actually negative, put a cost of zero.

In the second column, put a formula that subtracts the current range-size from the number of items remaining in the line above.

At the bottom, sum the entire column of costs.

This approach is easy to understand, but means you have a whole grid of numbers including your price list just to calculate one answer.

Complicated
===========

I'm fairly sure someone here can write a horrendously long and complicated sumproduct that tests how many items are in each range, multiplies the appropriate amount by the number, and adds it all in one go, but it's going to be a horrible one liable to typos and misunderstandings, so I'd only do this if I needed to calculate lots of prices on one worksheet. It's the sort of thing some people consider an interesting puzzle, but I haven't sufficient brain-cells to cope today.

 

[lionelhill]

... oh, forgot the obvious. If you do want a solution that calculates the answer in a single cell using a standard table of prices put at any convenient place, and if I'm understanding correctly what you want to do, it's probably easier to write yourself a user-defined function than to hope for a horrendously complicated formula.

 

[jknichols]

One other observation:

Your example is giving away 1 unit every 10,000 units - 10,001 to 20,000 is 10,000 not 9,999.

20K sales would be 10K plus 10K, not 10K +9,999 plus 1

also your logic seems backwards - for bulk purchasing, the second 10K should be cost less than the first, not more.

 

[byundt]

I made a three column table in a named range (cells F1:H6) called Brackets. The table looks like this:
F…….. G…….. H…….
00000 0.05 0.00
10001 0.06 500.00
20001 0.07 1099.94
30001 0.08 1799.87
40001 0.09 2599.79
50001 0.1 3499.70

The third column is the value of the preceding bracket prices. Cell H2 (second element in third column) uses this formula, which may be copied down:
=(F2-1-F1)*G1+H1

If the volume is in A1, the total billable is calculated using three VLOOKUP functions:
=VLOOKUP(A1,Brackets,3)+(A1-VLOOKUP(A1,Brackets,1)+1)*VLOOKUP(A1,Brackets,2)

Brad