Link to home
Start Free TrialLog in
Avatar of Babypink2807
Babypink2807

asked on

Formula to calculate new delivery date due

Maths was never my forte at all, and I have just driven myself to the brink to work out how to formulate this calculation, and hope someone can help

The quandry is what will the next delivery date be, based on the following information

18kva Generator, on a qtr load, uses 24 litres a day (1ltr per hour), the fuel tank is 2000ltrs, and it runs 40hrs over a 5 day week (8hrs a day) the last delivery date was 1st February

100kva Generator, on a qtr load, uses 120 litres a day (5ltr per hour), the fuel tank is 2000 litres, and it runs 24/7, the last delivery date being 1st February

What will based on the above useage, the next delivery date be?



Thanks
 Fuel-consumption-for-yedl.xlsx
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

You can get the number of days by dividing the capacity (2000) by the number of litres used in a day, which would be 24*8 = 192 so the result is approximately 10 workdays....so if you use WORKDAY function that will be this calculation

=WORKDAY(G5,E5*5/F5/C5)

[assuming that F5 contains just the number 40, without text]

For 24/7 operation then that would be

=G5+2000/120

both calculations assume that the tank will be full after a delivery.....

regards, barry
Find the updated sheet. I've added a column (with yellow back ground) Usage/Week (in Hrs). Provide the weekly hourly usage (max 168 hrs) in that column and check the Next delivery date column (with green background). Fuel-consumption-for-yedl.xlsx
Avatar of Babypink2807
Babypink2807

ASKER

Thank you for the file attachment, now I maybe not getting it right, but as the fuel rate goes up say to say 120 ltrs a day as opposed to the 24 litrs a day, the refuelling should be more regular which the spreadsheet does not seem to do?

As an example if I put the figure 168 in the useage column which equates to 24/7 running and set the last delivery date to 1 Feb 2011 the higher the fuel consumption then surely the next delivery date will get less as I go down the table.  On the table atatched its all the same?

Sorry to be a pain
Did you try the formula I suggested? For 24/7 that would be

=G5+E5/C5

If totals look the same try pressing F9 key to re-calculate.....

Also I'm a little unclear on when you want the MG calculation and when it's 24/7, what determines that, do you want that automated?

regards, barry
Terribly sorry

My tank size is 2000 litres, I have a generator which is 18kva and is running 40hrs over a five day week (40/5) and each day (if it was running 24/7) it would use 24 litres (1 ltr per hour)

So as this generator is running 40hrs over five days that would mean each day it runs 8hrs so using 8litres a day (8hrx5days =40hrs).  With the fuel tanks capacity of 2000 litres this means theoretically it needs to be refuelled every xxxx days

Sorry now you know why I have a headache about it :-)
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Added one more column (with yellow background). Put litres of usage in an hour in that column.

Check and update me.
Fuel-consumption-for-yedl.xlsx
Hi guys

Will try this today thank you and let you know the outcome.  I really do appreciate this help - thank you