Find the Average with Selected Cells

[rudevincy]

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

Please HELP!

 

[NeonRedSharpie]

=averageif(A1:A15,"<>"&0)

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

 

[rudevincy]

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]

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]

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]

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

 

[Weazel]

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

 

[XOR LX]

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

Sure!

 

[barry houdini]

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]

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