Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    669
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Get average of percentages

    I've got a spreadsheet with about 100 cells that each have a percentage. I'm trying to have one cell that shows the total or average of each of those cells. What it's doing is adding them all up and giving a total of like 193% when adding two of the cells that have 95% and 98%. What am I doing wrong?

    P.S. I had been using pluses, but if I use a , inbetween the cell formulas, it will max out and say"more arguments have been specified than are allowed in this current file format."
    Last edited by kzkz; 2012-09-21 at 14:54. Reason: Update

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,486
    Thanks
    211
    Thanked 850 Times in 782 Posts
    Kzkz,

    If you have 95% in A1 and 98% in A2 then put =Average(A1:A2) in A3 to get the average. I would assume you are using a =SUM(A1:A2) or =A1+A2 to get the answer of 193%.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    669
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The problem too is that if I have two cells that have actual data, it works, but if I tell it to complete the average of the other cells that have zero then it lowers the result.
    Each of those cells that it's reading are coming from cells that have the following as they are producing a result of the average number. =IF(I16=0,0,SUM(I15-I16)/I15)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    If you use:
    =IF(I16=0,"",SUM(I15-I16)/I15)

    The average will ignore the text strings

    Steve

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    669
    Thanks
    1
    Thanked 0 Times in 0 Posts
    OK, but then it will result in the grant total average with #VALUE! if that cell is blank.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,486
    Thanks
    211
    Thanked 850 Times in 782 Posts
    Kzkz,

    Or maybe something like this -
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    669
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I can't use ranges since each of the cells are not right next to each other. Would that formula still work? What would that look like? Couldn't figure that one out. Thanks

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,486
    Thanks
    211
    Thanked 850 Times in 782 Posts
    Kzkz,

    Ok, how about this approach?
    Select all the cells you want to average (hold Ctrl and click on each).
    In the name box give it a name {MyPcts}.
    Use this formula: =SUM(MyPcts)/COUNT(MyPcts).

    Note: although my example has them all in a single column this method will work with any cells you select on this or even other sheets in the same workbook.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I don't understand, if a cell is blank it shouldn't trigger an error when calculating the average. Could you elaborate on your setup, perhaps attach an example file...

    Steve

  10. #10
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    2,854
    Thanks
    19
    Thanked 110 Times in 104 Posts
    Whereas it is quite possible to calculate a mean of a number of percentages, are you quite sure that what you are doing is actually a meaningful (no pun intended!) operation? That is, are these percentages based on similarly-sized fractions?

    To give a pathological example, suppose you were taking the mean of two percentages, 50% and 80%: answer 65%.
    But what if the two fractions were 1/2 (50%) and 200/250 (80%)?
    Really the mean should be (1+200)/(2+250) or 201/252 or 79.8%.
    BATcher

    Dear Diary, today the Hundred Years War started ...

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    The difference in the 2 calculations are the whether one wants the average percentage (which was the request) or a weighted average (which is the second. But your example presumes the "fraction" are both unreduced: that is to say Does 1/2 mean 1 out of 2, 3 out of 6, or even 125/250 that has been reduced...

    The average assumes the "fractions" are 50/100 and 80/100 which averages 65/100 no matter which way it is done.

    A mean (unweighted) average always assumes that each value is of equal likelihood. It is only when it is not that one would do a weighted average.But both are meaningful, they just mean different things....

    Steve
    Last edited by sdckapr; 2012-09-22 at 10:45.

  12. #12
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    669
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Steve, attached is a cut down version of the sample file. Maybe that will help.
    The totals I'm trying to get right are in columns M, N and O at the top.
    I think that changing each of the cells to being separated by a comma rather than plus would work but it only allows so many items and stops. Maybe a combination of the named field with the sumifs command?

    --KZ

  13. #13
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,350
    Thanks
    49
    Thanked 274 Times in 252 Posts

    VB solution

    In your average formula (M1 and N1), you summed all your values (to one number) then took an average of that sum. Of course, the average of a number is the number. Here is a code in VB that will do what you want to do. I have left it simple, and therefore not as efficient as could be, but just a functional. All text in green are comments to help you understand the flow. I have applied it to the attached revised spreadsheet with an update button. The code is located in a subroutine, PercentCalc(), in Module 1

    Dim num As Integer, PercentTotal, PercentAverage

    Public Sub PercentCalc()

    ' *******************
    ' * Code by Maudibe *
    ' * 9/22/12 *
    ' *******************
    '-----------------------------------------------------------------------------------------------

    ' % PROFITS
    num = 0 'number of values excluding zeros as the divisor (denominator)
    PercentTotal = 0 'running sum of percentages
    PercentAverage = 0 ' Sum of percentages divided by the number of acceptable values
    For I = 6 To 11 Step 5 'cylce between the 2 columns F and K where the Labor Hours are located
    For J = 9 To 438 Step 8 'cylce between the many rows where the Labor Hours are located
    PercentTotal = PercentTotal + Cells(J, I).Value 'Update running sum of percentage totals
    If Cells(J, I).Value <> 0 Then 'Checks if it is a legitimate entry for the divisor
    num = num + 1 'Divisor is incremented
    End If
    Next J
    Next I
    PercentAverage = PercentTotal / num 'Average is calculated
    Cells(1, 13).Value = PercentAverage 'Assign M1 Average Percent
    '------------------------------------------------------------------------------------------------
    ' % PARTS
    num = 0 'number of values excluding zeros as the divisor (denominator)
    PercentTotal = 0 'running sum of percentages
    PercentAverage = 0 ' Sum of percentages divided by the number of acceptable values
    For I = 6 To 11 Step 5 'cylce between the 2 columns F and K where the COGS are located
    For J = 8 To 437 Step 8 'Cyclce between the many rows where the COGS are located
    PercentTotal = PercentTotal + Cells(J, I).Value 'Update running sum of percentage totals
    If Cells(J, I).Value <> 0 Then 'Checks if it is a legitimate entry for the divisor
    num = num + 1 "Divisor is incremented
    End If
    Next J
    Next I
    PercentAverage = PercentTotal / num 'Average is calculated
    Cells(1, 14).Value = PercentAverage 'Assign N1 Average Percent
    End Sub


    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2012-09-22 at 16:48. Reason: added coded

  14. #14
    2 Star Lounger
    Join Date
    Jul 2012
    Posts
    102
    Thanks
    0
    Thanked 4 Times in 4 Posts
    What Steve is saying is vital to understand how averages work. Most calculations that used the average function assumed that you are entering a fraction of 1.

    What Excel and other applications will do when it formats your results as a percentage is it puts the % in, it is going the back end multiplication for you. If you are getting a result of 193%, there may be a whole number in your row or column of numbers somewhere. This is a common error (I do it all the time). You need to highlight the column of numbers and reformat them with number and decimal places. That way you can see if one of the values exceeds a fraction.

    The other portion to understand is that, depending on what you are doing, a blank cell may be included in the calculation of averages just as a cell with a 0 becomes part of the average calculation unless you turn this off. There is a difference between a blank cell and a cell with a null value.

    As a side point, there are studies by a number of individuals that criticized te ability of Microsoft Excel to calculate results correctly. Whenever I do a complicated spreadsheet, I usually check it with pocket calculator because I don't trust the stuff.

  15. #15
    5 Star Lounger
    Join Date
    May 2001
    Location
    Allen, Texas, USA
    Posts
    669
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I understand what you are saying. Did you take a look at my attached file? How do you turn off the calculations of a result of 0 in Excel?
    The cells are calculating correctly with full numbers.

Page 1 of 3 123 LastLast

Posting Permissions

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