+ Reply to Thread
Results 1 to 8 of 8

Need help with a weighed average function please :)

Hybrid View

JCarollo765 Need help with a weighed... 05-01-2014, 03:01 PM
zbor Re: Need help with a weighed... 05-01-2014, 03:08 PM
prjt Re: Need help with a weighed... 05-01-2014, 03:14 PM
hemesh Re: Need help with a weighed... 05-01-2014, 03:15 PM
JCarollo765 Re: Need help with a weighed... 05-01-2014, 03:37 PM
zbor Re: Need help with a weighed... 05-01-2014, 03:49 PM
JCarollo765 Re: Need help with a weighed... 05-01-2014, 03:52 PM
zbor Re: Need help with a weighed... 05-01-2014, 04:27 PM
  1. #1
    Registered User
    Join Date
    04-22-2014
    Location
    San Diego
    MS-Off Ver
    Excel 2010
    Posts
    36

    Question Need help with a weighed average function please :)

    I would like to get the weighted average of a person based on the department they have worked in. Then another problem is a person will have more then one entry in the same department. Is it possible to combine the weighted average of that person? I have attached an example of what I'm working with. Thank you for any help!!!
    Attached Files Attached Files
    Last edited by JCarollo765; 05-01-2014 at 03:53 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,072

    Re: Need help with a weighed average function please :)

    Try Pivot table
    Attached Files Attached Files
    Never use Merged Cells in Excel

  3. #3
    Registered User
    Join Date
    04-03-2014
    Location
    Canada
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    75

    Re: Need help with a weighed average function please :)

    For your reference, I don't think this is a weighted average; it's just a percentage...

    The following is an example to calculate the percentage for Bob in department 1. For other people and departments obviously change "Bob" to the new name and "1" to what ever the department number.

    =SUMIFS($C$2:$C$15,$A$2:$A$15,"Bob",$B$2:$B$15,"1")/SUMIF($A$2:$A$15,"Bob",$C$2:$C$15)

    Pivot table also a good idea

  4. #4
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Need help with a weighed average function please :)

    or you could use in C21
    =SUMIFS($C$2:$C$15,$A$2:$A$15,$A21,$B$2:$B$15,$B21)/SUMIF($A$2:$A$15,$A21&" Total",$C$2:$C$15)
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  5. #5
    Registered User
    Join Date
    04-22-2014
    Location
    San Diego
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Need help with a weighed average function please :)

    Thank you it does look like the pivot table is the way to go. If I dont have the weighted average column calculated, its there a way I can have my pivot table do that for me?

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,072

    Re: Need help with a weighed average function please :)

    You can create it as
    Formula: copy to clipboard
    =C2/SUMIF($A$2:$A$15,A2,$C$2:$C$15)
    and then you can remove Bob Total from your original table.

  7. #7
    Registered User
    Join Date
    04-22-2014
    Location
    San Diego
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Need help with a weighed average function please :)

    Thank you so much zbor!!!!!!!!!!!!!

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,072

    Re: Need help with a weighed average function please :)

    You can also add to your first table like this (then you don't need Pivot)
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Weighed Average
    By 0-0 Wai Wai ^-^ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-07-2005, 11:10 AM
  2. Error Handling #N/A with AVERAGE Function - Average of values in Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  3. Error Handling #N/A with AVERAGE Function - Average of values in Row
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  4. Weighed Average of a weiged average when there are blanks
    By krl - ExcelForums.com in forum Excel General
    Replies: 1
    Last Post: 07-06-2005, 03:05 PM
  5. Weighed average if the adjecent column contains blanks
    By Kasimir Lehväsl in forum Excel General
    Replies: 2
    Last Post: 07-04-2005, 05:12 PM

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