Averaging with Zeroes (Excel 2000)

Topic

New Reply

I put together the following formula to average two cells with zeroes. IF(AND(O107>0,Q107>0),AVERAGE(O107,Q107),(O107+Q107)/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….

Reply | Quote

Replies

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?

Reply | Quote

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

Steve

Reply | Quote

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.

Reply | Quote

If all you are trying to do is ignore the zero’s in your column try =Average(if (b2:b120,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.

Reply | Quote

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.

Reply | Quote

In that case try
=AVERAGE( IF( b2:b120, 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.

Reply | Quote

Try this:

=SUMIF(A1:H1,”Percent”,A2:H2)/SUMPRODUCT((A1:H1=”Percent”)*(A2:H20))

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

There is a difference between a 0 and a blank. Blanks are not averaged.

Select the range of interest
Edit – replace
Find: 0
replace :[Leave blank]
Check: find entire cells only

And the zeroes in the range will be eliminated and won’t be averaged

Steve

Reply | Quote

The Excel AVERAGE function does not include blanks in the average. So, if ‘zero is just “blank”‘ as you said, it is not included in the average. What is the problem? Excel is doing exactly what you say you want! Zero in NOT a blank, it is a value, and as my examples show it should be included in the average.

Reply | Quote

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:

=AND(ISNUMBER(MATCH(A1,$A$8:$A$10,0)),A2>0)

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

Your average is:

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

Steve

Reply | Quote