Average - How do I get an average of the 3 most right values in an array

majon96

Board Regular
Joined
Sep 25, 2002
Messages
61
I have information in twelve columns (months) on multiple rows.
Some months there are no data entered.

I want to make a formula that calculates the average of the three most recent values (new values always entered to the right of the most recent) which means that sometimes it's the 3 most right cells and on other months it takes many more cells in order to find three values to make an average from. If there aren't three values to be found it can average the values found the last 12 months.

Thanks in advance
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
try this

Book1
ABCDEFGHIJKLMN
112345678910111211
2123456787
312343
4121.5
5123456789109
Sheet1
Cell Formulas
RangeFormula
N1:N5N1=AVERAGE(OFFSET($A$1,0,COUNT($B1:$M1)-2,1,3))
 
Upvote 0
Thanks for the reply

In my set of numbers there are some empty cells within the array. For example on row 1 the number in column K might be missing and then I want the average to be calculated on J, L, M instead.
That won't be taken care of in the solution above if I read it correct?
 
Upvote 0
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 
Upvote 0
As long as you have 2010 or newer & will never have just one value.

+Fluff v2.xlsm
ABCDEFGHIJKLMN
1
2A589710083556824402558857372
3B602341.5
4C39621255771252336550
5D55862154
6E773757
7
Data
Cell Formulas
RangeFormula
N2:N6N2=AVERAGE(INDEX(B2:M2,AGGREGATE(14,6,(COLUMN(B2:M2)-COLUMN(B2)+1)/(B2:M2<>""),IF(COUNT(B2:M2)>2,{1,2,3},{1,2}))))
 
Upvote 0
This will handle only one entry in the row
+Fluff v2.xlsm
ABCDEFGHIJKLMN
1
2A589710083556824402558857372
3B6060
4C39621255771252336550
5D55862154
6E773757
Data
Cell Formulas
RangeFormula
N2:N6N2=AVERAGE(INDEX(INDEX(B2:M2,AGGREGATE(14,6,(COLUMN(B2:M2)-COLUMN(B2)+1)/(B2:M2<>""),MIN(3,COUNT(B2:M2)))):M2,))
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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