Find the Average with Selected Cells

rudevincy

Active Member
Joined
Feb 21, 2005
Messages
417
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!
 

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).
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top