# Find the Average with Selected Cells

#### rudevincy

##### Active Member
Hi

I have a worksheet that lists current data and YTD data.

A1 current data
A2 YTD data
A3 current data
A4 YTD data
A5 current data
A6 YTD data
A7 current data
A8 YTD data
A9 current data
A10 YTD data
A11 current data
A12 YTD data
A13 current data
A14 YTD data
A15 current data
A16 YTD data

I would like to find the average of these numbers but with only those numbers that are not 0. I have created this formula but excel is not allowing me to select more then 4 cells.

the formula that I have is in A25 to find the average of just the current data cells is =AVERAGE(IF(A1,A3,A5,A7,A9,A11,A13,A15<>0, A1,A3,A5,A7,A9,A11,A13,A15,"")) but it is telling me there are to many arguments in the function.....

how do I make this formula work.....

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### NeonRedSharpie

##### Well-known Member
Code:
``=averageif(A1:A15,"<>"&0)``

But I don't know about doing every other...

#### rudevincy

##### Active Member
I cannot use the range A1:A15 as I only want to count the current data cells which are A1,A3,A5,A7,A9,A11,A13,A15

#### NeonRedSharpie

##### Well-known Member
I know, that's why I have that comment in my post. Is there a header to the left or to the right? Maybe that says YTD or current? Then you can use averageifs to pull that into the formula.

#### Weazel

##### Well-known Member

this logic always seems to make me squirrely but maybe something like...

=AVERAGE(IF((ISEVEN(ROW(A1:A16))*(A1:A16<>0)),A1:A16)) control shift enter

which would rely on the data starting in A1

#### XOR LX

##### Well-known Member
this logic always seems to make me squirrely but maybe something like...

=AVERAGE(IF((ISEVEN(ROW(A1:A16))*(A1:A16<>0)),A1:A16)) control shift enter

which would rely on the data starting in A1

Don't you mean ISODD? I thought we were taking every other row beginning with the first?

In any case, you're right to be "squirelly" . I think it's better to "generalize" that solution to:

=AVERAGE(IF(ISODD(ROW(Range)-MIN(ROW(Range))+1)*(Range<>0),Range))

which will calculate the average over every other cell (beginning with the first) in Range and which does not depend upon the row number of the first cell in that range.

Replace Range with whatever happens to be the range in question, of course.

Regards

Last edited:

#### Weazel

##### Well-known Member

I think you're right, XOR LX, thanks for catching that #### XOR LX

##### Well-known Member
I think you're right, XOR LX, thanks for catching that Sure! #### barry houdini

##### MrExcel MVP
Assuming you don't have negative values you can use this formula

=SUM(A1,A3,A5,A7,A9,A11,A13,A15)/INDEX(FREQUENCY((A1,A3,A5,A7,A9,A11,A13,A15),0),2)

• XOR LX

#### XOR LX

##### Well-known Member
Assuming you don't have negative values you can use this formula

=SUM(A1,A3,A5,A7,A9,A11,A13,A15)/INDEX(FREQUENCY((A1,A3,A5,A7,A9,A11,A13,A15),0),2)

Perhaps not so flexible in terms of extending to larger ranges, but nice nonetheless!

Regards

Replies
9
Views
143
Replies
3
Views
43
Replies
15
Views
74
Replies
1
Views
22
Replies
8
Views
217