Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jun 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hallo

    The attached sheet has projects & weights

    plus completion

    How can I calculate the completion as per the weight?

    Thanks

    Adam
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In d3:
    =C3*B3/$B$10

    Copy D3 to D49

    You can also eliminate the intermediate column if desired and calculate the weighted completion average as [currently in D10 as =SUM(D39)]:
    =SUMPRODUCT(B3:B9,C3:C9)/SUM(B3:B9)

    Steve

  3. #3
    Lounger
    Join Date
    Jun 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Steve

  4. #4
    Lounger
    Join Date
    Jun 2009
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Attached is the updated version. I wonder if the Weighted average is correct?

    Also, the numbers look somewhat abstract and meaningless - is there something we can do to make it more meaningful?

    Thanks in advance - Adam
    Attached Files Attached Files

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by adamsnokia View Post
    Attached is the updated version. I wonder if the Weighted average is correct?

    Also, the numbers look somewhat abstract and meaningless - is there something we can do to make it more meaningful?
    On the first point, notice that the sum of D39 equals D10, so it looks right in context. (Just in case: if column B is actually percentage and should always add to 100%, you don't strictly need the "/SUM(B3:B9)" part of the formula in cell D10 - it's dividing by 1, which as we know...)

    As to your second question, formatting is in the eye of the beholder, but it appears that all column D values should be formatted as percentages, and you could format the Borders on row 10 to be show that this is the total section; with no borders at all that row looks like scratch pad calculations rather than a presumably important Total/Average row. And if Column B is percentages, format them that way.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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