MPG Question

[Guest]

Morning,

I recieve a fuel report daily that list all the equipment that's fueled, the
gallons, odometer reading and unit numbers. I copy and paste this report into
an excel spreadsheet. I have a spreadsheet for each month. It's fairly easy
to do on a monthly basis. The problem I'm having is coming up with a way to
update the MPG daily. I can do this but I have to change each formula for
each truck to include the newest date. Does anyone know of a simple way to do
this. I'm thinking some kind of macro but am just not sure. I hope I
explained this enough that someone will understand what I'm talking about.

 

[djessellis]

A macro does seem the simplest way - have you tried recording macro's manually?

If you have a consistent set up of the spreadsheets each month then that may be workable. If you record a Macro, (fairly easy) you are showing precise actions that you want to be able to repeat each month. These actions will be carried out on precise cells, exactly as you instruct initally. It may be worth thinking about if you can ensure consistency each month and then work out exactly what actions you repeat - then you can record them as a macro and then you'd have an automated process....

 

[Guest]

Can we see samples of data and formula(s) you're using now?

 

[Guest]

If it's mainly a question of updating every formula to include the correct
date, can you not just enter the date in a given cell and point every formula
at that cell? Then you only have to change one thing, rather than many.

 

[Guest]

I guess my problem is that not all the trucks fueled on the 1st day of the
month so I have different start days and not all trucks will fuel on the last
day of the month. I think I need a formula or macro that looks at the unit
number then the first and last day said unit fueled then substracts the first
odometer reading from the last. I could be going about this the wrong way,
that's why I asking for help. Thanks for your reply if you can add anything
else I'd appreciate it.

 

[Guest]

Is there a way to attach a file to these post? I tried copy and pasting a
rows of the spreadsheet, but of course the formatting is off.

Thank you

 

[djessellis]

You can attach picture files - take a screen shot of what you have and save it in paint as a jpg, gif, etc...

Is there a way to attach a file to these post? I tried copy and pasting a
rows of the spreadsheet, but of course the formatting is off.

Thank you

 

[Sandy Mann]

I haven't been following this thread too closely but with the dates in
Column A, truck registration in Column B and the odometer reading in Column
C all starting from Row 4:

=IF(OR(COUNTIF($B$4:B5,B5)<2,MONTH(A5)<>2),"",MAX(($B$4:B5=B5)*(MONTH($A$4:A5)=2)*$C$4:C5)-MAX(($B$4:B4=B5)*(MONTH($A$4:A4)=2)*$C$4:C4))

Array entered with Ctrl + Shift + Enter not just Enter and copied down will
return the mileage since the time that the truck registration in that row
was refueled.

=IF(OR(COUNTIF($B$4:B5,B5)<2,MONTH(A5)<>2),"",MAX(($B$4:B5=B5)*(MONTH($A$4:A5)=2)*$A$4:A5)-MAX(($B$4:B4=B5)*(MONTH($A$4:A4)=2)*$A$4:A4))

Array entered will return the number of days in the above period and:

=IF(OR(COUNTIF($B$4:B7,B7)<2,MONTH(A7)<>2),"",(MAX(($B$4:B7=B7)*(MONTH($A$4:A7)=2)*$C$4:C7)-MAX(($B$4:B6=B7)*(MONTH($A$4:A6)=2)*$C$4:C6))/(MAX(($B$4:B7=B7)*(MONTH($A$4:A7)=2)*$A$4:A7)-MAX(($B$4:B6=B7)*(MONTH($A$4:A6)=2)*$A$4:A6)))

Again Array entered will return the average daily mileage in that period.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

As I said, I have not been following this tread too closely and I had a
vague recollection that the OP talked about doing it on a monthly basis but
re-reading the original post I think that he only want a formula for daily
MPG. That being the case it simplifies it down to:

=IF(COUNTIF($B$4:B5,B5)<2,"",MAX(($B$4:B5=B5)*$C$4:C5)-MAX(($B$4:B4=B5)*$C$4:C4))

Array Entered for mileage since last refueling:

=IF(COUNTIF($B$4:B5,B5)<2,"",MAX(($B$4:B5=B5)*$A$4:A5)-MAX(($B$4:B4=B5)*$A$4:A4))

Array Entered or the number of days since last refueling and:

=IF(COUNTIF($B$4:B5,B5)<2,"",(MAX(($B$4:B5=B5)*$C$4:C5)-MAX(($B$4:B4=B5)*$C$4:C4))/(MAX(($B$4:B5=B5)*$A$4:A5)-MAX(($B$4:B4=B5)*$A$4:A4)))

again Array Entered for the average daily mileage since last refueling
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Nope, no way to upload here in the Microsoft Discussion forum. Some other
sites that link in do have that advanced, extremely helpful technology
available. Here we have to struggle with typing in examples manually and
hoping the line wrap doesn't make the effort totally wasted.

It looks like Sandy Mann has something that could work for you on down the
list of responses a bit.