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


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!

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

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?

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
View this thread: http://www.excelforum.com/showthread...hreadid=555103

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!!

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!!

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.

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

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

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,

Adam

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

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

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.

Thanks for your help.

bws

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.

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

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
View this thread: http://www.excelforum.com/showthread...hreadid=497216

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.

Please clarify.

regards,

T. Saravana

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

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

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.

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

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,""))

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


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