Average value by minimum path rule and with updating/changing criteria

[Krabben]

Hey

I'm looking for some help to make my life and thesis run abit smoother

So basically i'm looking for a formula that can find the average in a particular way with a updating/changing criteria.

1. Lets say i have a data set ranging from B1:B10
2. I have to average the dataset by using a "minimum path rule", starting from the highest cell number (B10 - Cell value 20 in the picture) and then working towards B1.
3. With "minimum path rule" i mean, that any cell value higher than the previously minimum cell value, should be excluded from the equation.

I hope my question is understandable If possible i would like to avoid a helper coloumn, since my dataset changes constantly.

Any ideas?



Thanks

 

[Eric W]

There's a minor tweak to make it work in a different range:

Book1
	B	C	D	E
1
2
3
4
5			Minimum path average
6			14.6666667	7
7
8			Cap
9			99
10
11	8
12	12
13	6
14	14
15	16
16	8
17	17
18	18
19	19
20	20
Sheet1

Cell Formulas
Range	Formula
D6	D6	=AVERAGE(IF(B11:B20<D9,IF(B11:B20<=SUBTOTAL(5,OFFSET(B11,ROW(B11:B20)-ROW(B11),0,ROW(B20)-ROW(B11:B20)+1)),B11:B20)))
E6	E6	=AVERAGE(IF(B11:B20<D9,IF(B11:B20<=SUBTOTAL(5,OFFSET(B11,0,0,ROW(B11:B20)-ROW(B11)+1)),B11:B20)))
Press CTRL+SHIFT+ENTER to enter array formulas.