Need to calculate monthly avg and yearly miles from odometer
Solved/Closed
jamishore
Posts
9
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
September 13, 2017
-
Sep 6, 2017 at 05:14 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Sep 13, 2017 at 09:41 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Sep 13, 2017 at 09:41 PM
Related:
- Annual mileage calculator with odometer dates
- Mtu calculator - Guide
- Vat calculator excel - Guide
- Best statistics calculator app - Download - Calculators
- Apple iphone 13 release dates - Guide
- Moffsoft calculator 2 - Download - Calculators
5 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Sep 10, 2017 at 03:46 AM
Sep 10, 2017 at 03:46 AM
Hello Jamishore,
You would like to total each row in Column N and average the month to date values in Column O?
If so, in cell N2, type in:
=SUM( (the parenthesis just may appear automatically).
From B2, drag across to M2. You will see a formula begin to develop in the formula bar. Place a closing parenthesis at the end of the formula and then click away. Being a table that you are using, the formula should flash fill downwards and the totals will appear in Column N for each row.
In cell O2, type in:
=average(
Again, from B2 drag across to M2. You will again see a formula begin to develop. Close the parenthesis and click away. The formula should again flash fill for you. The values will change as you fill in the currently empty cells.
I hope that this helps.
Cheerio,
vcoolio.
You would like to total each row in Column N and average the month to date values in Column O?
If so, in cell N2, type in:
=SUM( (the parenthesis just may appear automatically).
From B2, drag across to M2. You will see a formula begin to develop in the formula bar. Place a closing parenthesis at the end of the formula and then click away. Being a table that you are using, the formula should flash fill downwards and the totals will appear in Column N for each row.
In cell O2, type in:
=average(
Again, from B2 drag across to M2. You will again see a formula begin to develop. Close the parenthesis and click away. The formula should again flash fill for you. The values will change as you fill in the currently empty cells.
I hope that this helps.
Cheerio,
vcoolio.
jamishore
Posts
9
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
September 13, 2017
Sep 12, 2017 at 04:26 PM
Sep 12, 2017 at 04:26 PM
Thank you very much for getting back to me.
Since there is an odometer reading listed in those columns, it wouldn't come up with the actual yearly mileage if I did a Sum formula. it would add them all up correct?
Instead I ended up having to add additional columns to calculate the mileage for each month and manually come up with the Yearly Miles by subtracting the starting odometer reading in January from this months odometer reading for column CB (which I will have to do each month).
And because there will always be a negative amount in the current month I am unable to average those columns, so instead I divided column CB by 12 to get the monthly average for column CC.
Any advise would be greatly appreciated.
Also, here is my updated spreadsheet:
https://www.dropbox.com/s/19k44tbw16gk6cx/Average%20Miles.xlsx?dl=0
Since there is an odometer reading listed in those columns, it wouldn't come up with the actual yearly mileage if I did a Sum formula. it would add them all up correct?
Instead I ended up having to add additional columns to calculate the mileage for each month and manually come up with the Yearly Miles by subtracting the starting odometer reading in January from this months odometer reading for column CB (which I will have to do each month).
And because there will always be a negative amount in the current month I am unable to average those columns, so instead I divided column CB by 12 to get the monthly average for column CC.
Any advise would be greatly appreciated.
Also, here is my updated spreadsheet:
https://www.dropbox.com/s/19k44tbw16gk6cx/Average%20Miles.xlsx?dl=0
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Sep 13, 2017 at 04:24 AM
Sep 13, 2017 at 04:24 AM
Hello Jami,
It may be a better option (but its up to you) to use two separate tables side by side. One table is for the monthly odometer readings and the other can be used to do the calculations without the negative values. We can then use VBA to insert formulae for the calculations.Have a look at the following sample I knocked up for you:-
http://ge.tt/2r7NeWm2
To start with, type a value into Column H (1st August), larger than the previous value, then click away (or press enter or down arrow) and you'll see the second table update, including the yearly total and average to date. Do the same for the remaining months then start a second row if you wish. Totals and averages should update.
As you can see, I haven't actually set the sample up as tables but you can do this yourself just to see that it all still works as it should. Remember to keep at least one column between the tables as per the sample.
Following is the code for this:-
It is a Worksheet_Change event and needs to be placed into the sheet1 (or your master sheet) module.
Let us know your thoughts.
Cheerio,
vcoolio.
It may be a better option (but its up to you) to use two separate tables side by side. One table is for the monthly odometer readings and the other can be used to do the calculations without the negative values. We can then use VBA to insert formulae for the calculations.Have a look at the following sample I knocked up for you:-
http://ge.tt/2r7NeWm2
To start with, type a value into Column H (1st August), larger than the previous value, then click away (or press enter or down arrow) and you'll see the second table update, including the yearly total and average to date. Do the same for the remaining months then start a second row if you wish. Totals and averages should update.
As you can see, I haven't actually set the sample up as tables but you can do this yourself just to see that it all still works as it should. Remember to keep at least one column between the tables as per the sample.
Following is the code for this:-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(Target, Range("A2:M" & lr)) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Range("O2:Z" & lr).Formula = "=IF(B2-A2=0,"""",B2-A2)"
Range("AA2:AA" & lr).Formula = "=SUM(O2:Z2)"
Range("AB2:AB" & lr).Formula = "=AVERAGE(O2:Z2)"
For Each c In Range("O2:Z" & lr)
If Left(c.Value, 1) = "-" Then
c.ClearContents
End If
Next c
Sheet1.Columns.AutoFit
Application.ScreenUpdating = True
End Sub
It is a Worksheet_Change event and needs to be placed into the sheet1 (or your master sheet) module.
Let us know your thoughts.
Cheerio,
vcoolio.
jamishore
Posts
9
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
September 13, 2017
Sep 13, 2017 at 04:53 PM
Sep 13, 2017 at 04:53 PM
That worked great! Thank you soooo much.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Sep 13, 2017 at 09:41 PM
Sep 13, 2017 at 09:41 PM
Hi Jami,
You're welcome. I'm glad that I was able to help.
Cheerio,
vcoolio.
You're welcome. I'm glad that I was able to help.
Cheerio,
vcoolio.
