non-contiguous non-zero sum averages

[unclehighbrow]

I've been trying to averages without zero values, and that was pretty easy, I found a number of solutions that worked perfectly. Although when I try to make those solutions work with non-contiguous cells, i.e., =AVERAGE(IF(H3,I3,M3,P3,P4,S3)) instead of =AVERAGE(IF(H3:H12)) I either get a #VALUE or just an Excel 'you did something wrong' dialog. Anybody get what I'm talking about? Is this as easy as it seems like it should be?

Thanks
unclehighbrow

[Joe4]

Check out the various replies in this thread. There are many more, if you use the Search functionality on this web site.

http://www.mrexcel.com/board2/viewto...ight=averageif

[GaryB]

I may be misunderstanding you, but wouldn't =AVERAGEA((H3,I3,M3,P3,P4,S3) do what you want?

GaryB

[Aladin Akyurek]

=AVERAGE(IF(LARGE(NCrange,ROW(INDIRECT("1:"&COUNT(NCrange)))),LARGE(NCrange,ROW(INDIRECT("1:"&COUNT(NCrange))))))

which must be array-entered, that is, you need to hit control+shift+enter at the same time, not just enter.

NCrange is the name that you type in the Name Box after pressing and holding down control key while selecting all relevant non-consecutive cells.

[unclehighbrow]

Yeah, I'm probably not being that clear. AVERAGEA doesn't work for me because it doesn't ignore zero values. And that link to another thread kind of left me more confused than I already am. Before, when I needed to get the ignoring zero averages, I'd have a hidden cell that had the count of the column, that went like

=COUNTIF(R3:R24, ">0")

and then just used that to divide the sum of the row. But doing this, that is, averaging non-contiguous cells:

=COUNTIF(R3,R24,R30, ">0")
or
=COUNTIF((R3,R24,R30), ">0")

messes it up

[unclehighbrow]

Oh, and I didn't read Aladin's post before I replied. That's a damn fine piece of excel work there, but if I have to go to all the trouble of setting up names for every group, I might as well just go through and just pick out all the non-zero values and stick them in an average, you know? So do I still have a problem or is it that I just don't understand the solutions given to me. Or both?

Thanks
unclehighbrow

[NateO]

Hello,

Didn't we decided that the following's a good way to do this:

http://www.mrexcel.com/board2/viewto...58360&start=10

?

[PaddyD]

"That's a damn fine piece of excel work there, but if I have to go to all the trouble of setting up names for every group, I might as well just go through and just pick out all the non-zero values and stick them in an average, you know"


Perhaps, although nameing them might save you time if you had to operate on the same set of non-contiguous rangtes a lot. In any event, either there is a regularity about the cells you want to pick (every third column, for example), or you'll have to define them explicitly, whether with names or range references.

[Aladin Akyurek]

Originally Posted by PaddyD

"That's a damn fine piece of excel work there, but if I have to go to all the trouble of setting up names for every group, I might as well just go through and just pick out all the non-zero values and stick them in an average, you know"


Perhaps, although nameing them might save you time if you had to operate on the same set of non-contiguous rangtes a lot. In any event, either there is a regularity about the cells you want to pick (every third column, for example), or you'll have to define them explicitly, whether with names or range references.

My reply assumes/pre-supposes indeed an irregularly spaced non-contiguous set of cells/ranges. If there is any regularity in spacing, then formulas exploiting such regularity should be used instead.

[unclehighbrow]

All right, get this:

=IF( OR(H9,I9,M9,P9,S9),(H9+I9+M9+P9+S9)/(IF(AND(H9<>0),1,0)+IF(AND(I9<>0),1,0)+IF(AND(M9<>0),1,0)+IF(AND(P9<>0),1,0)+IF(AND(S9<>0),1,0)), 0)

Kind of unweildy, but I guess that's how it has to be.

Thanks for everyone's help

unclehighbrow