Average a row of data without zeros
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Average a row of data without zeros

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

    Default

     
    My sheet has 4 rows of data and a total row that is supposed to average the results over a week. example:
    week1 100
    week2 125
    week3 140
    week4 98
    Total =Average(A1:a4)
    Problem is if week 2,3 and 4 are empty or my user unknowingly enters a zero the average is incorrect. What I actually want to do is say if week1 is not empty or greater than 0 the average = week1. If week1 and week2 are not empty or greater than 0 then average week1 and week2. Is this possible?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    Board Regular
    Join Date
    Sep 2002
    Location
    Simsbury CT USA
    Posts
    1,551
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this


    ******** ******************** ************************************************************************>
    Microsoft Excel - Book4___Running: xl2002 XP : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    Q
    15
    16
    1001001001001001001000100100100100100
    17
    18
    Sheet1

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    You need to entry this formular using Control/Shift/Enter keys

    pll

    [ This Message was edited by: plettieri on 2002-10-18 13:59 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Another formula option would be
    =SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0"))

    which should work about the same as the array
    =AVERAGE(IF(A1:A4,A1:A4))
    with your data, except no error message if no entries are made in A1:a4

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