+ Reply to Thread
Results 1 to 6 of 6

sumifs for weighted average

  1. #1
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    sumifs for weighted average

    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.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,186

    Re: sumifs for weighted average

    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

  3. #3
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    Re: sumifs for weighted average

    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.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: sumifs for weighted average

    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

  5. #5
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    Re: sumifs for weighted average

    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.

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: sumifs for weighted average

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1