Multiplier question

[limelicious]

Hi,

I am trying to create a formula such that for every increase in load by 2000kg, the multiplier would increase by 1.

For example, I have a product that weighs 900kg each but the truck that the product needs to be loaded on has a maximum capacity of 2000kg. So if I need to transport 3 products or 2700kg I would need 2 trucks, etc.

However, I am unsure of how to input the formula if I was to ship 10 products or 9000 kg which would require 5 trucks.

Currently my incomplete formula looks like this:

=IF(U13*L13<=2000,VLOOKUP(D13,A!C:BS,21,FALSE),IF(U13*L13>2000,VLOOKUP(D13,A!C:BS,21,FALSE)*2))


Is there a way of inputing it on excel so that I don't need to type out every increase in 2000kg. i.e. for every increase in 2000kg, increase multiplier by 1.


Thank you!

 

[Oeldere]

You could look for an solution with Excel solver.

Excel 2007 => data => solver

 

[FormR]

Welcome to the forum, try:

=VLOOKUP(D13,A!C:BS,21,FALSE)*ROUNDUP(U13*L13/2000,0)