+ Reply to Thread
Results 1 to 14 of 14

Excel 2007 : Average Function used to calculate percentage when category quantities change

  1. #1
    Registered User
    Join Date
    07-10-2012
    Location
    Mt. Prospect, IL
    MS-Off Ver
    Excel 2007
    Posts
    7

    Average Function used to calculate percentage when category quantities change

    Hi:

    I need to calculate a scoring sheet, to rate the performance of architects. The scoring sheet has five categories:

    Category 1 = 3 statements
    Category 2 = 11 statements
    Category 3 = 3 statements
    Category 4 = 4 statements
    Category 5 = 4 statements

    After rating each statement with a 1-5 rating, each category is summed (total points and percentage of points received) and then a percentage is given at the end to see where the architect rated.

    I initially started by creating a formula for each category, to get a percentage total for that category:

    First I totaled the points in each category, then I calculated the percentage based on those points. That gave me the percentage total for points scored for that category.

    Category 1
    to sum total score of 3 statements
    =SUM(H2:H5)
    to get percentage of three statements
    =SUM(H6/15)

    The final results were:

    Category 1 = 11 out of 15 (73%)
    Category 2 = 24 out of 55 (44%)
    Category 3 = 15 out of 15 (100%)
    Category 4 = 17 out of 20 (85%)
    Category 5 = 16 out of 20 (80%)
    Total = 76% (using the AVERAGE function)

    How do I calculate the total if the number of categories changes? There will be times when only 3 or 4 categories will be rated. If I use the same formula at the end of the spreadsheet to get the overall percentage scored, the total percentage will not be reflective of the actual score using the AVERAGE function. Let’s say I don’t need to score category 2:

    Category 1 = 11 (73%)
    Category 2 = 0 (0%)
    Category 3 = 15 (100%)
    Category 4 = 17 (85%)
    Category 5 = 16 (80%)
    Total = 68% (using the AVERAGE function for five categories)

    How do I tell the AVERAGE function that category 2 wasn’t used and shouldn’t be used to calculate the average? Using a revised AVERAGE function for 4 categories, the percentage goes up to 84.5%, which is the correct answer.

    Joe

  2. #2
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Average Function used to calculate percentage when category quantities change

    How do we know when a category should be excluded? Is it when the score is 0, or might an architect actually score 0 on a category? If it's the first, then you can use =AVERAGEIF() on your original data, if it's the second, then you need to state somewhere in your workbook that a given category is included; e.g. an extra column and then use =AVERAGEIF().
    Sincerely
    S?ren Larsen

    "Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"

  3. #3
    Registered User
    Join Date
    07-10-2012
    Location
    Mt. Prospect, IL
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Average Function used to calculate percentage when category quantities change

    Thanks Søren!

    The architect may score a zero in any given category (not likely, but it could happen) in which case I would expect zero's to be entered and for that to affect their overall score. For instance, I entered zeros in category 2 and the result was that the overall percentage changed to 68%. But if a category needs to be removed from the scorecard (because the architet wasn't incolved in Interior Design, for example, in which case, we don't want to rate them in that category), then what do I need to do to the formula? Where I currently have =AVERAGE add IF to the end?

    This is what my current =AVERAGE fundtion looks like:

    =AVERAGE(J13,J27,J33,J40,J47)

    It totals and averages the percentages of each category. J27 is category 2, which currently shows all zeros. If I want to instead exclude that category, what do I need to do?

    I attached the worksheet so you can see it.

    Joe
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Average Function used to calculate percentage when category quantities change

    I assume that you want this to happen automatically, in which case you need to "make Excel aware" of that. You could, e.g., write "Include"/"Exclude" in cells B13, B27 and so on. In that case you can use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-10-2012
    Location
    Mt. Prospect, IL
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Average Function used to calculate percentage when category quantities change

    Thanks, but I feel as if I'm getting even more confused! not your fault, just that I don't hang out in Excel as often as I would like to.

    I would like it to happen automatically; my intent would be to include a note on the form that if a category is not relevent to the review, then nothing should be entered, or maybe NA should be entered, whatever trigger is needed so Excel would know that a particular category shouldn't be included in the sum/average formula at the end.

    As far as the formula you included is concerned, where do I paste it? In J49, the percentage cell that sums and averages all category percentages? and when you say I could write 'include/exclude' in cells B13, B27, etc are you saying the user would have to type 'include' or 'exlude' on each line contained in a category that is included or excluded?

    Sorry for the questions...

    Joe

  6. #6
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Average Function used to calculate percentage when category quantities change

    Quote Originally Posted by josuff36 View Post
    I would like it to happen automatically; my intent would be to include a note on the form that if a category is not relevent to the review, then nothing should be entered, or maybe NA should be entered, whatever trigger is needed so Excel would know that a particular category shouldn't be included in the sum/average formula at the end.
    It doesn't matter how many questions you have, as long as we get you where you want to go! Where would you input "NA"?

  7. #7
    Registered User
    Join Date
    07-10-2012
    Location
    Mt. Prospect, IL
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Average Function used to calculate percentage when category quantities change

    It doesn't need to be NA, I just thought that, for the category(s) that were going to be skipped, some form of text would be needed in each line item so Excel knew not to include that category. If each line were left blank, is there a formula to let Excel know to skip that category?

    And thanks again for your help!

    Joe

  8. #8
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Average Function used to calculate percentage when category quantities change

    Here's an example of how you could do it:
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-10-2012
    Location
    Mt. Prospect, IL
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Average Function used to calculate percentage when category quantities change

    Thanks Søren, but I'm not sure that'll work.

    With category 2 blank (as if that category was not needed as part of the scoring), the percentage is correct (85%: 73% + 100% + 85% + 80% /4 = 84.5%). But if category 2 is not excluded, how do I get the total for that category to be included in the overall percentage total currently shown as 85%? I entered random numbers to get a percentage subtotal to appear in that category, but no matter which numbers I entered, they did not factor into the grand total percentage of 85%.

    What I need is for Excel to know when a categeoy is excluded by the user, then change the way it calculates the final percentage. If all 5 categories are used, then it should total the five subtotal percentages and divide by 5. If only three categories are used, it should add those three subtotal percentages and divide by three, ignoring the two that were excluded.

    Is there a formula with enough sophistication to do that?

  10. #10
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Average Function used to calculate percentage when category quantities change

    Whether a category is included or not is controlled from the cells B13, B27, B33, B40 and B47 respectively. At the moment (in my attachment) B27 says "Exclude", hence the category "Programming and Design" is excluded from calculation. If you change "Exclude" to "Include", then the category is included in calculation. The same goes for B13, B33, B40 and B47.

  11. #11
    Registered User
    Join Date
    07-10-2012
    Location
    Mt. Prospect, IL
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Average Function used to calculate percentage when category quantities change

    The worksheet will be given to all project managers, to rate the architects they work with. The worksheet needs to be as easy as possible for all to use, or no one will use it. Half of the PMs don't even know what a formula is and it would be an uphill battle to get them to revise one.

    As unfortunate as it is, I work with a lot of old-school coworkers who are not big fans of doing more than they need to when it comes to technology.

    Is there a way to exclude a cell/cells if it's left blank? So if Excel saw zeros it would know to add those to the overall total but if it was blank it would know that the category should be skipped?

  12. #12
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Average Function used to calculate percentage when category quantities change

    Yes, the attached functions completely automatic; if all subjects in a category is left blank, then it's excluded from calculation.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-10-2012
    Location
    Mt. Prospect, IL
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Average Function used to calculate percentage when category quantities change

    That's it, that's exactly what I needed! Thanks Søren, I really appreciate your help with this! If you're ever in Chicago, let me know and I'll buy you a hot dog!

  14. #14
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Average Function used to calculate percentage when category quantities change

    Thanks; maybe I will!

+ 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