# Average of 3 most recent points of data

#### ErnieCooper

##### New Member
Hello All,

Needing a bit of help on this one: I need to find an average of the 3 most recent low points on a table.

 C E F G H I J K L M N O P Q R 1 04/01/2022 24/12/2021 23/12/2021 22/12/2021 21/12/2021 20/12/2021 17/12/2021 16/12/2021 15/12/2021 14/12/2021 13/12/2021 10/12/2021 09/12/2021 08/12/2021 72 TEST DATA 1​ 2​ 0​ 4​ 1​ 3​ 4​ 3​ 6​ 5​ 5​ 4​ 1​ 8​ 73 false false LOWEST false LOWEST false false LOWEST false LOWEST false false LOWEST false

Each morning a new column is added with a new date and the days data.
I need a formula running that picks up the the 3 most recent points where is says "LOWEST" and gets an average of the data.
I can get it running with an averageif over all time, but this will become defunct.

Ideally, it would be good if I could have another cell where I can set how many low points I look at:- if I wanted to look at the most recent 4 or 5 for example.

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRS
1EFGHIJKLMNOPQR
2104/01/202224/12/202123/12/202122/12/202121/12/202120/12/202117/12/202116/12/202115/12/202114/12/202113/12/202110/12/202109/12/202108/12/2021
372TEST DATA12041343655418
431.333333373FALSEFALSELOWESTFALSELOWESTFALSEFALSELOWESTFALSELOWESTFALSEFALSELOWESTFALSE
Lists
Cell Formulas
RangeFormula
B4B4=AVERAGEIFS(E3:R3,E2:R2,">="&INDEX(E2:R2,AGGREGATE(15,6,(COLUMN(E2:R2)-COLUMN(E2)+1)/(E4:R4="lowest"),A4)),E4:R4,"LOWEST")

I'm getting a #DIV/0! error when I paste that in...

Are your dates real dates, or text?
Also check that your numbers are real numbers.

Are your dates real dates, or text?
Also check that your numbers are real numbers.
Just checked all that
When evaluating the formula the problem was with the dates in criteria1 - for some reason, if I put a space in between the "> =" then the div/0 then the formula doesn't error but it doesn;t come up with the correct answer.

In that case can you post some sample data (along with the formula) that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Replies
4
Views
127
Replies
3
Views
212
Replies
4
Views
412
Replies
2
Views
233
Replies
9
Views
199

1,203,546
Messages
6,056,035
Members
444,840
Latest member
RazzelDazel

### 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.

### Which adblocker are you using?

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