Re: Average if

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Try Domenic's second formula. As is, it will work on your first range in
column B. You'll need to modifiy it slightly to get it to work on your other
column, column N:

Change:
0

To:
<>0

Biff

"Curtis" <Curtis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8CCA6085-11A7-4327-8F42-EFFBD0B146AF@xxxxxxxxxxxxxxxx
Sorry new to this!

Actual ranges where I need the averages are b9:b17 and b24:b53. This range
can contain zero value, which I need excluded... No negative values

However there is a range in a different column that des have zero and
negative values where I will need to average the negative values (i.e.
n9:n17
and n24: n53

"Biff" wrote:

Well, that's why it's a good idea to provide as much detail as possible
when
posting a question. The formula I suggested only works when each range is
the same size. So, instead of taking another guess at what you want:

Are there any negative numbers in either range?

Are there any empty cells in either range?

Biff

"Curtis" <Curtis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ECDA3AE5-046C-4194-A60C-80DE88012AE2@xxxxxxxxxxxxxxxx
Thanks

But when I use the formula

{=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<>0,CHOOSE({1,2},B9:B17,B24:B55)))}

it returns a value of #N/A

What am I doing wrong?

"Biff" wrote:

Hi!

Is this what you mean?

Average A1:A5 and F1:F5?

If so, try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<>0,CHOOSE({1,2},A1:A5,F1:F5)))

Biff

"Curtis" <Curtis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:85A75973-16B5-44B9-8731-A176771B3353@xxxxxxxxxxxxxxxx
How do I calculate the averages of numbers in 2 sets of ranges...
excluding
zero vaules?

Thanks








.

Relevant Pages

  • Re: Min Function Excluding Zero Values & More
    ... Biff ... > Instead of returning 0 and having to use a formula that excludes it, ... >> 1) Find the MINexcluding zero values. ... >> non-zero value, this means that row 6 is now fully populated and should ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Name & Number in one cell.
    ... Biff, I have tried and I get an error message that states the formula ... a plain zero, ... In cell d1 I would like to show the name only (from cell a1 or ...
    (microsoft.public.excel.worksheet.functions)
  • Re: calculating in a changing range
    ... > Biff, thank you very much for the help. ... > 3, but, this data is actually data on a circle(example: first cell is a ... > recording at 0 degrees, and the last cell is a recording 359.9 ... > degrees), so, is there a way to find the max between zero 3 and zero ...
    (microsoft.public.excel.misc)
  • Re: Day of week referenced in if statement
    ... "Biff" wrote in message ... >>Peo Sjoblom ... >>> the date in the cell, if not return a zero. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: More elegant method?
    ... Biff ... > I am trying to pull the latest number greater than zero from the list. ... > the extra cells, and would like to get it in a more elegant manner. ...
    (microsoft.public.excel.worksheet.functions)