Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Average 'Overload' (Excel XP)

    I need help with a formula that will return an average from 48 ranges...

    On a sheet, I enter percentages (98.56%, 88.75%, ...) into a chart. Because the chart contains other data like text and dates, the percentages that need to be averaged are somewhat scattered.

    As a simple example, how would you obtain an average of the percentages in cells: C3:C7, D37, E3:E7, C11:C15, D1115, E11:E15 ? Then imagine, instead of 6 individual ranges, there's 48...

    To complicate matters, some of the 48 ranges may be blank. The chart is updated with new entries as the year progresses. I tried selecting all the cells in the various ranges and then applying a name "yuck". Thought I could just do =Average(yuck) but the range name didn't seem to stick to a non-contigious range of cells...

    Probably easier than I'm making it. If it helps, I've attached the sheet in question. Looking for a grand total average for cell C64...
    Thanks.
    - Ricky

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average 'Overload' (Excel XP)

    <pre>=AVERAGE(C3:E7, C11:E15)
    </pre>

    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Average 'Overload' (Excel XP)

    Thanks Legare...
    I guess my example was a bit simplistic and your response certainly works. I should have noted that the actual data to be averaged is interrupted by columns of dates. Or, will the <font color=blue>=AVERAGE</font color=blue> accept 48 separate ranges as its argument?

    Seems like I tried this and it didn't work out...
    - Ricky

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average 'Overload' (Excel XP)

    I know you won't be pleased with my comments, but here goes anyhow:

    You would benefit from changing the setup of the data, by having a simple table with all percentages, places and dates below each other. You can then use a pivot table to create nicely looking summary results from your data AND very easily compute (sub)totals and averages based on diverse criteria.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Average 'Overload' (Excel XP)

    Your comments make perfect sense and I appreciate them. But, and there's always a 'but". The document is updated as new scores come in and then the spreadsheet is converted to a pdf and emailed to an audience that doesn't have Excel on their systems. Only email and a few proprietary programs necessary for running their business.

    Also, the pdf is then printed at the location and posted on an employee bulletin board.

    As much as a pivot table would clean this mess up, in this case it wouldn't serve the end-user. Still, I'm very interested in making format changes to the form if it will accomplish two things: 1) Keep it simple to maintain and 2) produce a concise one page printed report that shows individual scores and dates, monthly averages, the annual average and the overall team average.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  6. #6
    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

    Re: Average 'Overload' (Excel XP)

    I second Jan's suggestion. You could still keep a sheet that looks like what you have, just put the "raw data" in a more "structured" format. You could create a macro to take the data and transform it into the structure you have (or use a pivot table).

    If your data was more "consistent" and you always had 3 or always 4 rows, you could just average the "row averages" to get the overall average, but with some being based on 3 and some on four, you will give more "weight" to the months with lesser days.

    Steve

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average 'Overload' (Excel XP)

    The limit on the number of ranges you can specify for the AVERAGE() function is 30. If the ranges were as you specified in the first post, then combining the the three contiguous ranges into one would have dropped the number of ranges down to 16. If you must average 48 ranges, then the only choice I can think of is a formula like the one below:

    <pre>=(SUM(range1)+SUM(range2)+SUM(range3)+...+SUM (Range48))/number-of-values
    </pre>


    You might be able to write a User Defined Function that you could pass the Range that contains all of the cells if there is a way for the function to figure out which ones should be in the average.
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Average 'Overload' (Excel XP)

    Using your example:
    <pre>=(SUM(range1)+SUM(range2)+SUM(range3)+...+SUM (Range48))/number-of-values</pre>



    Can /number-of-values be determined by =countif((range1, range2, range3...range48),">0.01%")? Or, is the countif function also limited to 30 ranges? I looked in Excel help for each of the functions and could not find the limitations documented...

    Thanks
    - Ricky

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average 'Overload' (Excel XP)

    You could quite simply have formulas in the pretty sheet that show the percentages AND then very easily compute the averages from a simple table.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    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

    Re: Average 'Overload' (Excel XP)

    How about this approach:
    If you change your "summary averages" to text (eg in C10)
    <pre>=IF(ISERROR(AVERAGE(C6:C9)),"",TEXT(AVERAGE(C 6:C9),"0.00%"))</pre>


    They will display correctly but not be a number.

    Then in the "Grand average" you could use (eg in C63)
    <pre>=SUMIF(C661,"<=1")/COUNTIF(C661,"<=1")</pre>


    Which will only sum and count the percentages (which should be <=1) and ignore the "text- summary averages" and the dates. If your averages could be >1 then use a different number, dates are about 38,000.

    Steve

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Average 'Overload' (Excel XP)

    Thanks, I like it! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average 'Overload' (Excel XP)

    COUNTIF has only one range parameter. Therefore, it can only count in a single range.

    Another possibility. Create a new sheet and in a contiguous range on that sheet put references to the cells that you want to average. Then use the AVERAGE function on that one contiguous range.
    Legare Coleman

  13. #13
    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

    Re: Average 'Overload' (Excel XP)

    Actually on further thought, you don't need the sumif/countif. This should work directly":

    <pre>=average(C6:c61)</pre>


    once you get rid of the monthly avgs as values and turn them into text. You don't need to have the dates even in the ranges! You could use that formula if you wanted an "overall average":
    <pre>=SUMIF(C6:J61,"<=1")/COUNTIF(C6:J61,"<=1")</pre>

    So you ignore the dates.

    Remeber both require that formula to convert monthly averages into text (so they are ignored).

    Steve

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average 'Overload' (Excel XP)

    Of course she can also use

    =SUBTOTAL(1,C6:C9) in cell C10 (etcetera)

    and

    =SUBTOTAL(1,C661)

    in C63.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    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

    Re: Average 'Overload' (Excel XP)

    If you use use intermediate Subtotals in C10, etc, you will be including those averages of the averages in the "overall average" which will give you erroneous results, since some are avg so 3 and sum are avgs of 4. The "Key" was turning the "intermediate avg into "text" so they would not be included in the average.

    Steve

Page 1 of 2 12 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
  •