Free Microsoft Excel 2013 Quick Reference

Average excluding zeros Results

Hi,

i want to calculate the average of a column but exclude anyzeros in that column.
For example: If column A1 contained 2, 2, 0, 2, 2, 0 then the average would be 2. (8/4) NOT (8/6)

Thanks.

Need help with calculating a rolling 30-day valid-test average. Tests with a result of zero are deemed invalid for this discussion. The zeros must be excluded from the average, which must consist of 30 valid results.

The challenge here is the variable number of values which get evaluated... for example, 2/6/2010 in the attached example requires 36 calendar days to obtain 30 valid tests for the average. The frequency of zeros is also random. Help!

Thanks in advance,

Air Hugger

Hi all I am trying to find the average of the 5 lowest numbers of a group of ten, but the ten may contain zero's which I want to exclude from the lowest 5.

as an example
A1= Score
50
20
60
20
35
0
0
0
0
0

but the zero's could be anywhere in the ten, so if I copy and paste to a new range then sort it to lowest 5

Lowest 5
20
20
35
50
60

I get 37 average

I want to be able to do it on the original list of ten

Hello

I wonder if anybody can help me out on this one.

I need to average a column, without a fixed number of rows, and within an interval (and excluding zeros).

Example: Average of all numbers in the bracket 28-35, in column B2-B??

By this I mean that the total of rows in column B will vary from time to time, so in order to not having to manually change the number of rows each time, I need an "open-ended" formula, that also incorporates say 4 intervals (for instance 1-27, 28-35, 36-70, 71-100).

I would be grateful for any feedback!

Regards,
Christian

Hi Experts,

I am relatively new to excel subtotal functions. Here is my problem. I need to find an average of ages, so I cant use either 0 or null in my average function. Though 0 or null mean the same in this case, as i cant control user entered data, I cant alter what they entered.

states ages
va 1
va 2
pa
md 3
md
md 4
va 0
va 5
va 6
md 0
pa 3
average 3.428571429
For example for the above data , if i filter for different states, I should get respective averages of that state, excluding 0s and nulls. I need to embed the function inside a java program that will generate the excel, so its really hard for me to use something like below.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW($B$2:$B$12)-ROW($B$2),,1)),--($B$2:$B$12<>0))/
SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW($B$2:$B$12)-ROW($B$2),,1)),--($B$2:$B$12<>0)).

the above worked but it always shows, #VALUE for the first time and only when I go to the function "fx" space and then hit enter, it converts #VALUE to number.

I have a large spreadsheet with January to December Data.

Some of the rows have 0 in the monthly sales area. How can I get a monthly average but exclude the zero values?

Thank you

Hi

I have a Summary Sheet with calculations based on info contained within a Requests Sheet which contains details of correspondence received. Columns C:C on the Request sheet details the branch/section names and column CC:CC calculates the number of days taken to issue a response

I use this formula on the summary sheet to show minimum number of days to respond if the branch name is Finance:
{=MIN(IF(Requests!$C$2:$C$263=B1,Requests!$CC$2:$CC$263))} - this formula would be copied across a number of cells on the same row on the seperate sheet so B1 has the word Finance in it, c1 would have HR in it etc.

