Weekly Averages

C

chorina13

New Member
Joined
Mar 23, 2024
Messages
15
Office Version
  1. 2021
Platform
  1. Windows
Hi,
I am trying to calculate a weekly average of my bike rides done. I have attached a part copy of the area I am using but having trouble trying to isolate each week, and then count the number of rides for that and and come up with an average. Here's hoping someone can help
Thank you
Blackie
 

Attachments

  • 2024-05-01 17 50 45.jpg
    2024-05-01 17 50 45.jpg
    206.7 KB · Views: 14
Sort by date Sort by votes
Snakehips said:
@chorina13 Does this help?

Testing Formula Update.xlsm
BCDEFGHI
3TotalPer Ride
4Average km per week150.0325.67
5
6
7DateMonthWeekBikeKmCuml KmWeek KmAv Km / Ride/Wk
8Monday 1 January 202411Felt 72030.2730.27  
9Tuesday 2 January 20241130.27  
10Wednesday 3 January 202411Felt 72022.7853.05  
11Thursday 4 January 20241153.05  
12Friday 5 January 202411Felt 72034.0687.11  
13Saturday 6 January 20241187.11  
14Sunday 7 January 202411Felt 72015.15102.26102.2625.57
15Monday 8 January 202412102.26  
16Tuesday 9 January 202412Felt 72028.3130.56  
17Wednesday 10 January 202412130.56  
18Thursday 11 January 202412Felt 72018.88149.44  
19Friday 12 January 202412149.44  
20Saturday 13 January 202412149.44  
21Sunday 14 January 202412Felt 72033.66183.1157.5426.95
22Monday 15 January 202413183.1  
23Tuesday 16 January 202413183.1  
24Wednesday 17 January 202413183.1  
25Thursday 18 January 202413183.1  
26Friday 19 January 202413183.1  
27Saturday 20 January 202413Felt 72021.18204.28  
28Sunday 21 January 202413204.28151.7721.18
29Monday 22 January 202414Felt 72015.44219.72  
30Tuesday 23 January 202414219.72  
31Wednesday 24 January 202414Felt 72042.52262.24  
32Thursday 25 January 202414262.24  
33Friday 26 January 202414262.24  
34Saturday 27 January 202414262.24  
35Sunday 28 January 202414262.24188.5528.98
2018
Cell Formulas
RangeFormula
H4:I4H4=AVERAGE(H8:H35)
G8:G35G8=SUM(F$8:F8)
H8:H9H8=IF(D9<>D8,G8-SUM(I7:I$8),"")
I8:I35I8=IF(H8="","",AVERAGEIF(D$8:D8,D8,F$8:F8))
H10:H35H10=IF(D11<>D10,G10-SUM(I$8:I9),"")
Click to expand...
Hi Again, Sorry to be a bother, but I have entered your formulas as per instructions, but have now struck one small snag. After entering the formula in Cell I8 it calculates my average based on 7 days and not the 4 only rides for that week. Your example shows the average as 25.57 kms and mine shows 14.61kms, and so on down the column.
Sorry about this.
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
alansidman said:
1. Highlight your range of data and then click on Data, Get and Transform Data, From Range or Table.
2. With your data now in the Power Query Editor, Click on the drop down arrow in the Bike Column
3. Uncheck the Felt 720 box
4. Highlight the Week column and then select Group By from the PQ Ribbon
5. Type WeekAverage in the New Column Name Box
6. Select Average in the next field, then select the KM field
7. Select Close and Load and then select where you wish your data to go.
Click to expand...
Sorry but I am going to put it in the to hard basket, all I got was 2 columns, the week column showing 1 to 52 and the Week average showing about halfway down two cells with some numbers it which appear to have no relationship to what I am after. Also you say to clear or remove Felt 720, when in fact I now have two bikes, the BlazeX only just recently, which I am hoping to include in the mix together, so will be some more calculations to differentiate between the two, to work out..
 
Upvote 0
when in fact I now have two bikes,
Click to expand...

Which you did not disclose in your sample. How are we to know how to help you if you do not present a realistic sample of your data. Good Luck. I am out of here.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Formula to calculate average and not include blanks or zeros in the calculation
Replies
4
Views
313
etaf
etaf
A
  • Question
Two dimensional dynamic named range over a pivot table not working as I thought
Replies
4
Views
410
ashleighbrown
A
C
  • Question
Rugby - Biggest Win
Replies
8
Views
221
chorina13
C
T
  • Question
Smooth monthly trend to achieve assumed average YoY growth
Replies
6
Views
144
Cubist
Cubist
O
  • Solved
Calculate running averages, according to criteria in other cells
Replies
3
Views
252
Fluff
Fluff

Forum statistics

Threads
1,216,075
Messages
6,128,657
Members
449,462
Latest member
Chislobog

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top