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

majon96

Board Regular
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.

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

AlanY

Well-known Member
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))

majon96

Board Regular

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?

Fluff

MrExcel MVP, Moderator
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

Fluff

MrExcel MVP, Moderator
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}))))

Fluff

MrExcel MVP, Moderator
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,))

Replies
5
Views
143
Replies
6
Views
128
Replies
4
Views
316
Replies
8
Views
130
Replies
2
Views
243

1,128,156
Messages
5,629,019
Members
416,359
Latest member
Juena

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.

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

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