Help with an Excel formula please?

[Victor Delta]

I have a spreadsheet with (random - i.e. no pattern) dates in Col A and the
relevant total mileage of a car in Col B.

Calculating the average overall annual mileage of the car is easy (total
mileage / no of days since purchase * 365).

However, can anyone please help me with a formula that I can enter in Col C
to calculate the car's mileage just over the previous 12 months. Obviously
if the dates were regular (e.g. weekly or monthly) it would be easy, but
random dates (usually 1 or 2 every month but not always) makes it much more
difficult.

Many thanks,

V

 

[Fred Smith]

I don't know exactly how your data is formatted, but hopefully, this will point
you in the right direction.

First, sort your dates in ascending order.

Next, calculate the date one year ago: =date(year(a1)-1,month(a1),day(a1))

Now, use Vlookup to get the closest entry you have to that date:
=vlookup(date(year(a1)-1),month(a1),day(a1)),A:B,1,true) --> this will get the
date on file
=vlookup(date(year(a1)-1),month(a1),day(a1)),A:B,2,true) --> this will get the
mileage on that date

Now subtract the mileage found from the current mileage.

Finally, pro-rate it to 365 days as you did with total mileage.

 

[Ragdyer]

How about you set the time parameters by entering the start date in C1, the
end date in C2, and this *array* formula in C3:

=AVERAGE(IF((A1:A100>=C1)*(A1:A100<=C2),B1:B100))

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

 

[Victor Delta]

I don't know exactly how your data is formatted, but hopefully, this will
point you in the right direction.

First, sort your dates in ascending order.

Next, calculate the date one year ago: =date(year(a1)-1,month(a1),day(a1))

Now, use Vlookup to get the closest entry you have to that date:
=vlookup(date(year(a1)-1),month(a1),day(a1)),A:B,1,true) --> this will get
the date on file
=vlookup(date(year(a1)-1),month(a1),day(a1)),A:B,2,true) --> this will get
the mileage on that date

Now subtract the mileage found from the current mileage.

Finally, pro-rate it to 365 days as you did with total mileage.

Thanks, Fred, you're a star.

V

PS I actually found it slightly simpler to use (A1-365) as the expression
for the date 1 year ago. Works a treat.

 

[Fred Smith]

A1-365 is much simpler. Sometimes we overthink these things.

Glad it worked.

 

[Victor Delta]

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead
of
the regular <Enter>, which will *automatically* enclose the formula in
curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

Again, many thanks.

However, to be honest, despite many years of using quite complex Excel
spreadsheets, I have yet to fully understand array formulae. So for the time
being, I'm going to stick to the simpler vlookup option which seems to work
perfectly. One day, I will set aside some time...

Thanks,

V