Taking the average of the last 3,6,12 values.

Cubist

Cubist

Well-known Member
Joined
Oct 5, 2023
Messages
760
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have a similar but slightly different than this thread. I want to take the average of the last 3,6,12 cells of each column. For the sake of an example, let's say I need the last 3 (highlighted in the sample).

1) I do not want to include the 0 diagonal or the error cells.
2) When it gets to column M, there are only one values. When this happens, I want to average the one values and {1;1} (a total of 3 values).

The formula provided in the other thread does not exclude the 0 diagonal.
Excel Formula: 
=AVERAGE(TAKE(VSTACK(SEQUENCE(3,,1,0),TOCOL(C1:C12,3)),-3))

Book2
BCDEFGHIJKLMN
110/31/20223.9122911.128521.0146711.0056271.0019821.0003351.00091511110
211/30/20224.4451341.032741.0035381.0035361.0040311.0016741.0010161110#DIV/0!
312/31/20223.1592671.2817081.0157461.0026711.0005060.9999771110#DIV/0!#DIV/0!
41/31/20234.7849791.5704261.0177921.0066310.9384331.001111.0117811.0008080#DIV/0!#DIV/0!#DIV/0!
52/28/20237.2937051.1344091.0079681.0047321.0026121.0008350.998950#DIV/0!#DIV/0!#DIV/0!#DIV/0!
63/31/20233.6772281.2381192.5261851.0023941.0006270.9937690#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
74/30/20234.3575531.489251.0120721.0296211.0074250#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
85/31/20233.3266431.0638451.0276241.0031440#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
96/30/20233.4871531.1307111.0240140#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
107/31/20235.7335361.0393440#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
118/31/20232.338320#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
129/30/20230#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
Sheet1
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
How about
Excel Formula: 
=AVERAGE(TAKE(DROP(VSTACK(SEQUENCE(3,,1,0),TOCOL(C1:C12,3)),-4),-3))
 
Upvote 0
Solution
Fluff said:
How about
Excel Formula: 
=AVERAGE(TAKE(DROP(VSTACK(SEQUENCE(3,,1,0),TOCOL(C1:C12,3)),-4),-3))
Click to expand...
Hi Fluff,

I'm not getting the expected result.

Book2
ABCDEFGHIJKLMN
110/31/20223.9122911.128521.0146711.0056271.0019821.0003351.00091511110
211/30/20224.4451341.032741.0035381.0035361.0040311.0016741.0010161110#DIV/0!
312/31/20223.1592671.2817081.0157461.0026711.0005060.9999771110#DIV/0!#DIV/0!
41/31/20234.7849791.5704261.0177921.0066310.9384331.001111.0117811.0008080#DIV/0!#DIV/0!#DIV/0!
52/28/20237.2937051.1344091.0079681.0047321.0026121.0008350.998950#DIV/0!#DIV/0!#DIV/0!#DIV/0!
63/31/20233.6772281.2381192.5261851.0023941.0006270.9937690#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
74/30/20234.3575531.489251.0120721.0296211.0074250#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
85/31/20233.3266431.0638451.0276241.0031440#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
96/30/20233.4871531.1307111.0240140#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
107/31/20235.7335361.0393440#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
118/31/20232.338320#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
129/30/20230#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!
13
14Fluff's Formula3.7871421.2872591.5173151.004678
15Expected3.8530031.0779661.0212361.01172
16
17CheckFALSEFALSEFALSEFALSE
Sheet1
Cell Formulas
RangeFormula
C14:F14C14=AVERAGE(TAKE(DROP(VSTACK(SEQUENCE(3,,1,0),TOCOL(C1:C12,3)),-4),-3))
C15C15=AVERAGE(C9:C11)
D15D15=AVERAGE(D8:D10)
E15E15=AVERAGE(E7:E9)
F15F15=AVERAGE(F6:F8)
C17:F17C17=C15=C14
 
Upvote 0
Misread what you wanted, change the -4 to -1
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Extract a range of values
Replies
8
Views
345
Fluff
Fluff
J
  • Question
SQL Link Refresh Scrambles Order
Replies
5
Views
195
jarett
J
K
  • Solved
=IFERROR help
Replies
4
Views
608
Peter_SSs
Peter_SSs
D
  • Question
VBA Code - the code is not pasting to the column I need it to.
Replies
2
Views
573
dragontbone
D
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
311
awoohaw
awoohaw

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

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