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]

Welcome to the MrExcel forum!

Try:

Book1
	B	C	D
1	8
2	12
3	6
4	14
5	16
6	8		14.66667
7	17
8	18
9	19
10	20
Sheet9

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

 

[Krabben]

Welcome to the MrExcel forum!

Try:

Book1
	B	C	D
1	8
2	12
3	6
4	14
5	16
6	8		14.66667
7	17
8	18
9	19
10	20
Sheet9

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

Hey Eric

Thank you - your formula worked perfectly!

Cheers

 

[Eric W]

Happy to help! Thanks for the update.

 

[Krabben]

Welcome to the MrExcel forum!

Try:

Book1
	B	C	D
1	8
2	12
3	6
4	14
5	16
6	8		14.66667
7	17
8	18
9	19
10	20
Sheet9

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

Might aswell throw you an additional question, since you know a thing or two about excel

Lets say i have a cell value of 10 from another dataset. Now i want the formula to exclude the values 20,19,18,17, so the average count start at 8. (So the average only counts 8 and 6)

Is that possible?

 

[Krabben]

Might aswell throw you an additional question, since you know a thing or two about excel

Lets say i have a cell value of 10 from another dataset. Now i want the formula to exclude the values 20,19,18,17, so the average count start at 8. (So the average only counts 8 and 6)

Is that possible?

I have another question, which i should have posted with post #5

If i simply want the formula to start at B1 (Cell value 8) and "run" towards B10, what should i do?

I apologize for my somewhat simple questions, but this could really be a time saver

 

[Eric W]

For your first question, try:

Book1
	B	C	D
1	8
2	12
3	6
4	14
5	16		Minimum path average
6	8		7
7	17
8	18		Cap
9	19		10
10	20
Sheet9

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

And I don't quite understand what you want with your second question. Do you mean that you want the formula in C1, and that as you drag it down, you want C2, C3, etc. to calculate the result using the B column data up to that point?

 

[Krabben]

For your first question, try:

Book1
	B	C	D
1	8
2	12
3	6
4	14
5	16		Minimum path average
6	8		7
7	17
8	18		Cap
9	19		10
10	20
Sheet9

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

And I don't quite understand what you want with your second question. Do you mean that you want the formula in C1, and that as you drag it down, you want C2, C3, etc. to calculate the result using the B column data up to that point?

Thank you again

Regarding my question, i simply want the nr. 2 point in question #1 changed to

2. I have to average the dataset by using a "minimum path rule", starting from the lowest cell number (B1 - Cell value 8 in the picture) and then working towards B10.

The solution could be very simple, but i think i have confused myself abit

Thank you again Eric, you're a lifesaver

 

[Eric W]

I see, try:

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

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

The E6 formula should do that. On this data set, it just includes 8 and 6.

 

[Krabben]

I see, try:

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

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

The E6 formula should do that. On this data set, it just includes 8 and 6.

Hey again Eric

I've been trying to implement the E6 formula into my document.

It seems to be working if the dataset is in the cells B1:B10.
But if I move it to B11:B20, it skips the number 8 and therefore the average simply becomes 6.

Can you help me fix that?