smide
Board Regular
- Joined
- Dec 20, 2015
- Messages
- 162
- Office Version
- 2016
- Platform
- Windows
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.
<tbody>
</tbody>
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.
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.
A | B | C | D | E | F | |
1 | Product list | |||||
2 | orange | apple | 4 | 7 | orange | 7.33 |
3 | tomato | peach | 1 | 14 | peach | ... |
4 | cabbage | orange | 8 | 9 | ... | ... |
5 | orange | plum | 5 | 6 | ||
6 | apple | peach | 16 | 11 | ||
7 | plum | orange | 8 | 8 | ||
8 | ... | .... | ... | ... |
<tbody>
</tbody>
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.