Range of Numbers percentile avg

[blimes]

I have a column of numbers in A1:A1000 and a number that is in cell G1. In cell G2 I want to make a average of the numbers in Column A1:A1000 that are within 5% +or- of the value in G2. Can anyone help me on this?

Thanks
Adam

 

[Eric W]

Like this?

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	7197						750
2	5342						754.25
3	8946
4	4390
5	7125
Sheet8

Cell Formulas
Range	Formula
G2	G2	=AVERAGE(IF((A1:A1000>=G1*0.95)*(A1:A1000<=G1*1.05),A1:A1000))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Another option

=AVERAGEIFS(A:A,A:A,"<="&G1*1.05,A:A,">="&G1*0.95)

 

[Eric W]

Another option

=AVERAGEIFS(A:A,A:A,"<="&G1*1.05,A:A,">="&G1*0.95)

AVERAGEIFS isn't available in Excel 2013.

 

[Fluff]

I thought it came out with 2007, but if not I'm sure I had it in 2013.

 

[Fluff]

Just checked the Excel Functions Bible "By Norman Harker in association with Ron de Bruin" and that shows it appeared with 2007

 

[Eric W]

AVERAGEIF 2007

AVERAGEIF function - Microsoft Support

Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.

support.microsoft.com

AVERAGEIFS 2019

AVERAGEIFS function - Microsoft Support

This article describes the formula syntax and usage of the AVERAGEIFS function, which returns the average (arithmetic mean) of all cells that meet multiple criteria.

support.microsoft.com

 

[Fluff]

I think that MS have that wrong as the Excel Function Bible was last modified in 2014 & shows that function so it must have been available in 2013.

 

[Eric W]

It appears you're right and Microsoft docs are wrong. I have 2013 loaded on another computer, and it has AVERAGEIFS.