# Weight Break calculations - A method question

#### LMCT3

##### New Member
Hey everyone,

Although this account is new I have been around before and ive always gotten good answers and im hoping once again i may get some help.

This question is about how to convert a single line of parameters into slightly different static parameters. I could quite easily create a nested IF formula to dynamically calculate the answers i require but this is intended to create a database to be imported into an antiquated Microsoft Dynamics CRM which requires a series of static integers to calculate upon.

The math is simple.

 Minimum Weight MIN 0 45 100 250 300 500 1000 6.266319 246 39.2575 21.115 15.785 0 14.35 13.8375 0

<tbody>
</tbody>

The figures across the top are related as follows.

Minimum Weight = Minimum possible weight for a job
MIN = Minimum possible charge for a job
0 = Rate for jobs between 0 and =<45
45 = Rate for jobs between 45 and =<100
etc

The interesting bit is that for a job that is say, 24KG, the charge would be 942.18. When the weight increases to 25KG the same bracket would make the charge 981.4375.

The charge for 45kg (The bottom of the next bracket) would be 950.175, and because this is less than the same charge for a weight higher than 24.2kg you would want to charge at the 45kg rate (you assume the shipment is then 45kg to reduce your costs).

With this information, a new set of optimal weight brackets can be configured.

In this case it would look like: between 0 and 24.2 at rate 39.2575 and the next bracket would be 24.3 to 45 at 21.115 (with a minimum weight to charge of 45).

This reworked optimal bracketing is what the Database requires.

It would look like this.

 Minimum Weight Maximum Weight Minimum Weight to Charge Rate 0 24.2 0 39.2575 24.21 44.99 45 21.115

<tbody>
</tbody>

So for each line in the existing table there will be 6 new rows for that one configuration.

What i was trying to achieve was a formula that would enable me to run it down a large sheet of 1000+ lines and output the new optimized data set in another sheet that i could then use for importing.

I appreciate that i haven't given a huge amount of info here but its all ive got. I was trying to avoid using VBA to do this. Otherwise i might aswell write a script in C# to do it. However if someone can give direction with the VBA im happy to use that as a solution.

If it would be useful i can also attach a snippet from the DB in question.

Thanks in advance for anyone who has the time to assist me.

Sincerely, Lewis.

No problem

Have a nice day

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Replies
3
Views
6K
Replies
6
Views
434
Replies
6
Views
588
Replies
2
Views
120
Replies
2
Views
311

1,141,299
Messages
5,705,574
Members
421,399
Latest member
hjweiss00

### 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?

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