Calculate average for first value(s) after a pre-defined text marker

[smide]

Hello.




In column B (B2:B600) I have a product list (about 25 different products) and in column E their prices.
In column D I have a list of markers for each product. Markers are S - sold, W - waiting and A - away.


In column G (from cell G2 and downwards) I have list off ALL potential products.


I need a formula to find average of first price occurrence for each product IF the marker for previous occurrence for that product was A (away) and to put those results/averages in Column H.


example.


(for simplicity only for product 'apple')

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"][/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"]12.5[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]sugar[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]sugar[/TD]
[TD="align: center"]....[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"]....[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"]....[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]sugar[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"][/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]sugar[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]32[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"][/TD]
[TD="align: center"]S[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]sugar[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]20[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"]orange[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"]sugar[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"][/TD]
[TD="align: center"]apple[/TD]
[TD="align: center"][/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"][/TD]
[TD="align: center"]sugar[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="align: center"]....[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]....[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

cell H2 is average value for apple (first price after marker A for previous apple product): =average (E7,E11) = average(9,16) = 12.5

 

[Tetra201]

Here is a monstrous array formula (to be entered using Ctrl+Shift+Enter, not just Enter) that returns the desired result for the posted dataset.
Caveat: "apple A" must not be the last entry for "apple".
Hope somebody can offer a shorter and more elegant solution.

=SUM(IF(INDEX($D$2:$D$600,N(IF(,,SMALL(IF($B$2:$B$600=G2,ROW($B$2:$B$600)-ROW($B$2)+1),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$600,G2)))))))="A",INDEX(INDEX($E$2:$E$600,N(IF(,,SMALL(IF($B$2:$B$600=G2,ROW($B$2:$B$600)-ROW($B$2)+1),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$600,G2))))))),N(IF(,,ROW(INDIRECT("1:"&COUNTIF($B$2:$B$600,G2)))+1)))))/COUNTIFS($B$2:$B$600,G2,$D$2:$D$600,"A")

 

[Peter_SSs]

If I am understanding correctly, the cells to be averaged in your example would be E7 and E11.
Would it be possible for you to have data as shown below (only change I have made is D2 from "S" to "A")?
If that is possible, should cell E14 also be included in the average, even though its own marker is "A"?

Book1
	B	C	D	E
2	apple		A	12
3	sugar			6
4	apple		A	14
5	orange			28
6	sugar			5
7	apple		W	9
8	sugar			32
9	orange			11
10	apple		A	8
11	apple		S	16
12	sugar			20
13	orange			3
14	sugar			19
15	apple		W	9
16	sugar			5
Sheet1