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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,240
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
Joined
Sep 25, 2002
Messages
61
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
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,))
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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