coldsteel404
New Member
- Joined
- Nov 12, 2014
- Messages
- 16
I use excel 2007
Hi,
I'm trying to do the following. I have 3 colums, one with the year, one with a quantity and one with prices
What I want to do is to calculate the average price of the year 2012; 2013, 2014 whilst taking the quantity into acount.
<tbody>
</tbody>
<tbody>
</tbody>
I can count the average price without taking the quantity into acount with =AVERAGE.IF($B$2:$B$13;G14;E2:$E$13)
I can also use SUMPRODUCT to calculate the matrixes for the total of money spend =SOMPRODUCT(D2:D13;E2:E13)
But I can't seem to combine them, let allone use the data as a criteria for if.
So, I hope I made myself somewhat clear (English isn't my native language so explaining Excelfunctions is rather challeging
I think I want something like this:
=AVERAGE.IF($B$2:$B$13;F14;SUMPRODUCT($C$2:$C$13;$D$2:$D$13))but without the message "error" :P
Thanks in adance,
Hi,
I'm trying to do the following. I have 3 colums, one with the year, one with a quantity and one with prices
What I want to do is to calculate the average price of the year 2012; 2013, 2014 whilst taking the quantity into acount.
2012 | 92 | € 156,00 |
2012 | 37 | € 250,00 |
2012 | 12 | € 500,00 |
<tbody>
</tbody>
2013 | 98 | € 156,00 |
2013 | 30 | € 250,00 |
2013 | 11 | € 1.000,00 |
<tbody>
</tbody>
I can count the average price without taking the quantity into acount with =AVERAGE.IF($B$2:$B$13;G14;E2:$E$13)
I can also use SUMPRODUCT to calculate the matrixes for the total of money spend =SOMPRODUCT(D2:D13;E2:E13)
But I can't seem to combine them, let allone use the data as a criteria for if.
So, I hope I made myself somewhat clear (English isn't my native language so explaining Excelfunctions is rather challeging
I think I want something like this:
=AVERAGE.IF($B$2:$B$13;F14;SUMPRODUCT($C$2:$C$13;$D$2:$D$13))but without the message "error" :P
Thanks in adance,
Last edited: