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

E

enslaved

New Member
Joined
Mar 6, 2012
Messages
10
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?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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
 
Upvote 0
Maybe something like this

A B C D E F
[TABLE="width: 306"]
<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>[TR]
[TD="class: xl65, width: 79, bgcolor: transparent"]Bands[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Cost[/TD]
[TD="class: xl65, width: 73, bgcolor: transparent"]Difference[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Parcels[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Total[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8,95[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8,95[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]78[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]657,6[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8,45[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]-0,5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]50[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7,95[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]-0,5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]100[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7,45[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]-0,5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]250[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6,95[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]-0,5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]500[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6,45[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]-0,5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1000[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5,95[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]-0,5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2000[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5,45[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]-0,5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Creating a 'reference sheet' of postage prices from different suppliers with different weight bands and sizing options.
Replies
0
Views
469
Rhothgar
R
R
  • Question Question
Identifying the difference between two data sets
Replies
1
Views
165
igold
igold
1
  • Question Question
Calculate on every third Friday of the month and show results between positive & negative weeks
2
Replies
14
Views
949
15minoffame
1
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
M
  • Question Question
How to see if my loan payment is within budget with true or false statement
Replies
6
Views
766
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top