Average of 3 most recent points of data

[ErnieCooper]

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.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1					E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2			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
3			72	TEST DATA	1	2	0	4	1	3	4	3	6	5	5	4	1	8
4	3	1.3333333	73		FALSE	FALSE	LOWEST	FALSE	LOWEST	FALSE	FALSE	LOWEST	FALSE	LOWEST	FALSE	FALSE	LOWEST	FALSE
Lists

Cell Formulas
Range	Formula
B4	B4	=AVERAGEIFS(E3:R3,E2:R2,">="&INDEX(E2:R2,AGGREGATE(15,6,(COLUMN(E2:R2)-COLUMN(E2)+1)/(E4:R4="lowest"),A4)),E4:R4,"LOWEST")

 

[ErnieCooper]

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

 

[Fluff]

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

 

[ErnieCooper]

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.

 

[Fluff]

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.