average of averages

[phoeberunner]

Hi there,

I have a data that shows the average price of a product for each quarter. Now I would like to to get the average price for each half a year. How could I do this in pivot?

Basically, I want to get the average of averages.

Product Year Period Period2 unit Average_price
A 1999 H1 Q1 5 200
A 1999 H1 Q2 15 400
A 1999 H2 Q3 6 500
A 1999 H2 Q4 10 800

Product Year Period unit Average_price
A 1999 H1 20 350
A 1999 H2 16 687.5

thanks

phoeberunner

 

[mikerickson]

You could use a formula like this in I1, dragged down.

=SUMPRODUCT(--($C$1:$C$4=H1), $E$1:$E$4, $F$1:$F$4)/SUMPRODUCT(--($C$1:$C$4=H1), $E$1:$E$4)

<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=25><b>E</b><td width=25><b>F</b><td width=25><b>G</b><td width=25><b>H</b><td width=25><b>I</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>A<td align="right" bgcolor=#FFFFFF>1999<td align="left" bgcolor=#FFFFFF>H1<td align="left" bgcolor=#FFFFFF>Q1<td align="right" bgcolor=#FFFFFF>5<td align="right" bgcolor=#FFFFFF>200<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>H1<td align="right" bgcolor=#FFFFFF>350</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>A<td align="right" bgcolor=#FFFFFF>1999<td align="left" bgcolor=#FFFFFF>H1<td align="left" bgcolor=#FFFFFF>Q2<td align="right" bgcolor=#FFFFFF>15<td align="right" bgcolor=#FFFFFF>400<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>H2<td align="right" bgcolor=#FFFFFF>687.5</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF>A<td align="right" bgcolor=#FFFFFF>1999<td align="left" bgcolor=#FFFFFF>H2<td align="left" bgcolor=#FFFFFF>Q3<td align="right" bgcolor=#FFFFFF>6<td align="right" bgcolor=#FFFFFF>500<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF>A<td align="right" bgcolor=#FFFFFF>1999<td align="left" bgcolor=#FFFFFF>H2<td align="left" bgcolor=#FFFFFF>Q4<td align="right" bgcolor=#FFFFFF>10<td align="right" bgcolor=#FFFFFF>800<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>

 

[phoeberunner]

You could use a formula like this in I1, dragged down.

=SUMPRODUCT(--($C$1:$C$4=H1), $E$1:$E$4, $F$1:$F$4)/SUMPRODUCT(--($C$1:$C$4=H1), $E$1:$E$4)







<table border="1" cellspacing="0"><tbody><tr align="center" bgcolor="#a0a0a0"><td width="25"> </td><td width="25">A</td><td width="25">B</td><td width="25">C</td><td width="25">D</td><td width="25">E</td><td width="25">F</td><td width="25">G</td><td width="25">H</td><td width="25">I</td></tr><tr><td align="center" bgcolor="#a0a0a0">1</td><td align="left" bgcolor="#ffffff">A</td><td align="right" bgcolor="#ffffff">1999</td><td align="left" bgcolor="#ffffff">H1</td><td align="left" bgcolor="#ffffff">Q1</td><td align="right" bgcolor="#ffffff">5</td><td align="right" bgcolor="#ffffff">200</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">H1</td><td align="right" bgcolor="#ffffff">350</td></tr><tr><td align="center" bgcolor="#a0a0a0">2</td><td align="left" bgcolor="#ffffff">A</td><td align="right" bgcolor="#ffffff">1999</td><td align="left" bgcolor="#ffffff">H1</td><td align="left" bgcolor="#ffffff">Q2</td><td align="right" bgcolor="#ffffff">15</td><td align="right" bgcolor="#ffffff">400</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">H2</td><td align="right" bgcolor="#ffffff">687.5</td></tr><tr><td align="center" bgcolor="#a0a0a0">3</td><td align="left" bgcolor="#ffffff">A</td><td align="right" bgcolor="#ffffff">1999</td><td align="left" bgcolor="#ffffff">H2</td><td align="left" bgcolor="#ffffff">Q3</td><td align="right" bgcolor="#ffffff">6</td><td align="right" bgcolor="#ffffff">500</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">4</td><td align="left" bgcolor="#ffffff">A</td><td align="right" bgcolor="#ffffff">1999</td><td align="left" bgcolor="#ffffff">H2</td><td align="left" bgcolor="#ffffff">Q4</td><td align="right" bgcolor="#ffffff">10</td><td align="right" bgcolor="#ffffff">800</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr></tbody></table>

http://www.mrexcel.com/forum/member.php?u=73026
Thanks mikerickson! I copied the formula at pasted it at I1, but it didn't turn our correct number, it appeared something like "#DIV/", which means it has error.

By the way, my data has not only 4 rows, it has thousand rows. Some years don't have all 4 quarters, let say, year 2011, it only has Q1, Q2 and Q3, but we still need to retrieve the average price for H1 and H2. So in this case, the average price for H2 will be the same as the average price for Q3.

Can the formula you provided does this?

thanks a bunch!

 

[mikerickson]

Are you also putting the string "H1" in cell H1?

In your multi-year situation, my formula should return the average for all H1, not just 1999 H1.

Is that what you want?