Free Microsoft Excel 2013 Quick Reference

- Calculating Averages but excluding zero's
- Calculating a rolling 30-day valid-test average (without zeros)
- Find average of 5 lowest numbers in 10 excluding zero's
- Average of open-ended column, within given intervals
- Trying to create SUB_TOTAL average function excluding zeros and nulls
- Formulas: Average but not if there are Zero's
- Formula to calculate min/max/average of filtered list
- Omit Na Error In Average
- Average Omitting Blanks / Zeros
- Average for Numbers More Than Zero
- Pivot Table - grouping numbers with decimals, excluding rows
- Calculate Average Using Non-consecutive Columns
- Simple average solution? thanks
- AVERAGE and/or IF Question
- Summing & Averaging of absolute values vs. empty cells
- Hidden zero values messing up averages in pivot table - help!
- #N/A In Max min Average Formula
- Average starting with first month
- How do I average a column without including zeros
- Error Handling #N/A with AVERAGE Function - Average of values in Row

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.

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

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

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

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.

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

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

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

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))}

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.

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,

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

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

=(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

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?

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?

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

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.

any cells with a 0%, what formula would I use? Thanks!!!

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