formula Average
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: formula Average

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Netherlands
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    If got an excelsheet with 7 different numbers in 7 cells in each cell one number.
    If I want to know the average i'll just use the formula, and all the numbers wil be added and divided by 7 because of the 7 cells, sofar no problem, but I need to keep that range of 7 cells, but sometimes the will only be 5 cells filled withe numbers, an than I still need to know the average of those numbers. But now it has to divid by 5.
    How can i create a formula that will adapt to the number of filled cells.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,868
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-25 10:50, Marky wrote:
    If got an excelsheet with 7 different numbers in 7 cells in each cell one number.
    If I want to know the average i'll just use the formula, and all the numbers wil be added and divided by 7 because of the 7 cells, sofar no problem, but I need to keep that range of 7 cells, but sometimes the will only be 5 cells filled withe numbers, an than I still need to know the average of those numbers. But now it has to divid by 5.
    How can i create a formula that will adapt to the number of filled cells.
    How about:

    =SUM(A1:A7)/MAX(1,COUNT(A1:A7))

    where A1:A7 is the target range (adapt to suit).


  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use =AVERAGE(A1:A7) and be done with it!!!
    AVERAGE ignores empty cells.


    [ This Message was edited by: Mark W. on 2002-02-25 11:01 ]

  4. #4
    Guest

    Default

      
    =SUM(A1:A7)/(7-COUNTBLANK(A1:A7))

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com