Results 1 to 6 of 6

Thread: Average (2000)

  1. #1
    BakerMan
    Guest

    Average (2000)

    Attached is a small workbook that poses my problem. I have columns with formulas. I want to only average the numbers from the column that is >0. Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Average (2000)

    Your description isn't very clear, but does
    <pre>=SUMIF(C3:C6,">0")/COUNTIF(C3:C6,">0")
    </pre>

    do what you want?

  3. #3
    BakerMan
    Guest

    Re: Average (2000)

    I need to be more clear. The formula needs to look and see if any cells in the range are greater than 0 then average just those cells that are >0. When I just use the average function, it adds the totals then averages by the total number of cells in the formula. I think it is because each cell in the column that I want to look at contains a formula. The formula that I have pasted on the right side was a trial and error that didn't work. Thanks for the fast reply.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Average (2000)

    My formula should work if you adjust the range to be looked at.

  5. #5
    New Lounger
    Join Date
    Oct 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Average (2000)

    Does this work for you?

    =AVERAGE(IF((C5:E5)>0,C5:E5)) as an array

    Patrick

  6. #6
    BakerMan
    Guest

    Re: Average (2000)

    Hans , as usual, you are correct. thanks for all relies.

Posting Permissions

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