AVERAGE EXCLUDING SOME CELLS
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: AVERAGE EXCLUDING SOME CELLS

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

    Default

     
    How do I write a formula to average about 10 specific cells but I need to exclude any 0's from the average?
    Example: Average cells A1, C1, D3, F4, G5 but if any one of those is a zero I don't want it to be included because it will skew the average.
    Thanks!

  2. #2
    MrExcel MVP Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In the cell where you want the average to be, type =AVERAGE(

    Then, hold down control and click on all of the cells you wish to average. You can leave out any cells you want.

    When you have all of the cells selected, close your parenthesis (add the ")" to the end) and press enter.
    Kristy

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

    Default

    Thanks. It's a varying sheet though, meaning some days there will be a zero in the cell and other days the same cells will have a value. I need something that can dynamiclly change to exclude any zeros that may be in the cells. Any ideas on how to exclude zeros? Again, thanks.

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

    On 2002-03-15 08:30, Q45 wrote:
    How do I write a formula to average about 10 specific cells but I need to exclude any 0's from the average?
    Example: Average cells A1, C1, D3, F4, G5 but if any one of those is a zero I don't want it to be included because it will skew the average.
    Thanks!
    In an unused column (e.g., H) enter a reference to each of these cells (e.g., =A1 in H1, =C1 in H2, =D3 in H3, etc.), hide the column (if desired), and use the following array formula...

    {=AVERAGE(IF(H1:H5,H1:H5))}

    Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.


    [ This Message was edited by: Mark W. on 2002-03-15 09:40 ]

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have completed the formula exactly as you stated and excel enclosed the formula with the {} to recognize the properly entered array formula. However, the value of the cell gives me the #VALUE! message. Any suggestions? Thank you!

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Q,

    is the range size and location static, just some could be 0 and some could have numbers in them ?

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

    Default

    On 2002-03-15 15:29, Chris Davison wrote:
    Q,

    is the range size and location static, just some could be 0 and some could have numbers in them ?
    Chris: I think he has formula returned blanks in some of the cells of interest.

    After creating a range of consecutive cells, would

    =SUM(H1:H5)/MAX(1,COUNTIF(H1:H5,">0")+COUNTIF(H1:H5,"<0"))

    do the job.

    Aladin

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-15 15:37, Aladin Akyurek wrote:
    Chris: I think he has formula returned blanks in some of the cells of interest.
    yeah, trying to use a named range defeated me (!) Question thusly : with a named range of non-contiguous cells (say "table") why does =sum(table) give a number, say 60, but =countif(table,">0") gives #VALUE error ?

    Forever curious.....



    [ This Message was edited by: Chris Davison on 2002-03-15 15:54 ]

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

    Default

    On 2002-03-15 15:53, Chris Davison wrote:
    On 2002-03-15 15:37, Aladin Akyurek wrote:
    Chris: I think he has formula returned blanks in some of the cells of interest.
    yeah, trying to use a named range defeated me (!) Question thusly : with a named range of non-contiguous cells (say "table") why does =sum(table) give a number, say 60, but =countif(table,">0") gives #VALUE error ?

    Forever curious.....

    [img]/board/images/smiles/icon_smile.gif[/img]

    [ This Message was edited by: Chris Davison on 2002-03-15 15:54 ]
    I suppose the syntax difference explains the trouble:

    SUM([multi-cell-range]*,[single-cell-range]*)

    COUNTIF(a-multi-or-single-cell-range,condition)

    A named range of non-contiguous cells (say "table") used in =sum(table) will give a number, because the underlying syntax supports it, while that of countif does not. Names thus does not add an enrichment to the syntax the functions require. I'd say that's just right.

    =countif(table,">0") should give indeed a #VALUE error, indicating that the function is fed with a range arg which it does not support.

    Aladin

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    thanks,

    a subtle, albeit unfortunate syntax difference !

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