Formula to get average of 3 lowest numbers in a changing range.

[chrisj2812]

Hi All,

I've spent far too long trying to figure out this so any help from the group is really appreciated.

I need the average of the lowest 3 numbers in the changing range (index3) to appear in the end column.

For example, the average for range Dundalk 44613 is (0.01 +0.03 + 0.05) / 3 = 0.03

Many thanks in advance

Chris

 

[BSALV]

Map1
	A	B	C
1	id	number	small3
2	a	0,054489	0,118203
3	a	0,836725
4	a	0,337003
5	a	0,964188
6	a	0,999119
7	a	0,479222
8	a	0,869039
9	a	0,100095
10	a	0,894006
11	a	0,798181
12	a	0,200026
13	a	0,417769
14	a	0,856677
15	a	0,222464
16	a	0,756726
17	a	0,847696
18	a	0,50534
19	b	0,783834	0,105555
20	b	0,280079
21	b	0,030635
22	b	0,969359
23	b	0,419816
24	b	0,528728
25	b	0,19785
26	b	0,504649
27	b	0,11547
28	b	0,177431
29	b	0,643351
30	b	0,985646
31	b	0,810614
32	b	0,432601
33	b	0,315325
34	b	0,17056
35	b	0,282065
36	c	0,69774	0,395072
37	c	0,088251
38	c	0,87888
39	c	0,781585
40	c	0,399225
41	c	0,871271
42	c	0,88622
Blad1

Cell Formulas
Range	Formula
C2:C42	C2	=IF(COUNTIF($A$2:A2,[@id])=1,AVERAGE(AGGREGATE(15,6,[number]/([id]=[@id]),{1;2;3})),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B42	Expression	=B2<=AGGREGAAT(15;6;$B$2:$B$42/($A$2:$A$42=A2);3)	text	NO

 

[kvsrinivasamurthy]

Data range is A2:F30

ARRAY formula in 2nd row then copied down.

=IF(COUNTIF($A$2:$A2,$A2)=1,AVERAGEIFS($F$2:$F$30,$A$2:$A$30,$A2,$F$2:$F$30,"<="&SMALL(IF($A$2:$A$30=$A2,$F$2:$F$30,""),3)))
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

 

[chrisj2812]

Wonderful, you both are amazing. Really appreciate the help!