Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: average formula without getting div/0 error...????

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    dallas tx
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i have a column of numbers aht (average handle time) in seconds, and it is listed per
    days of the week... for each of my agents, is there a formula to tkae the average and leave out the zero, if someone is absent..

    412
    300
    356
    435
    0

    =#div/0!.. i want to be able to use one formula and get the average no matter if a zero is present or not.. is it possible..??




  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you provide a bit more detail, including the formula you use to calculate your average? If there are other numbers you use in your formula, please include them also.

    Thank you.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    =SUM(range)/COUNTIF(range,">0")
    It's never too late to learn something new.

    Ricky

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-09 11:07, Escher wrote:
    i have a column of numbers aht (average handle time) in seconds, and it is listed per
    days of the week... for each of my agents, is there a formula to tkae the average and leave out the zero, if someone is absent..

    412
    300
    356
    435
    0

    =#div/0!.. i want to be able to use one formula and get the average no matter if a zero is present or not.. is it possible..??
    =SUM(A1:A5)/MAX(1,COUNT(A1:A5)-COUNTIF(A1:A5,0))

    Aladin

    A personal note: I take it IML will notice this.


  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    dallas tx
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =SUM(H72,H149,H222,H294,H330)/MAX(1,COUNT(H72,H149,H222,H294,H330)-COUNTIF(H72,H149,H222,H294,H330,0))

    is this right?????

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

    {=AVERAGE(IF(range,range))}

    where "range" is a cell range

    Note: This is an array formula which 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.

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    dallas tx
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok i am confused now...

    i am not to familiar with big formulas as you can tell...

  8. #8
    New Member
    Join Date
    Apr 2002
    Location
    dallas tx
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    S.O.S?

    help??

    = )

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

    What's this...

    H72,H149,H222,H294,H330


    Aren't your numbers contiguous?

  10. #10
    New Member
    Join Date
    Apr 2002
    Location
    dallas tx
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    nope... those are my cells, that i want to get the average of... but one has a value of 0 (zero) so the average is wrong...

    values 412,300,356,435 and 0....

    i would like to find one formula that i would like to use on 30 different agents...
    with out going back and editing each forumla!





    [ This Message was edited by: Escher on 2002-04-09 12:16 ]

Some videos you may like

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
  •