Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an Excel formula please?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an Excel formula please?
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. -- Regards, Fred "Victor Delta" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an Excel formula please?
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Victor Delta" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an Excel formula please?
"Fred Smith" wrote in message
... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an Excel formula please?
A1-365 is much simpler. Sometimes we overthink these things.
Glad it worked. -- Regards, Fred "Victor Delta" wrote in message ... "Fred Smith" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with an Excel formula please?
"Ragdyer" wrote in message
... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Excel Formula Issue | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |