How to sum different rates based on number of bins?

[envirowebs]

Transportation Rate Schedule for Bins.xlsx

Hello,

This is my first post, so please excuse my poor use of Excel Jargon.

I'm trying to find a formula to calculate an combine different transportation rates based on the total number of bins to be shipped.

Our trucks carry two bins, so we give a discount for picking up the second bin.

Looking at the attached sheet, can someone help me with a formula that can sum in cell H5 based on the following criteria:
One Bin - C5*D5=H5
Two Bins - C5*(D5+E5)=H5
Three Bins - C5*(D5*2+E5)=H5
Four Bins - C5*(D5*2+E5*2)=H5
Five Bins - C5*(D5*3+E5*2)=H5
Etc.
Etc.
Etc.

Thank you!

[FlameRetired]

Hello envirowebs. Welcome to the forum.

If we can know how many bins are being used this should be doable. Where is that information in the upload?

[envirowebs]

Hi FlameRetired,

Thanks for the warm welcome!

The number of bins (Cell C5) changes...its the variable.

So if I enter "1" in cell C5 it should equal $497.91
"2" should equal $746.87
"3" should equal $1244.78
so on and so on....

Let me know if that doesn't make sense and I'll try to explain it another way.

~Pax

[FlameRetired]

What version of Excel are you using? Newer versions have more possibilities. (Please update your profile to show us that.)

[envirowebs]

Excel for MS 365 Pro Plus

[FlameRetired]

Thank you for that.

In column C find some random bin counts. In column H this formula in H5 and filled down.

Please Login or Register  to view this content.

[envirowebs]

Thank you so much FlameRetired!!!!! This is exactly what I was looking for!

[FlameRetired]

You are welcome. Glad it helped. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.