Results 1 to 4 of 4

Thread: AVERAGE (2000)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    AVERAGE (2000)

    Is it true that the AVERAGE function will only average 30 numbers? If so, what to do if you have >30?

  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 (2000)

    The Average function is limited to 30 arguments. However, each argument can be an array. Therefore, =Average(A1:A1000) will average 1000 numbers. What this does mean is that if your numbers are in non contiguous cells (=Average(A1,A3,A4,A4)), you will be limited to 30 values. So, what you can do is design your sheet so that you can average arrays of values. If that is not possible, then you will have to write formulas to do calculate the average without using the average function.
    Legare Coleman

  3. #3
    BAM
    Guest

    Re: AVERAGE (2000)

    Hi jlkirk,

    It's true that the AVERAGE function, as the majority of the common functions, allow up to 30 arguments in the function.

    If you are typing each number yourself such as =Average(20,30,40,10,5,15) then "Yes" it is limited to averaging 30 numbers for each number is an argument. In the above example there are 6 arguments used.

    However if you place the numbers in your worksheet (the preferred method), starting at say A3 then the function would read: =Average(A3:A8). By using a range of cells you use a single argument.

    Given that a worksheet has 256 columns and 65,536 rows, I'd say the maximum number of cells that could be used in the Average function in merely a single worksheet would be: 16,777,215.

    I hope that answers your question. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    ~~~~~~~~~~~~~
    Cheers!

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

    Re: AVERAGE (2000)

    Actually, if you are typing the numbers yourself, then you can enter them as an array, and you can have up to 30 arrays. This formula works fine on my Excel to average 40 numbers:

    <pre>=AVERAGE(0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7, 8,9,{0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9})
    </pre>


    I think that the only time the limit of 30 parameters causes a problem is if you have numbers in more than 30 non-contiguous cells.
    Legare Coleman

Posting Permissions

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