Need help getting optimum split of a fuel delivery between tanks

[Fastpat27]

Hi All
New to this forum so not sure if it's the correct place to get help creating a spreadsheet. I tried an excel freelancer but they couldn't seem to make the calculations work proportionately. It's simple to do on paper but creating a formula seems to be more difficult. I'm not an expert on excel by any means but have a little knowledge.

Basically I want to divide a fuel delivery between 3 tanks in a ratio of 50:35:15 but I get stuck when one tank has more in it already than what is needed, it therefore tries to make me take fuel out by generating a minus total but that is not possible.

On paper it is simple as I take the total current stock in the 3 tanks, add on the delivery total, and divide the overall total into the ratios. If tank 1 (50%) is over allocated (each tank holds 21000lts), the amount over allocated goes into tank 2 (35%). If Tank 2 is over allocated with the original 35% + tank 1 over allocation, then the remainder will go into tank 3 (15%)

This gives me the totals after delivery for each tank, I then subtract the current stock from this total and it give me the delivery required for each tank. Does this make sense?

So I start with the current stock, add the delivery and divide into the ratios, but tank 3 has more current stock in it than 15% so it starts me off with a minus figure which I can't figure out how to get around this.

This is probably hard to get your head around but I would appreciate any help if any one has any ideas.

Many Thanks
FP

 

[TonyUK72]

Are you happy for ALL or ANY of the tanks to be filled to capacity?

 

[Fastpat27]

Hi Tony, thanks for your interest.

All the tanks will never be completely filled but possible that 2 will be filled. Each tank holds 21000 litres but they all empty at different rates, hence the ratio, tank 1 will use 50% of the weekly fuel, tank 2 35%, and tank 1 15% (these are approx. usage but close enough for us to guage without running out.

 

[TonyUK72]

Just for testing purposes can you tell me a typical delivery volume?

 

[Fastpat27]

Typical delivery is usually 30000 litres.

Last delivery we had 6949Lts in tank 1 before delivery, 6123Lts Tank 2, and 14333Lts in tank 3.

They put too much in tank 3 the previous delivery, so this is why I like to try and create something simple for them.

 

[TonyUK72]

The formulas below have speech marks at the start and end that need to be removed.

Assume that A1 = Tank1 Current Quantity, B1 = Tank2 Quantity, C1 = Tank3 Quantity and D1 Delivery Quantity

The formula for A2 should be "=IF(($D$1/100)*50+A1>21001,21000,($D$1/100)*50+A1)"
The formula for B2 should be "=IF(($D$1/100)*35+B1>21001,21000,($D$1/100)*35+B1)"
The formula for C2 should be "=IF(($D$1/100)*15+C1>21001,21000,($D$1/100)*15+C1)"

I thought the above solved the problem, but it actually needs a little work.

 

[Fastpat27]

Thank you for trying Tony, I thought it was simple until I started working on it. I'll try your formulas and maybe see it from a different angle, I'll let you know if I get a result.

thanks for your help and if you can figure anything else out it will be much appreciated.

I'll need to go here so will probably be tomorrow night before I get back at it.

Thank you
FP

 

[TonyUK72]

The only problem with my formula is that they DONT carry over the excess from the previous tanks.

Based on your last delivery Tank1 should receive 21949, but its capacity is 21000, so the 949 needs to be carried over to Tank2 etc.....


EDITED
Would that 949 be carried to tank 2 (upto 21000), then carried to tank 3 or would it go straight to tank 3?

 

[Fastpat27]

Yes the 949 would be carried over to tank 2 up to 21000, then any access above this carried to tank 3. Tank 3 uses the least fuel in the week so sometimes it can do without any fuel.