Free Microsoft Excel 2013 Quick Reference

# Average Dynamic Data But Exclude Zeros

Greetings

I use a SQL query to export a subset of data from our server. After manipulating the received data I need to Average one column that has been sorted into a number of blocks consisting of variable numbers of rows and post the result to the ajacent cell. I have tried the Countif worksheet function but am unable to resove the syntax problem. I would be grateful for any assistance and thankyou in advance.

```
VB:
SumBlock()
Dim First_Row As Long
Dim Last_Row As Long
Dim iTotalRows As Long
Dim iCount As Long
iTotalRows = Range("A65536").End(xlUp).Row
First_Row = 2
Do While Last_Row < iTotalRows - 1
Last_Row = ActiveSheet.Range("j" & First_Row).End(xlDown).Row
iCount = Application.WorksheetFunction.CountIf(Range("J" & First_Row), ("J " & Last_Row), ">0")
ActiveSheet.Range("K" & Last_Row + 1).Formula = "=Sum(J" & First_Row & ":J" & Last_Row & ")/iCount"
First_Row = Last_Row + 2
Loop
End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

## Related Results

### Average of separate cells, exclude zero

I am trying to average particular cells and exclude zeros, but I have not been able to figure it out.
I started with this formula:
=SUM(C5:C18)/COUNTIF(C5:C18,">0")

but really, it needs to be more like:
=SUM(G8,J8,M8,P8,S8,V8,AB8,AE8,AH8,AK8,AN8,AQ8,AT8,AW8,AZ8,BC8,BF8,BI8)/COUNTIF(G8,J8,M8,P8,S8,V8,AB8,AE8,AH8,AK8,AN8,AQ8,AT8,AW8,AZ8,BC8,BF8,BI8,">0")

i need to average specific cells, not a range.
HELP!

### Calculating an Average excluding Zeros across a workbook

Hi,

I have a workbook with 7 months of data spread across 7 sheets (I'm only starting now and going until the end of the financial year).

The sheets each contain a cell, K32, which calculates the average number of days taken to process an application which passes through our offices. The code in this cell is the same for each sheet;

=SUM(H:H)/MAX(1,COUNTIF(H:H,">0")+COUNTIF(H:H,"<0"))

On the final (eighth) page, there is a summary of the stats with running totals , showing how we are doing on each stat in the workbook, i.e. how many applications have been processed in the year so far, etc etc.

I'd like to have a box showing the average days taken to process ALL applications received to date, i.e. a running total, in the final summary sheet. However, this is proving problematic. The reason being that some of the K32 cells contain '0' (zero) results, as no data has been entered for those months yet. This means that the average will be wrong, as it's including the zeros in it's calculation of the time taken to process the applications.

The question is therefore how I can get the system to calculate an average across the workbook from each K32 cell, but excluding zeros (I'm using " =SUM('September 09:March 10'!K26) " to provide sums for other cells across the workbook - i.e. through the range of seven months, and these formulas are working fine)

Any ideas would be great!

thanks,
K

### Average a range of data but exclude blank data

I am trying to average a range of cells =AVERAGE(I4:I10) but I have data that is blank. How do I write the formula to average the cells andskip no value cells but still accept zero's?

### Calculating Averages but excluding zero's

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.

--
fodman
------------------------------------------------------------------------
fodman's Profile: http://www.excelforum.com/member.php...o&userid=31941

### How to average a column, but exclude zero AND negative values?

I used the formula of =AVG(IF(A1:A10)0,A1:A10)) to exclude just the zero
values, but what about zero AND Negative?

Thanks!!

### How to average a column, but exclude zero AND negative values?

I used the formula of =AVG(IF(A1:A10)<>0,A1:A10)) to exclude just the zero
values, but what about zero AND Negative?

Thanks!!

### Calculating Averages but excluding zero's

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.

### Summing Data, but Excluding certain data

I have a worksheet that contains codes in column A and values in column C

The last value in in cell C50. I want to add all the values in column Code, but exclude any value where the codes in column C contains 50, 51, 55.

for eg if the code is 5000, 5001, 5100, 5105, 5500, 5501, 5510 etc the value must be excluded.

Your assistance will be most appreciated

Regards

Howard

### Min function to exclude zeros

Hi,

I need to create a formula that gives me the min of a range but excludes zeros. So if the range contained, 0, 1, 2, 3 the min would be 1.

Is there a function to enable me to do this?

Thanks

### Average values with the corresponding name , but exclude zeros and blanks.

Hi Everyone,

I have an issue with a model I am building. I can't figure out how to pull the average for values that correspond to different names. If I have Column A with company names repeating in random order, and column B with their associated value, I need to average the associated values for each company. However, I need it to ignore the zeros and blanks, otherwise the data is skewed.

I have been able to pull the averages with an array formula {=AVERAGE(IF(A\$21:A\$64 = A4, B\$21:B\$64)))}, but not exclude the zeros. Any help is greatly appreciated. Attached is an example.

Thanks,

### Average percentage excluding zeros spanning numerous tabs

I've got a sheet consolidating the data from various detail sheets... I need
to create a formula for the average percentage excluding zero values... i've
got the following formula =AVERAGE(IF(A1:A52>0,A1:A52)) but that doesn't give
the option of using information from different tabs (the data is in the same
cell in each tab)... any ideas?!

Cheers

### Average percentage excluding zeros spanning numerous tabs

I've got a sheet consolidating the data from various detail sheets... I need
to create a formula for the average percentage excluding zero values... i've
got the following formula =AVERAGE(IF(A1:A52>0,A1:A52)) but that doesn't give
the option of using information from different tabs (the data is in the same
cell in each tab)... any ideas?!

Cheers

### Excluding zero value cells in average

Hi everyone,

Sorry if this is a double post but I had a hard time finding what I was really looking to do. I want to compute the average of a row, excluding the zero values. Each week, a new value would be added. For instance, week 1 would just be one term, week 2 would average the first two terms, week 3 would average the first three. The remaining terms out to 20 would be zero and should not be included in the average. The result would remain in the same cell throughout. I was thinking kind of 'if statement' but I couldn't come up with it.

bws

### Average Non-contiguous Cells & Exclude Zeros

I would like to average a non-continuous range of cells while also excluding all zeros.

I am averaging hours worked and the hours are found in cells:

B2, D2, F2, H2, J2, L2, N2

Some of these cells contain zeros at this time and I do not what to include the zeros in the average.

I have found formulas that would work but they are all with continuous cell ranges.

Any help would be greatly appreciated.

### Weighted average Non-contiguous Cells & Exclude Zeros

Hi,

I'm quite a novice at excel and would appreciate your help.

My query:
I need to calculate a weighted average of stock days across several oper
Data is non contiguous but in separate sheets (see uploaded sample spreadsheet)
Where data on stock days is not provided, the value of that stock should be excluded from the calculations to determine the weighting.

I include a sample sheet. B4 is the value I'm looking for.

Thanks

### Min Function Excluding Zero Values & More

Disclaimer: My experience with and knowledge of Excel is very limited.
I'm not sure if what I'd like to do can be done with a simple function
or if I can explain clearly what I'm trying to do.

I have a column (A1:A10) of totals derived from the rows that intersect
them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only
A1:A6 contain non-zero values, so far.

I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There
are two MIN issues I need to resolve:

1) Find the MIN(A1:A10) excluding zero values. I found this solution
in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift
+ enter. This works fine. This solution may not be needed if it can be
incorporated into the solution for the second part.

2) The zero values in A1:A10 will always be at the bottom of the list
until the corresponding rows are populated a row at a time descending.
So, with A1:A6 containing non-zero values, I'd like to exclude from the
MIN function not only the zero values A7:A10, but A6 also (the last
non-zero cell.) Is there a way to simply find the first zero value
cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from
the MIN function on A1:A10? Does this make sense?

The reason for this is A7 remains a zero value until row 6 is fully
populated and row 7 gets its first piece of data. When A7 has a
non-zero value, this means that row 6 is now fully populated and should
be considered in the MIN(A1:10) function. At this point A7 should be
excluded even though it is no longer a non-zero value because row 7 in
not fully populated yet.

Any help would be appreciated.

Thanks,
--Robert-->

--
WeatherGuy
------------------------------------------------------------------------
WeatherGuy's Profile: http://www.excelforum.com/member.php...fo&userid=9254

### Calculating the Average of Every Nth Value, Excluding Zeros

Hi,

I want to find average of every nth value, excluding zeros for which I found the solution through net as "=AVERAGE(IF((MOD(COLUMN(AE2:BI2)-CELL("col",AE2)-32,4)=0)*(AE2:BI2<>0),AE2:BI2))". This worked for range AE2:BI2 but when I copied the formula for the next result cell i.e., for range AF2:BJ2 it worked but I am not able to understand what is the role of -CELL("col",AE2)-32,4)=0) where I changed -32 to -33 for AF2:BJ2 range it did not work. This is i presume no. of cells difference from start cell. However it works for both the ranges with the same -32 value.

regards,

T. Saravana

### Min Function Excluding Zero Values & More

Disclaimer: My experience with and knowledge of Excel is very limited. I'm not sure if what I'd like to do can be done with a simple function or if I can explain clearly what I'm trying to do.

I have a column (A1:A10) of totals derived from the rows that intersect them (i.e. A1 contains =sum(B1:M1), A2 contains =sum(B2:M2), etc.) Only A1:A6 contain non-zero values, so far.

I am calculating the MIN, MAX, and AVERAGE of the A1:A10 column. There are two MIN issues I need to resolve:

1) Find the MIN(A1:A10) excluding zero values. I found this solution in another posting: =MIN(IF(A1:A10=0,"",A1:A10)) and press ctrl + shift + enter. This works fine. This solution may not be needed if it can be incorporated into the solution for the second part.

2) The zero values in A1:A10 will always be at the bottom of the list until the corresponding rows are populated a row at a time descending. So, with A1:A6 containing non-zero values, I'd like to exclude from the MIN function not only the zero values A7:A10, but A6 also (the last non-zero cell.) Is there a way to simply find the first zero value cell in A1:A10 (let's say A7) and exclude A6 (A7-1) through A10 from the MIN function on A1:A10? Does this make sense?

The reason for this is A7 remains a zero value until row 6 is fully populated and row 7 gets its first piece of data. When A7 has a non-zero value, this means that row 6 is now fully populated and should be considered in the MIN(A1:10) function. At this point A7 should be excluded even though it is no longer a non-zero value because row 7 in not fully populated yet.

Any help would be appreciated.

Thanks,
--Robert-->

### Find average of 5 lowest numbers in 10 excluding zero's

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

### Trying to create SUB_TOTAL average function excluding zeros and nulls

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.

### Average last X rows excluding zero values

This seems simple however it has me stuck. I am trying to average the last four rows in column B excluding the zero values. I am using the formula below however, I'm confused on how the if statement should be added. Any suggestions?

=AVERAGE(OFFSET(B2,COUNT(B2:B60)-1,,-4))

### Average Non-Contiguous Range Excluding Zeros

I am trying to create an array formula to average a list of cells that are not continuous and disregard zero values. I can get the formula to work if cells are continuous but when they're not it doesn't work. Here is what I am trying.

=AVERAGE(IF(P12,R12,T12,V12,X12,Z120,P12,R12,T12,V12,X12,Z12,""))

### Collect data from last row and second last row in workbook but exclude three pages

Hello,

new to vba,

I'm having trouble getting this to work, what i would like to achieve is to run two macro's to collect data from sheets in the workbook and collect these too two target sheets
one should macro collects data from the last row and sends it to target1, but exclude's the target sheet, sheet1 and sheet 2, the other macro should collect data from the second to last row and sends it to target2, same sheets should be excluded.

im trying to get this but im having a hard time excluding two pages, keep ending up with errors,

```
VB:
Rectangle7_Click()
'collect data from last row to target1
Dim ws As Worksheet
Dim lRow As Long
Dim xlSht As Worksheet

