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.
--
Thank you

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

"ClarkDis" wrote:

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.
--
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
at that cell? Then you only have to change one thing, rather than many.
--
Kevin Ciccone


"ClarkDis" wrote:

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.
--
Thank you

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.
--
Thank you


"Dom_Ciccone" wrote:

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.
--
Kevin Ciccone


"ClarkDis" wrote:

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.
--
Thank you

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


"JLatham" wrote:

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

"ClarkDis" wrote:

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.
--
Thank you

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


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


"ClarkDis" wrote in message
...
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.
--
Thank you


"Dom_Ciccone" wrote:

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.
--
Kevin Ciccone


"ClarkDis" wrote:

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.
--
Thank you

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


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


"Sandy Mann" wrote in message
...
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


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


"ClarkDis" wrote in message
...
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.
--
Thank you


"Dom_Ciccone" wrote:

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.
--
Kevin Ciccone


"ClarkDis" wrote:

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.
--
Thank you

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.

"ClarkDis" wrote:


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


"JLatham" wrote:

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

"ClarkDis" wrote:

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.
--
Thank you