Hello. I've attached a sample workseet with data for invoices and then the cost. I would like to take a weighted average of the cost for each quarter? How would I do this? Various methods welcome. Thanks.
Hello. I've attached a sample workseet with data for invoices and then the cost. I would like to take a weighted average of the cost for each quarter? How would I do this? Various methods welcome. Thanks.
what are you basing the weight on...
and what are you trying to achieve with this?
=MONTH(B6)
B6 is the number of invoices (I presume?) for a cerat "whatever"?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
The weigths are based on the number of invoices (yes, B6). So, for each quarter I would like to have the weighted average using a sumif for each quarter. Thank ye kindly.
I assume this is related to your question here
I'd propose a similar formula to the one I suggested there, i.e.
=SUMPRODUCT(($B$2:$M$2=O2)*($B$4:$M$4=O4),$B$6:$M$6,$B$7:$M$7)/SUMIFS($B$7:$M$7,$B$2:$M$2,O2,$B$4:$M$4,O4)
Audere est facere
I'm going to try to use that formula but would it be possible to use a different type of formula instead of sumproduct? Maybe a sumif or something like that.
There's no significantly better alternative - SUMIFS can't be used because you need to multiply two ranges (unless you are prepared to have a helper row which multiplies rows 6 and 7). An "array formula" will do it but to no real advantage, i.e.
=SUM(IF(($B$2:$M$2=O2)*($B$4:$M$4=O4),$B$6:$M$6*$B$7:$M$7))/SUMIFS($B$7:$M$7,$B$2:$M$2,O2,$B$4:$M$4,O4)
confirmed with CTRL+SHIFT+ENTER
or with row 8 used as a helper, i.e. in B8 copied across
=B6*B7
you can use SUMIFS like
=SUMIFS($B$8:$M$8,$B$2:$M$2,O2,$B$4:$M$4,O4)/SUMIFS($B$7:$M$7,$B$2:$M$2,O2,$B$4:$M$4,O4)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks