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.

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

- Average of separate cells, exclude zero
- Calculating an Average excluding Zeros across a workbook
- Average a range of data but exclude blank data
- Calculating Averages but excluding zero's
- How to average a column, but exclude zero AND negative values?
- How to average a column, but exclude zero AND negative values?
- Calculating Averages but excluding zero's
- Summing Data, but Excluding certain data
- Min function to exclude zeros
- Average values with the corresponding name , but exclude zeros and blanks.
- Average percentage excluding zeros spanning numerous tabs
- Average percentage excluding zeros spanning numerous tabs
- Excluding zero value cells in average
- Average Non-contiguous Cells & Exclude Zeros
- Weighted average Non-contiguous Cells & Exclude Zeros
- Min Function Excluding Zero Values & More
- Calculating the Average of Every Nth Value, Excluding Zeros
- Min Function Excluding Zero Values & More
- Find average of 5 lowest numbers in 10 excluding zero's
- Trying to create SUB_TOTAL average function excluding zeros and nulls
- Average last X rows excluding zero values
- Average Non-Contiguous Range Excluding Zeros
- Collect data from last row and second last row in workbook but exclude three pages
- Add and average numbers, excluding zeros

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!

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

values, but what about zero AND Negative?

Thanks!!

values, but what about zero AND Negative?

Thanks!!

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

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

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

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

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

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

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

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

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

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

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 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(OFFSET(B2,COUNT(B2:B60)-1,,-4))

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

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:error i get is runtime 9 subscript out of rangeRectangle7_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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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 SuIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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