Set xlSht = Sheets("Target1")

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = xlSht.Name = "sheet1" And ws.Name = xlSht.Name = "sheet2" Then
'Do nothing
Else
ws.Rows("4:35").Copy
lRow = xlSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
xlSht.Cells(lRow - 1, 1).Value = ws.Name
xlSht.Cells(lRow, 1).PasteSpecial xlPasteAll
End If

Next ws

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
error i get is runtime 9 subscript out of range
this line is marked "If ws.Name = xlSht.Name = "sheet1" And ws.Name = xlSht.Name = "sheet2" Then"

Thanks

Ben

EDIT

I solved a part of my problem, only think left is how do i change my code to get the last two rows af data from every sheet?

```
VB:
Worksheet
Dim lRow As Long
Dim xlSht As Worksheet
Dim x2Sht As Worksheet
Dim x3Sht As Worksheet
Dim x4Sht As Worksheet

Set xlSht = Sheets("Mastersh")
Set x2Sht = Sheets("Catalog")
Set x3Sht = Sheets("Frontpage")
Set x4Sht = Sheets("WorkDetails")

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = xlSht.Name Or ws.Name = x2Sht.Name Or ws.Name = x3Sht.Name Or ws.Name = x4Sht.Name Then
'Do nothing
Else
ws.Rows("4:38").Copy
lRow = xlSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
xlSht.Cells(lRow - 1, 1).Value = ws.Name
xlSht.Cells(lRow, 1).PasteSpecial xlPasteAll
End If

Next ws

End Su

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```

### Add and average numbers, excluding zeros

I am currently working on an excel 97 spreadsheet where I need to average percentages. However, not all the numbers that I am averaging are numbers; they are zeros, and I do not want to include these zeros in my average. Therefore, I want to add only the numbers entered, then find the average of those number. For example:
95%
0
0
92%
85%
Total: 91% (divided by 3 not 5)
I do need to keep the zeros in, but I cannot include them in my count.
Please advise me on how I could enter a formula that would handle this problem.

Thank you,
Max