Calculating Parcel costs within a band - if between, then sum

[enslaved]

Hello,

I have a weird one for you guys today..very interesting if you ask me!

As an example, i am trying to work out the cost of 78 parcels, where from 0 - 25 parcels we charge $8.95 per parcel (Up to 25 parcels (8.95*25) = $223.75), 26 to 50 parcels (8.45*25) = $211.25 and 51 - 100 (7.95*28) = $222.6.
Parcel Volume Pricing p/parcel
Up to 25 parcels per month $8.95
26-50 $8.45
51-100 $7.95
101-250 $7.45
251-500 $6.95
501-1,000 $6.45
1,001-2,000 $5.95
2,001 and above parcels per month $5.45
I want the tool to allow me to enter in a value of parcels which will then give me a total price which sums per band.

As another example, if we have 55 parcels, this would be the calculation in the formula;

As the number is greater than 25, we grab (25 * 8.95) + (25 * 8.45) [as the number is greater than 21-50] + ( 5 * 7.95) [as '55' is 5 above 50).

For further clarity, if the number of parcels is 15, its simply 15* 8.95. If the number of parcels is 2560, it will calculate the cost of all bands accordingly.

Does this make sense?

 

[enslaved]

no body?

 

[enslaved]

The correct amount for 78 parcels is $657.6 :
Up to 25 parcels (8.95*25) = $223.75
26-50 (8.45*25) = $211.25
51 - 100 (7.95*28) = $222.6

 

[Marcelo Branco]

Maybe something like this

A B C D E F

Bands	Cost	Difference		Parcels	Total
0	8,95	8,95		78	657,6
25	8,45	-0,5
50	7,95	-0,5
100	7,45	-0,5
250	6,95	-0,5
500	6,45	-0,5
1000	5,95	-0,5
2000	5,45	-0,5

<colgroup><col style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;" width="79"> <col style="width: 48pt;" width="64"> <col style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;" width="73"> <col style="width: 48pt;" span="3" width="64"> <tbody>
</tbody>

Put 0 in A2

In A3:A9 the higher limit of each band

Put the costs in B2:B9

Formula in C2
=B2-N(B1)
copy down till C9

Enter the number of parcels in E2

Formula in F2
=SUMPRODUCT(--($E2>$A$2:$A$9),$E2-$A$2:$A$9,$C$2:$C$9)

Hope this is what you need

M.

 

[enslaved]

you are a dead set genius. amazing work Marcelo.

CHEERS!!

 

[Marcelo Branco]

you are a dead set genius. amazing work Marcelo.

CHEERS!!

M.

 

[Marcelo Branco]

BTW, to be fair.

Thanks to McGimpsey & Associates

McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

M.