AverageIF, last 4 values

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
737
Office Version
  1. 365
Platform
  1. Windows
hello, i have a column of days of week, mon-sat and values next to them. i want to do a running average of each day (monday, etc) but want to change how many Mondays to use

so if a dynamic cell i will have a value like 3 or 4 or 5 etc.

and the formula will get the average of the "Last 3 Mondays" or "Last 4 Mondays" etc. And to further annoy you, the dates are not in exact sequential order, some weeks the Monday is missing. so formula needs to be able to "find" the last n 'Mondays' regardless of how many rows back they are. thanks if you can assist


81​
Saturday
4​
Monday
94​
Tuesday
161​
Wednesday
143​
Thursday
155​
Friday
145​
Saturday
7​
Monday
88​
Tuesday
133​
Wednesday
140​
Thursday
144​
Friday
146​
Saturday
16​
Monday
16​
Tuesday
188​
Wednesday
148​
Thursday
145​
Friday
135​
Saturday
9​
Monday
118​
Tuesday
160​
Wednesday
142​
Thursday
156​
Friday
133​
Saturday
7​
Monday
84​
Tuesday
120​
Wednesday
160​
Thursday
200​
Friday
170​
Saturday
5​
Monday
122​
Tuesday
138​
Wednesday
128​
Thursday
169​
Friday
147​
Saturday
57​
Monday
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JamesCanale

Well-known Member
Joined
Jan 13, 2021
Messages
854
Office Version
  1. 365
Platform
  1. Windows
If you have 365, this will help:
MrExcelPlayground12.xlsx
ABCDE
13
281SaturdayMonday23.00
34MondayTuesday108.00
494TuesdayWednesday139.33
5161WednesdayThursday143.33
6143ThursdayFriday175.00
7155FridaySaturday150.00
8145Saturday
97Monday
1088Tuesday
11133Wednesday
12140Thursday
13144Friday
14146Saturday
1516Monday
1616Tuesday
17188Wednesday
18148Thursday
19145Friday
20135Saturday
219Monday
22118Tuesday
23160Wednesday
24142Thursday
25156Friday
26133Saturday
277Monday
2884Tuesday
29120Wednesday
30160Thursday
31200Friday
32170Saturday
335Monday
34122Tuesday
35138Wednesday
36128Thursday
37169Friday
38147Saturday
3957Monday
Sheet4
Cell Formulas
RangeFormula
E2:E7E2=AVERAGE(INDEX(FILTER($A$2:$A$39,$B$2:$B$39=D2),SEQUENCE($A$1,1,ROWS(FILTER($A$2:$A$39,$B$2:$B$39=D2))-$A$1+1)))
 
Solution

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
737
Office Version
  1. 365
Platform
  1. Windows
thanks. (I hate Excel formulas!) how can i open the Excel Playground and play with it... i dont see any buttons or links... thanks!
 

JamesCanale

Well-known Member
Joined
Jan 13, 2021
Messages
854
Office Version
  1. 365
Platform
  1. Windows
Thats just my file that I use to work out the solutions to problems. the tool to post mini-spreadsheets xl2bb, puts the filename on top.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,439
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
737
Office Version
  1. 365
Platform
  1. Windows
is there a way i can "see" the values this formula is using to do the average? thanks
 

JamesCanale

Well-known Member
Joined
Jan 13, 2021
Messages
854
Office Version
  1. 365
Platform
  1. Windows
sure. Delete the "AVERAGE" in the formula. It will show an array of numbers. Most of them will be a SPILL error.

So don't delete the AVERAGE, replace it with "TRANSPOSE". That will do the trick.

MrExcelPlayground12.xlsx
ABCDEFG
13
281SaturdayMonday23.00
34MondayTuesday108.00
494TuesdayWednesday139.33
5161WednesdayThursday143.33
6143ThursdayFriday175.00
7155FridaySaturday150.00
8145Saturday
97Monday
1088TuesdayMonday7557
11133WednesdayTuesday11884122
12140ThursdayWednesday160120138
13144FridayThursday142160128
14146SaturdayFriday156200169
1516MondaySaturday133170147
1616Tuesday
17188Wednesday
18148Thursday
19145Friday
20135Saturday
219Monday
22118Tuesday
23160Wednesday
24142Thursday
25156Friday
26133Saturday
277Monday
2884Tuesday
29120Wednesday
30160Thursday
31200Friday
32170Saturday
335Monday
34122Tuesday
35138Wednesday
36128Thursday
37169Friday
38147Saturday
3957Monday
Sheet4
Cell Formulas
RangeFormula
E2:E7E2=AVERAGE(INDEX(FILTER($A$2:$A$39,$B$2:$B$39=D2),SEQUENCE($A$1,1,ROWS(FILTER($A$2:$A$39,$B$2:$B$39=D2))-$A$1+1)))
E10:G15E10=TRANSPOSE(INDEX(FILTER($A$2:$A$39,$B$2:$B$39=D10),SEQUENCE($A$1,1,ROWS(FILTER($A$2:$A$39,$B$2:$B$39=D10))-$A$1+1)))
Dynamic array formulas.
 

Forum statistics

Threads
1,186,178
Messages
5,956,392
Members
438,249
Latest member
georgebasalic3

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
Top