# Thread: Averaging with Zeroes (Excel 2000)

1. ## Averaging with Zeroes (Excel 2000)

I put together the following formula to average two cells with zeroes. IF(AND(O107>0,Q107>0),AVERAGE(O107,Q107),(O107+Q10 7)/1).
If O or Q are greater than zero, then AVERAGE, otherwise, Add the cells and divide by 1.
Hokey, but it works. Of course unless you want to do three cells. And four.

I checked around and no one's multi cell averaging works properly. My cells are also not adjacent. How do I check a range for zero and then count the number of items I have to divide by? I may have answered my own question....

2. ## Re: Averaging with Zeroes (Excel 2000)

Can you explain what exactly you want to do?
Do you want to include or exclude cells that contain a value of 0?
Do you want to include or exclude blank cells?
Can there be cells with negative values, and if so, do you want to include or exclude them?
Can there be cells with text values, and if so, do you want to count them or not?

3. ## Re: Averaging with Zeroes (Excel 2000)

You could link the results of interest into a range of configuous cells then use SUMIF / COUNTIF combination.

Steve

4. ## Re: Averaging with Zeroes (Excel 2000)

Attached is a one sheet workbook that gives a few examples on averages, with and without zeros. After having the data in a contiguous range as Steve mentioned.

5. ## Re: Averaging with Zeroes (Excel 2000)

If all you are trying to do is ignore the zero's in your column try =Average(if (b2:b12<>0,b2:b12,"")) and enter it as an array CRTL+SHIFT+ENTER. Using this formula on the sample sheet from above, the average works out to 47.28571.

6. ## Re: Averaging with Zeroes (Excel 2000)

Interesting. i think all of these formulas have to do with the cells being in order, ie, all in the same column or row. What I have is a series of data like this (because of the way it gets imported):

Column heading: Amount Percent Amount Percent Amount Percent Amount Percent
Value: 20 30% 30 40% 0 0% 50 20%

I want to take the sheets natural state and pick out all the percents and average them- 30%+40%+20% / 3.

Each of the items above would represent a different area and that value is used in other formulas as well. They are the same type of value, but for a different group. They exist in one row because of a systematic report that is generated. I did figure out formulas that work (like in this post) when the like data is all aligned, but I dont know how to create a range with select values, like every other one, or every third value.

There wouldnt be any negatives. I want to exclude zeroes. There wouldnt be any text.

I am in a tight spot in organizing the data because this existed before I came along. Anyway, I would like to be able to use the data regardless of how it exists. I tried integrating Count, Count IF, Sum, etc to no avail.

7. ## Re: Averaging with Zeroes (Excel 2000)

In that case try
=AVERAGE( IF( b2:b12<>0, IF( LEFT( CELL( "format", b2:b12), 1)="P",b2:b12, ""), ""))

Building upon the earlier formula to look for non-zero values, this then looks for non-zero values in cells that are Percentage formatted

Hope that this helps.

8. ## Re: Averaging with Zeroes (Excel 2000)

Try this:

=SUMIF(A1:H1,"Percent",A2:H2)/SUMPRODUCT((A1:H1="Percent")*(A2:H2<>0))

9. ## Re: Averaging with Zeroes (Excel 2000)

Can you include formats as a criteria? The cell format would be percent, as opposed to a number. Here is an example attached:

10. ## Re: Averaging with Zeroes (Excel 2000)

I'm not using the formats as my criteria. I'm using the column labels you showed in your previous post. If the column labels are not predictable, then my solution won't work. The spreadsheet you posted has different column labels. Also, you seem to only want to include Me,You & Her. Why is Him excluded?

It would be pretty easy to write a custom function that would average all nonzero, percent-formatted numbers in a range. If you want to go that route, I can do that for you. But your last spreadsheet has confused me about the criteria for inclusion.

11. ## Re: Averaging with Zeroes (Excel 2000)

How about sumif, countif on a list that has the criteria?

The criteria is three items (add them to A8-A10)
A8=me
A9=you
A10 = her

in A3:
<pre>=AND(ISNUMBER(MATCH(A1,\$A\$8:\$A\$10,0)),A2>0) </pre>

Copy A3 to B3 to H3 [This is the criteria row]

<pre>=SUMIF(A3:H3,TRUE,A2:H3)/COUNTIF(A3:H3,TRUE)</pre>

Steve

12. ## Re: Averaging with Zeroes (Excel 2000)

In what country does a person average zero? This should be automatic. Excel should know to exclude zeroes, but I guess they are having the same issues.

The problem I have is that relationships among the data change on occasion because they are people in groups with varying details even beyond what I showed. The organization of those groups change, so I want a formula I can adjust without rearranging all the data. When I began this whole thing I was surprised that the Excel formula wizards do not seem to accomodate such complexity. Averages seem to depend on ranges, not user choices.

I guess the real answer is to better organize the data, although that organization itself does not suit how the data originates. It seems like I should be able to say, "I have these specific cells, or groups of cells, so please find the average excluding zero."

I know its kind of a crazy scenario, but this is how data in real life comes to me. And how I inherit a mess that I have to work with. I have spent much time organizing data and automating calculations and imports, but sometimes I just like to use what there is and make excel do all the work.

Ive gotten Excel to do some tough things, so I guess Ill pursue my options from here. Ill figure out a best solution.

Thanks.

13. ## Re: Averaging with Zeroes (Excel 2000)

Excel will ignore, blank cells, and text, but will not ignore numbers (even zero). I don't understand the need to ignore them. I think most countries average all numbers. I would never ignore zeroes in my averages.

Steve

14. ## Re: Averaging with Zeroes (Excel 2000)

When I was in school, if I made a zero on a test, that zero was averaged in like any other score. In most cases I can think of, a zero result would be included in the average. If my bank balance goes to zero, the zero is included when calculating the average daily balance.

15. ## Re: Averaging with Zeroes (Excel 2000)

Agreed, but based on real world situations like the one I described, I am trying to average groups of like data to come up with an accuarate average where there is no mandatory assignment to a value. Thus, zero is just "blank". Zeros arent penalized here. If I had thre people doing quality checks weekly and one got hit by a bus, I am not "failing" the whole group because one total was zero.

If Group 1:10%, Group2:10%, Group3:0% the totals could differ between 10% and 6%. There are two types of averaging situations. Here, like in many busienss related sceanrios, you need to figure out the average of a real group of numbers. A mandatory participation isnt necessary for each of the three groups, so you wouldnt penalize the "Group effort" with the zero. These arent test scores people. Averaging zero would not be a fair assesment of quality, or anything when you have like categories. That is not an accurate assesment.

Listen, is this an end user application, or a programmer's tool? I heard 12 different solutions and no one can average non-consecutive cells while ignoring zero using excel in its normal state. Thats voice of client to me.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•