MPG Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ClarkDis - 30 Aug 2007 15:28 GMT

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.

Signature

Thank you

Reply to this Message

JLatham - 30 Aug 2007 17:46 GMT

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

> Morning,
>
[quoted text clipped - 6 lines]
> 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.

Reply to this Message

ClarkDis - 31 Aug 2007 14:36 GMT

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

> Can we see samples of data and formula(s) you're using now?
>
[quoted text clipped - 8 lines]
> > 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.

Reply to this Message

JLatham - 31 Aug 2007 21:18 GMT

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.

> 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.
[quoted text clipped - 13 lines]
> > > 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.

Reply to this Message

Dom_Ciccone - 30 Aug 2007 22:18 GMT

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.

Signature

Kevin Ciccone

> Morning,
>
[quoted text clipped - 6 lines]
> 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.

Reply to this Message

ClarkDis - 31 Aug 2007 14:32 GMT

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.

Signature

Thank you

> 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
[quoted text clipped - 10 lines]
> > 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.

Reply to this Message

Sandy Mann - 31 Aug 2007 19:27 GMT

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.

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>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
[quoted text clipped - 30 lines]
>> > explained this enough that someone will understand what I'm talking
>> > about.

Reply to this Message

Sandy Mann - 31 Aug 2007 19:59 GMT

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

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

>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
[quoted text clipped - 50 lines]
>>> > explained this enough that someone will understand what I'm talking
>>> > about.

Reply to this Message

Rate this thread: