Averaging 1

[drm184]

Is it possible to average part of a row, but to not count the values of zero in the average? Meaning if I have values of 0,2,3,0,4,0,2; to count everything but the three zeros in the average. The reason I say part of the row is that the average column for those rows is in the same row, so i couldn't select the whole row. Not sure if that makes a difference or not. Thanks for the help!

 

[JVFriederick]

For a range of data within row 1-9,

=SUMIF(A1:A9,">0&quot/COUNTIF(A1:A9,">0&quot

 

[JVFriederick]

Whoops. Skip the first SUMIF ---> you don't need it !

=SUM(A1:A9)/COUNTIF(A1:A9,">0&quot

Must be too early in the morning.

 

[MisterC]

If the numbers you wish to average are going to be in the same columns for each row an average is calculated, the easiest way is to reference the individual cells to be averaged instead of a range.
For instance, if your average is in cell J5, and you wish to average cells B5, D5, and H5 your formula would be:

=Average(B5,D5,H5)

You could copy this formula down column "J" as far as you wanted and it would always reference the same columns.
As far as counting zero values in your average, well, zero is a number and will be averaged along with the rest of the values. If you want to exclude zero values you may need to replace the "0" with "" --------------
A little knowledge is a dangerous thing.