Find the Average with Selected Cells

rudevincy

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

Some videos you may like

Excel Facts

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

rudevincy

Active Member
Joined
Feb 21, 2005
Messages
415
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
Joined
Jul 14, 2014
Messages
1,678
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
Joined
Dec 24, 2011
Messages
3,155

ADVERTISEMENT

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
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Dec 24, 2011
Messages
3,155

ADVERTISEMENT

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,919
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top