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

#### smide

##### Board Regular
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.

 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.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Snakehips

##### Well-known Member
Maybe like...

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

##### Board Regular
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

##### Well-known Member
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.<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style=";">Product list</td><td style=";">Average*</td><td style="text-align: right;;"></td><td style=";">Helper</td><td style=";">Helper</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">orange</td><td style=";">apple</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style=";">orange</td><td style="text-align: right;;">4.67</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">tomato</td><td style=";">peach</td><td style="text-align: center;;">1</td><td style="text-align: center;;">14</td><td style=";">peach</td><td style="text-align: right;;">11.33</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">cabbage</td><td style=";">orange</td><td style="text-align: center;;">8</td><td style="text-align: center;;">9</td><td style=";">tomato</td><td style="text-align: right;;">1.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">orange</td><td style=";">plum</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style=";">cabbage</td><td style="text-align: right;;">8.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">apple</td><td style=";">peach</td><td style="text-align: center;;">16</td><td style="text-align: center;;">11</td><td style=";">apple</td><td style="text-align: right;;">10.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">plum</td><td style=";">orange</td><td style="text-align: center;;">8</td><td style="text-align: center;;">8</td><td style=";">plum</td><td style="text-align: right;;">7.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">peach</td><td style=";">apple</td><td style="text-align: center;;">15</td><td style="text-align: center;;">7</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">peach</td><td style="text-align: right;;"></td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style=";">orange</td><td style="text-align: center;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />

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

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCDEFGH
1Product listAverage3rd Last IndexAverage
2orangeapple47orange10.6666671410.66666667
3tomatopeach114peach121912
4cabbageorange89tomato7.333333327.333333333
5orangeplum56cabbage737
6applepeach1611apple101610
7plumorange88plum151515
8peachplum157
9peachapple157
10orangeapple82
11tomatopeach64
12cabbageorange36
13orangeplum55
14applepeach48
15plumorange59
16peachplum64
17peachapple39
18
19plum18
20orangepeach1514
21plumplum617
22peach10
23applepeach912
24orange8
25tomato15
26cabbage10
27
28apple12
29
Last 3

Last edited:

Replies
2
Views
140
Replies
10
Views
432
Replies
3
Views
177
Replies
14
Views
465
Replies
1
Views
70

1,130,258
Messages
5,641,178
Members
417,196
Latest member
Liziz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back