Find average of last three occurences (data in two columns)

[smide]

Hello.


In columns A (A2:A600) and B (B2:B600) I have products and in columns C (C2:C600) and D (D2:D600) their prices.
When product is in column A his price is in column C (same row) and when product is in column B his price is in column D.


In column E I have a list of all products (in cells E2:E50).


With array forumla in cell F2:
=IF(E2="","",LOOKUP(9.99999999999999E+307,IF($A$2:$A$600=E2,$C$2:$C$600,IF($B$2:$B$600=E2,$D$2:$D$600)))) , I can find last value/price for each product but I need average of last three prices.


example.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/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]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Product list[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]orange[/TD]
[TD]apple[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD]orange[/TD]
[TD="align: center"]7.33[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]tomato[/TD]
[TD]peach[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]14[/TD]
[TD]peach[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]cabbage[/TD]
[TD]orange[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]orange[/TD]
[TD]plum[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]apple[/TD]
[TD]peach[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]plum[/TD]
[TD]orange[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]...[/TD]
[TD]....[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

explanation: Last three orange prices are 8 (D7), 5 (C5), 9 (D4) -- =(8+5+9)/3 = 7.33


*Any solution with helper columns would be fine also.

 

[Snakehips]

Maybe like...

Excel 2010
	A	B	C	D	E	F	G	H	I
1					Product list	Average*		Helper	Helper
2	orange	apple	4	7	orange	7.33		0	1
3	tomato	peach	1	14	peach	13.67		1	0
4	cabbage	orange	8	9	tomato	1.00		1	1
5	orange	plum	5	6	cabbage	8.00		1	1
6	apple	peach	16	11	apple	10.00		1	1
7	plum	orange	8	8	plum	7.00		1	1
8	peach	plum	15	7				1	1
9	peach	apple	15	7				1	1
10
Sheet2

Cell Formulas
Range	Formula
F2	=IFERROR(SUMPRODUCT(($A$2:$B$600=E2)*($C$2:$D$600)*($H$2:$I$600=1))/MIN(3,COUNTIF($A$2:$B$600,E2)),"")
H2	=IF(A2="","",IF(COUNTIF($A2:$B$600,A2)<=3,1,0))
I2	=IF(B2="","",IF(COUNTIF($A2:$B$600,B2)<=3,1,0))

Helper columns copy down to row 600 Average column copy down to row 50.
I have assumed that if less than 3 entries for a product then return average for eg 1 or 2 ???
The only obvious potential error I see for what I have done is if you were to have the same product in both A and B of the same row, if that row is the second to last that contains that product!! ?????
Hope that helps.

 

[smide]

When my range (A2:B600) contains empty cells for some reason it does not work...


example.
If my range is populated from A2 to B236 (both products and prices) then formula has to be =IFERROR(SUMPRODUCT(($A$2:$B$236=E2)*($C$2:$D$236)*($H$2:$I$236=1))/MIN(3,COUNTIF($A$2:$B$236,E2)),"") , otherwise I'm getting blank cell as result...


Using excel 2007.

 

[Snakehips]

I'm sorry but at the moment I'm not seeing why that should be.
Below is a shot of my test, with some blanks, with some text entry for product and price and with some formulas generating product and price.
All appears good as I see it.

Excel 2010
	A	B	C	D	E	F	G	H	I
1					Product list	Average*		Helper	Helper
2	orange	apple	4	7	orange	4.67		0	1
3	tomato	peach	1	14	peach	11.33		1	0
4	cabbage	orange	8	9	tomato	1.00		1	0
5	orange	plum	5	6	cabbage	8.00		1	1
6	apple	peach	16	11	apple	10.00		1	1
7	plum	orange	8	8	plum	7.00		1	1
8
9	peach	apple	15	7				1	1
10
11
12	peach		8					1
13
14		orange		1					1
15
Sheet2

Please report back if you can add anything and I will do the same.

 

[Peter_SSs]

I have assumed that you have enough data that there will be at least 3 values for each item. If not the formula will error & you would need to advise what result you want in that case.
Like Snakehips I have also assumed that the same product will not appear twice on the same row.

Column G is my helper to produce the column F results. If you did want to do it without a helper column then you can use the much larger formula in column H.

The highlights were just to help me check a couple visually.

Excel Workbook
	A	B	C	D	E	F	G	H
1					Product list	Average	3rd Last Index	Average
2	orange	apple	4	7	orange	10.666667	14	10.66666667
3	tomato	peach	1	14	peach	12	19	12
4	cabbage	orange	8	9	tomato	7.3333333	2	7.333333333
5	orange	plum	5	6	cabbage	7	3	7
6	apple	peach	16	11	apple	10	16	10
7	plum	orange	8	8	plum	15	15	15
8	peach	plum	15	7
9	peach	apple	15	7
10	orange	apple	8	2
11	tomato	peach	6	4
12	cabbage	orange	3	6
13	orange	plum	5	5
14	apple	peach	4	8
15	plum	orange	5	9
16	peach	plum	6	4
17	peach	apple	3	9
18
19	plum		18
20	orange	peach	15	14
21	plum	plum	6	17
22	peach		10
23	apple	peach	9	12
24		orange		8
25		tomato		15
26		cabbage		10
27
28	apple		12
29
Last 3