I use this formula on the summary sheet to calculate average number of days (excluding #Div/0 values) to respond if the branch name is Finance:
{=IF(ISERROR(AVERAGE(IF(Requests!$C$2:$C$263=B1,Requests!$CC$2:$CC$263))),"",(AVERAGE(IF(Requests!$C$2:$C$263=B1,Requests!$CC$2:$CC$263))))} - again this formula would be copied across a number of cells on the same row on the seperate sheet so B1 has the word Finance in it, c1 would have HR in it etc.

these formulas work fine, but what i now need is to calculate these figures if i filter column C:C in the Requests sheet. So if i filter column C just to show the Finance section then the calculation on the summary sheet for HR etc should show a mimimum and average of 0.

I tried this formula -
{=SUBTOTAL(5,Requests!$CC$2:$CC$263)*(Requests!$C$2:$C$263=B1)} to calculate the minimum but get a zero even if a filter hasnt been put on column C.

Can anyone suggest a correction? i would really appreciate it as i have been struggling with this one all morning

Thanks

Philip

Afternoon,

I am trying to produce a sort of interactive chart whereby the user can choose which data points to exclude.
I have done this with a seies of check boxes, and whether or not they are ticked, dictates whether the data point is diplayed.
The plotted x axis data substitues "" if the box is unchecked, while the data points themselves substitutes NA() if the box is unchecked, to avoid the data point being plotted as a zero.
This works fine, but I also need an overall mean to be plotted, but "average" doesn't work because of the NA(). If you uncheck a box, the mean line disappears.

Any suggestions how I can do this, please?

A simplified spreadsheet is attached to make things clearer, I hope.

Thanks very much,

Dave
Interactive Chart 27.03.2007.xls

I have run into a problem with the array formula. After inserting formula with the CTRL+SHFT+Enter it is giving me the same sum to the 3 cells that I had array formulas in but trying to capture different data from what was in the capturing column.
Example:
If row F consists of text types:Move-in, Mid Year, and Year End as potential options,and row G is the score for that text type(cells will consist percentages), give me the average of all the cells in row G that are specific to Move-In only, but don't include the blanks in the average.

My existing formula isn't designated to exclude the blanks. Can you help me confirm how to exclude blanks and how to get it to stop giving me the same result in the three separate cells.

My current formula is as such: {=AVERAGE(IF(F2:F73=E76, H2:H73))}

Hi,

I have a spreadsheet with a column of numbers. The numbers has a combination of zero and more than zero.

How do I build a formula to average the whole list only for numbers more than zero and exclude cells with zero ?

Thanks.

Got a tricky one, I want to create a pivot table starting with a column of percentages on the left, so for example the first column of the table will be:

25% (Count)
33% (Count)
50% (Count)

The idea being to count how many 50%s there are, how many 25%s etc. Problem is, all of my numbers aren't integers, so I actually have 25.1%, 25.14%, 25.6% etc which leaves my pivot table like this:

25%
25%
25%
25%
33%
33% etc then a count of all the specifics. Is there a way to get the pivot table to ignore everything after the decimal place?

I then want more info to work out some averages of all the values attached to those initial percentages. This works fine, however on some I am doing calculations with zero, giving me "#DIV/0!" and consequently the same value in the pivot table. Is there a way I can exclude that value from counting for just that particular average?

Cheers,

Hello, I'm hoping someone can help me out here. I have data in 3 different columns (A, C, and E) to name a few. I want to average each of these columns, but if any of them include zero values, I want to exclude that from my calculation.

Example:

Column A = 10
Column B = 0
Column C = 3

Right now, my "average" formula, is showing the average as 4.33. (average a5, b5, c5)

The real average I'm looking for is 6.5. What is the best way to setup my formula?

Thanks for any help you can give me.

Laura

Hello,

I need some help with an average. Probably really simple.

In the columns I have 12 months.
The 12 months will fill with data.
The months that arent filled will have a zero.

Now I want an average of the 12 months excluding those months that have zeroes.
So a running tally of things.

Doing =average takes those zeroes in to account and will reduce the actual zero.

Any suggestions?

Thanks for your help.

Marc

I'm trying to average cells of data along a row, some of which contain the formula,
=(AA2/13)*100
but I want to exclude data from those cells (in this case AA2) where the data is a blank, (still producing a zero value in the target cell) to avoid using false data and producing a false average.
Can someone please tell me the easiest way to do this?
Many thanks.
Nick

I have a series of derived 'text' numbers to some formulas. Based on an answer I had received earlier, I made sure that these results became true numbers, and hence could be averaged, by using the edit/paste special feature and checking 'Add'.

So, now all zero values are counted and included in any averaging. This is fine as long as EVERY respondent to the questionnaire in question provided a response. Not always true. Given that I now have made the cell value = to a zero value, how do I EXCLUDE any cells that are empty (i.e. blank)in my averaging. By way of example, if all 10 respondents answered and there was a zero value, then that would be O.K. for the denominator on the average to be 10. But, if only 4 of 10 respondents answered and the other 6 cells are empty, I would want to average the received responses over only 4 and not 10 responses received. Is there a way I can do this?

A am keeping track of call center performance data in excel, and am running into problems with Excel's pivot tables and zero values that show as blanks in the source data.

Example: in a 5-day workweek, a phone queue has the following abandonment rates: 2%, 0%, 2%, 3%, 0%. The average abandonment rate should be 2.3% ((2+2+3)/3) but instead excel instead gives 1.4%, since zero values are not being excluded.

When I look at my source data, the cells do have blanks, but the formula bar shows 0%. Is there a way to force the pivot table to ignore zero values, or will I have to manually find and replace all 0's with blanks (delete key)?

Can i add a calculated field to the pivot table that ignores zero values?

I have lots of tables of data e.g. A1:A12 that is linked to line graphs and
max/min/average calculations.

The graphs need to plot a zero value when zero is entered but plot nothing
when no data is entered.

I am entering #N/A into the table, this solves the graph problem but
interfears with the max/min/average calculations, they now desplay #N/A.

Is there a way to exclude #N/A from the calculations

I have a large worksheet of sales data where products are down the rows and
months are across columns. I would like a formula to calculate the average
monthly sales but only inlude those months starting with the month of first
sale going forward. If a sale month is zero the cell is blank Example
worksheet:

Mo1 / Mo2 / Mo3 / Mo4 / Mo 5
Product 1 10 / 10 / 8 / 8 / 9 Avg = 9 (Avg includes all months)
Product 2 / 5 / 6 / 7 / 6 Avg = 6 (Avg excludes Mo 1 of zero)
Product 3 10 / / 8 / 7 / Avg = 5 (Avg includes Mo 2 & 5 of
zero)
etc

Any ideas? Thanks, Jim.

I want to average a column that is all percentages, but I want to excluding
any cells with a 0%, what formula would I use? Thanks!!!

Hi All,

I have a spreadsheet with an Array entered Formula (based on Rows using the
TRANSPOSE Function) that produces the #N/A error when no further data is
available to be calculated: this in itself is fine - the #N/A is disguised
with Conditional Formatting. However, if possible, I would like to find the
AVERAGE of the values in each Row excluding those cells with #N/A from the
calculation.

I've tried a few variations using either the ISERROR or ISNA Function nested
with the AVERAGE Function. I get either zero or #N/A returned.

The original Formulae providing the results that I now wish to Average was
brilliantly created by Dominic in this previous Post: "Count Intervals of
Filtered TEXT values in Column and Return Count across a Row"

http://www.officekb.com/Uwe/Forum.as...40OfficeKB.com

The values returned from the above Formulae is numeric and formatted as
GENERAL.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200507/1