How to list the numbers identified by AVERAGEIFS and other -IFS functions?

[Rnkhch]

Hello,

I have several numbers in column A, and I use the AVERAGEIFS function in cell B1 to calculate the average of numbers that are greater than or equal to 70 and less than or equal to 100, which is very easy and works very well. Now, what if I wanted a list generated of all the numbers that met my criteria, say in column C. Is there a way to do that without VBA?

This would of course easily apply to other -IFS functions such as MAXIFS and MINIFS.

Also I make my own "STDEVIFS" function, by using as many required nested IF functions, in the form of an array formula. It would be nice to be able to get the list of these values too.

Thanks much for any input!

 

[Toadstool]

Hi Rnkhch,

Is this what you want?

Rnkhch.xlsx
	A	B	C
1	Numbers	87.71429	List
2	91		91
3	112		98
4	98		70
5	120		100
6	70		80
7	100		86
8	80		89
9	86
10	60
11	120
12	89
Sheet1

Cell Formulas
Range	Formula
B1	B1	=AVERAGEIFS($A$2:$A$12,$A$2:$A$12,">="&70,$A$2:$A$12,"<="&100)
C2:C12	C2	=IFERROR(INDEX($A$2:$A$12,AGGREGATE(15,6,ROW($A$2:$A$12)-ROW($A$1)/(($A$2:$A$12>=70)*($A$2:$A$12<=100)),ROW()-ROW($C$1))),"")

 

[Rnkhch]

This is fabulous! Exactly what I needed! Thanks much!

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have 365 you could use

=FILTER(A2:A100,(A2:A100>=70)*(A2:A100<=100))

 

[Toadstool]

This is fabulous! Exactly what I needed! Thanks much!

You're welcome!

 

[Rnkhch]

Thanks Fluff. Just updated some details. My work computer Excel account page shows it is "365 ProPlus Version 1908 (Build 11929.20648 Click-to-Run) Semi-annual channel". I tried your formula, and it gives the "That function isn't valid" error. Perhaps there is some minimum subversion requirement for the Filter function? I'll check my personal laptop Excel version tonight when I get home and see how that one will do.

 

[Fluff]

That build number is over a year old, so you are obviously not getting the updates.
Might be worth checking with your IT department to find out why.

 

[Rnkhch]

The Filter function works very well on my personal laptop. Thanks!

My workplace is so slow implementing software updates.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Rnkhch]

Thanks. By the way, if I mark your post as solution, will that unmark the other solution? Both are interesting solutions. But I wasn't sure if I could mark more than one post as solution.