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

Cubist

Cubist

Well-known Member
Joined
Oct 5, 2023
Messages
2,135
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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

R
  • Solved
Remove unwanted #N/A errors in this LAMBDA/REDUCE formula
2
Replies
11
Views
673
Rnkhch
R
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
209
StephenCrump
S
B
  • Solved
Extract a range of values
Replies
8
Views
526
Fluff
Fluff
S
  • Question Question
Inventory disposal plan
Replies
3
Views
302
Spaceballs_the_fanboy
S
T
  • Question Question
Razz spreadsheet for sports cards
Replies
3
Views
283
Eric W
Eric W

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

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