# 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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Code:
``=averageif(A1:A15,"<>"&0)``

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

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

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.

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

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:
I think you're right, XOR LX, thanks for catching that

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)

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
4
Views
229
Replies
7
Views
210
Legacy 143009
L
Replies
0
Views
169
Replies
4
Views
161
Replies
7
Views
274

1,211,530
Messages
6,102,374
Members
447,795
Latest member
siddharthvb

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back