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

smide

Board Regular
Joined
Dec 20, 2015
Messages
153
Office Version
  1. 2007
Platform
  1. 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.

ABCDEF
1Product list
2orangeapple47orange7.33
3tomatopeach114peach...
4cabbageorange89......
5orangeplum56
6applepeach1611
7plumorange88
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
Joined
May 17, 2009
Messages
5,179
Office Version
  1. 2013
Platform
  1. Windows
Maybe like...
<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;;">7.33</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;;">13.67</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;;">1</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=";">peach</td><td style=";">plum</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;">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></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 /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=IFERROR(<font color="Blue">SUMPRODUCT(<font color="Red">(<font color="Green">$A$2:$B$600=E2</font>)*(<font color="Green">$C$2:$D$600</font>)*(<font color="Green">$H$2:$I$600=1</font>)</font>)/MIN(<font color="Red">3,COUNTIF(<font color="Green">$A$2:$B$600,E2</font>)</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=IF(<font color="Blue">A2="","",IF(<font color="Red"> COUNTIF(<font color="Green">$A2:$B$600,A2</font>)<=3,1,0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=IF(<font color="Blue">B2="","",IF(<font color="Red"> COUNTIF(<font color="Green">$A2:$B$600,B2</font>)<=3,1,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

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
Joined
Dec 20, 2015
Messages
153
Office Version
  1. 2007
Platform
  1. Windows
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...:confused:


Using excel 2007.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,179
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,580
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top