+ Reply to Thread
Results 1 to 5 of 5

2 part question on averaging

  1. #1
    Geo
    Guest

    2 part question on averaging

    Hi,
    I have the formula =SUM(G34:V34)/COUNTIF(G34:V34,"<>0") Which does a great
    job averaging the data as long as there is a numeric value in one of the
    cells. However, if there are no numbers I get a div/o error. Is there a fix
    to that?
    Question 2; Is there a way to average say the 5 highest values in the
    G34:V34 row?
    --
    Geo

  2. #2
    Bob Phillips
    Guest

    Re: 2 part question on averaging

    =IF(SUM(G34:V34)=0,"",SUM(G34:V34)/COUNTIF(G34:V34,"<>0"))

    and

    =AVERAGE(LARGE(G34:V34,{1,2,3,4,5}))

    --
    HTH

    -------

    Bob Phillips
    "Geo" <Geo@discussions.microsoft.com> wrote in message
    news:B2C406C4-229A-47AC-A3EA-300AEC3CF381@microsoft.com...
    > Hi,
    > I have the formula =SUM(G34:V34)/COUNTIF(G34:V34,"<>0") Which does a great
    > job averaging the data as long as there is a numeric value in one of the
    > cells. However, if there are no numbers I get a div/o error. Is there a

    fix
    > to that?
    > Question 2; Is there a way to average say the 5 highest values in the
    > G34:V34 row?
    > --
    > Geo




  3. #3
    Ron Rosenfeld
    Guest

    Re: 2 part question on averaging

    On Sun, 2 Jan 2005 08:47:02 -0800, Geo <Geo@discussions.microsoft.com> wrote:

    >Hi,
    >I have the formula =SUM(G34:V34)/COUNTIF(G34:V34,"<>0") Which does a great
    >job averaging the data as long as there is a numeric value in one of the
    >cells. However, if there are no numbers I get a div/o error. Is there a fix
    >to that?


    =IF(COUNT(rng)=0,"",AVERAGE(rng))

    where rng is replaced with G34:V34

    >Question 2; Is there a way to average say the 5 highest values in the
    >G34:V34 row?


    The array-entered formula:

    =IF(COUNT(rng)=0,"",AVERAGE(LARGE(rng,ROW(INDIRECT("1:"&MIN(5,COUNT(rng)))))))

    To array-enter a formula, hold down <ctrl><shift> while hitting <enter>. Excel
    will place braces {...} around the formula.


    --ron

  4. #4
    Aladin Akyurek
    Guest

    Re: 2 part question on averaging

    1]

    =SUM(G34:V34)/MAX(1,COUNT(G34:V34)-COUNTIF(G34:V34,0))

    2]

    =IF(COUNT(G34:V34)>5,AVERAGE(LARGE(G34:V34,{1,2,3,4,5})),AVERAGE(G34:V34))

    Geo wrote:
    > Hi,
    > I have the formula =SUM(G34:V34)/COUNTIF(G34:V34,"<>0") Which does a great
    > job averaging the data as long as there is a numeric value in one of the
    > cells. However, if there are no numbers I get a div/o error. Is there a fix
    > to that?
    > Question 2; Is there a way to average say the 5 highest values in the
    > G34:V34 row?


  5. #5
    Harlan Grove
    Guest

    Re: 2 part question on averaging

    "Aladin Akyurek" <akyurek@xs4all.nl> wrote...
    >1]
    >
    >=SUM(G34:V34)/MAX(1,COUNT(G34:V34)-COUNTIF(G34:V34,0))


    Fine if values in the range would only be nonnegative. If values could be
    negative as well as positive, then they almost certainly could be zero as
    well. In other words, it should be sufficient to average only the positive
    values. Also, if there were no numbers at all in the range, should the
    result be 0?

    An alternative.

    =IF(COUNTIF(A1:A10,">0"),AVERAGE(IF(A1:A10>0,A1:A10)),"")


    >2]
    >
    >=IF(COUNT(G34:V34)>5,AVERAGE(LARGE(G34:V34,{1,2,3,4,5})),AVERAGE(G34:V34))


    Returns #DIV/0! if there are no numbers in range. An array formula
    alternative.

    =IF(COUNT(A1:A10),AVERAGE(LARGE(A1:A10,
    ROW(INDIRECT("1:"&MIN(COUNT(A1:A10),5))))),"")



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1