Average Dynamic Data But Exclude Zeros

Feb 26, 2008

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.

Sub 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

View 5 Replies


ADVERTISEMENT

Get Average But Exclude Zeros AND Hidden Rows?

Aug 15, 2013

excluding zeros and for excluding hidden rows, but can't seem to find a way to do both at the same time, which is what I need to do.

My company has projects that come and go (and are hidden when they are gone) and at times, those projects return 0s because we do not work on them for a short period... so, when I average a column, I need to exclude both situations.

View 8 Replies View Related

Average Non-contiguous Cells & Exclude Zeros

Oct 10, 2007

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.

View 5 Replies View Related

Data Validation To Exclude Letters But Allow Numbers Beginning With Zeros

Nov 8, 2008

I am using Excel 07 with Vista, and creating a spreadsheet in which users will be inputting a record into every row.

There is a row that requires a number to be entered, where some of the numbers will begin with 0 or 00 or even 0000. I want to make sure no-one incorrectly enters a letter, however with data validation set to whole numbers, it formats the cell to change an entry of "005667" into just "5667". There is no set length for the field.

The only way to have "005667" is the change the column to text, but then obviously that defeats the whole purpose!

View 10 Replies View Related

COUNTIF Formula To Exclude Zeros

Aug 22, 2012

Names
Quarters
Nickels
Dimes
Pennies
Record exc 0s

John
0
1
2
3

[Code] ........

In my table above, I would like to use the countif formula to count records based on the person, but I don't want the count to include values if the sum of quarters, nickels, dimes, AND pennies equal 0.

View 8 Replies View Related

Exclude Rows Containing ZEROS In Column L

Feb 4, 2010

I analyze logged data that often contain ZEROS in column L, always starting on row 35. That's bad data. The first row with good data contains the number 700 in column L, but this row number is unpredictable.

How to make a VBA code to exclude the rows that contain the number 0 in column L, searching between cell L35 (including) all the way to the first row containing the number 700?

I need to limit the range to be scanned for ZEROS because sometimes there are valid ZEROS in column L, but those would be in rows below the rows contain 700 in column L.

View 9 Replies View Related

Exclude Zeros From Array Formula

Aug 23, 2008

I have 16 columns in a single row that I am trying to apply to an array formula. That works, but I need to exclude all cells with the number "0" from the array range. I think I can do this myself in VBA but I don't want to run a macro every time something changes. Is it possible to do this in excel?

{=10*log(10^(A1:A16/10))}

It is possible that A1:A4 is a non-zero range and A12:16 may be a non-zero range. (Zeros will always be grouped in 4s)

View 9 Replies View Related

Average Formula- Data Avaliable And Discard Any ZEROS

Jan 31, 2008

This is info I have in a column

The formula in each cell is

=REPLACE(Track!B58,5,11,"") the reason behind this is that the info originally is entered into one cell like 25.0 x 32.0 and I wanted to use a part of the cell for column A and the other for column B.

25.032.025.032.125.032.225.032.325.032.425.032.525.032.625.032.725.032.825.032.925.032.1025.032.1125.032.12

My dilema is that I want to average the row which I have tried loads of formulas etc which are not working

I use this as sometimes not all cells are filled in so I want it to average only the data avaliable and discard any ZEROS.

=AVERAGE(IF(B28:B41>0,B28:B41))

However when I use this formula it is just coming up with

#DIV/0!

how to enter a correct formula so I can get the average.

View 9 Replies View Related

Exclude Cells That Have 0 In Them From Average?

Jan 13, 2004

I have a list of weeks 1-4 for the fiscal month of January. I have a total column. Each week the appropriate week is updated and the total is updated via formula. The total column is just formulas adding Weeks 1-4 up. I also have an average column with the AVERAGE formula beside the total it that should give me the weekly average for January. However, it's trying to average all the weeks instead of just the weeks that I am on.

For example, Week 1 is 1,000,000. Week 2 is 500,000. Week 3 & 4 are 0 because there is no data in there yet. The AVERAGE formula keeps showing 375,000 instead of 750,000. It's averaging all the weeks and I just want it to average Weeks 1 & 2 right now, but automatically average Weeks 3 & 4 when they are populated.

View 9 Replies View Related

Exclude Outliers In Average Calculation

Oct 14, 2009

Is there a good way of excluding an outlier in an average calculation. In the example below will I exclude 1000 from the average-calculation.

The way to decide excluding-values can either be a percent based on the range or everything that is a higher than a user defined value. It can also be more than one outlier.
A user defined function is OK with me, if it is impossible to use the built-in functions.

View 6 Replies View Related

Exclude Minimum Two Values In Average

Dec 8, 2006

I have looked on here and I can find the minimum value in my range but what I am trying to do is find the minumum two values. Or rather, i want to find the average of the remaining values.

If the lowest value happens THREE OR MORE times, i still only want to exclude two of them. Same thing goes if the second lowest happens more than once, i only want to exclude at most two values from the avg.

currently, i have 12 values in my list.

example:

list --> one in each cell going across

25, 50, 87, 56, 99, 80, 81, 82, 50, 100, 98, 99

i have the answer as 83.2 doing it by hand. the 25 and one of the 50s would be dropped and the rest would be averaged.

View 3 Replies View Related

Exclude Hidden Cells In Average

Jul 18, 2007

I have a large data table that has autofilters on the headings.

I also have a "Data Summary" Sheet in which I would like to get a conditional average.

Here is my *working* array formula:

=AVERAGE(IF('Data Table'!$C$10:$C$65536=A2,'Data Table'!$BP$10:$BP$65536))

The problem is, it averages all cell values (conditional upon the C column's cell being equal to A2 of course), whereas I only want to average the VISIBLE cells that match the condition.

I have tried using a little VBA with:

Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim Cell As Range
Application.Volatile
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Else
Set Vis = Union(Vis, Cell)
End If
End If
Next Cell
End Function

and then trying the formula:
=AVERAGE(IF(Vis('Data Table'!$C$10:$C$65536)=A2,Vis('Data Table'!$BP$10:$BP$65536)))
but it does not work.

View 8 Replies View Related

Average Of Last 5 Values But To Exclude Any Blank Cells

Jul 3, 2014

I have a scenario where I have a rolling list of sales figures which get added to each week that passes.

I need a formula that will calculate the last 5 weeks of sales and generate an average - which I think I have an idea how to do.

The sticking point is that so as not to skew the averages, when there has been an exceptionally busy or quiet day for a reason we know about I exclude the sales from that week.

This then interferes with the averages as it either takes it as a zero and lowers it or seems to stop formulas from working.

So to summarise:

Average of last 5 weeks sales
Excluding any blanks
Dynamic enough to always pick up the last 5 values in the list (i.e. the last 5 weeks)

View 6 Replies View Related

AVERAGE Calculation To Exclude Blank Cells

Sep 3, 2009

I have an array formula that calculates the average of numbers between two dates:

{=AVERAGE(IF('Date Range'!$B:$B>$H$4,IF('Date Range'!$B:$B<=$B$3,'Numbers to Sum'!$C:$C)))*100}

The start date is the day after the date in H4 and the end date is in cell B3. The dates are in column B and the numbers to sum associated with these dates are in column C.

Some of the cells in column C are blank and my formula is taking these blanks into account in calculating the average, while I would prefer not to count them in the calculation.

Can someone please suggest how I could amend the formula above to accommodate this?

View 8 Replies View Related

Dynamic Named Range Drop-down List To Exclude N/A Errors?

May 7, 2014

I have a dynamic named range which allows me to have an automatically growing drop down list in B1 as more cities are added in column A. however, my workbook will have N/A errors to begin with before cities are added.

is there a way for my drop down list in B1 not to include the N/A errors and only the cities? of course when the next N/A error turns into a city, i would like the B1 drop down list to recognise this and include it.

View 4 Replies View Related

Average - Cut Out Zeros

Jul 7, 2009

Within a worksheet I have data like this in a column (actually, lots like it in many columns):

View 3 Replies View Related

Using Count Function Exclude Zeros In Count

Jul 18, 2007

I am trying to count data using several criteria

1.Need to add data from for a certain category, say "blue" + data during a certain date but exclue the ones with zeros

My formula using arrays look like this, but it is still counting data with zero in the cell as an item

=count(if(A4:A400(text by category)=”blue”,count(if(O4:O400(date)<”04/01/07”,count(An4:An400)[Actual data],-(countif(An4:An400,0))))))

View 9 Replies View Related

How Do I Get An Average Without Including The Zeros

Jan 27, 2006

I am using a formula to include all values greater than zero in my average which has worked fine until i tried to extend the parameters, then it gives me the Value? sign. Does anyone know another way to accomplish what i want?

here is what i am using now.
=average(if(BZ28:CM28<>0, BZ28:CM28, " "))

i was trying to make the CM extend to CS, but for some reason it is not working.

Anyway, i figure a different equation might be better than what i
have and solve my problem.

View 10 Replies View Related

Average Of Cells With Zeros

Aug 8, 2007

I want to take an average of only non-zero numbers in a range of cells containing a few zeros. I know there is a function to do this, but I haven't been able to find it. With the normal average() function, the denominator is too large as cells with 0 are included.

View 9 Replies View Related

Dynamic Pie Chart With No Zeros

Jul 11, 2007

Please see the attached spreadsheet, I would like to create a dynamic pie chart shows the monthly strategy breakdown (one month at a time), but not showing the zero value and also a drop down box to be able to choose which month's strategy breakdown to display on the chart.

View 9 Replies View Related

Average Over Worksheets Excluding Zeros

Dec 14, 2009

im trying to average over a number of work sheets to determine the avergage talk time of employees over a day, ive tried:

View 14 Replies View Related

Average Of Every Other Cell Excluding Zeros?

May 9, 2013

I current have an Excel doc that I am trying to pull averages from. Every row has data that needs to be averaged but I only need every other cell. And I cannot have zeros in the average because it will skew the data. Also if the average for a row ends up being 0, I want that average to show up as 0 not #DIV/0!. From Column H to EE I need to average each row individually.

I came up with the below formula.

=IF(SUM(H1:H1:J1:J1:L1:L1)=0, "0", AVERAGEIF(H1:H1:J1:J1:L1:L1, ">0")

It works but Its not practical because I have to manually enter all of the cells that it pulls from.

View 14 Replies View Related

Average While Ignoring Zeros And Errors?

Feb 14, 2013

I'm trying to return the average, min and max values of a range that will have errors, be blank or have zeros. I have already found an array formula that works getting rid of the errors but the zeros are now a problem.

{=AVERAGE(IF(NOT(ISERROR(D27:G27)),D27:G27))}

I was thinking that an OR might be needed but for the life of me can't figure out where it belongs. I will also be using this formula with both MIN and MAX in the adjacent cells so it would be great if all I have to do is swap those in for AVERAGE. I'm not keen on how array formulas work, just found an example similar to this on the interwebs, and I'm curious why if the array brackets are removed the value returned is 0 and not an error.

Workbook is proprietary so VB is locked and not an option (not sure if it was but wanted to be clear).

View 5 Replies View Related

Rolling Average That Excludes Zeros

Dec 7, 2006

I've got a worksheet that has chronological quarterly data for which I'd like to do a 5-year rolling average. The layout is: Column A = Dates, Column B = Values, and Column C = 5-year Rolling Average. My problem is that I want to exclude zero-values from the averaging calculation. I tried the following:


={AVERAGE(IF(B1,B5,B9,B13,B170,B1,B5,B9,B13,B17))}

However, Excel will not accept this formula (I assume an array must be contiguous? Please let me know if that's correct).

View 9 Replies View Related

Excluding Zeros When Calculating An Average

May 10, 2007

I have a one column of numbers...let's say consisting of six rows
the following numbers 12,0,14,25,0,9
if I average this it comes out to 10

how can I average the same rows but exclude the zeros in the outcome(is there a formula to do that?)

I would like the answer to be 15.....I still need the zero's in the rows however

View 9 Replies View Related

Average & Count Zeros But Not Blanks

Apr 19, 2006

I am trying to make a excell spread sheet that will calculate my students averages for the year. I need to account for zeros in the coarse and I want this excel sheet to track the current average all year long . So I do not need to include my blanks in the average as I go. Also , how do I formulate my average accum to show this formula - Test scores, four of them count as 80% of the total grade. So each test is worth 20% / Lab 10% of total grade and homework is additional 10%. I downloaded the templete from MSN and have tweaked it to my liking except for the coding above. Please advise. I am a below par on Excel. My attachemtn is below of my templete. One note. the templete gave me the room to include 13 ros of homework - but I will not necessarily use all of them... I can make it one row for home work only ....

View 9 Replies View Related

Sum & Average Including Zeros But Not Blanks

Aug 14, 2007

Attached is an xls with my formulas and problem. We need a way to factor in zeros in grading student workers. However, we also need a way to omit blank or null cells if the workers did not do a particular project. The formulas currently in the sheet compute zeros for both scenarios, lowering the overall 'grade' for workers who didn't do a project compared with workers who did the project but got a '0'

View 8 Replies View Related

Average Omitting Blanks / Zeros

Feb 12, 2008

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

View 8 Replies View Related

Average Function That Excludes Zeros

May 10, 2008

I am working with averages of ACT scores and Class rank percentages. I cannot figure out how to exclude all the zeros and/or DNR (do not rank) values that are in those columns. For example if I have 10 items, and 2 are DNR, the I want to exclude the 2 DNR's in my average. I tried putting it in the Row or Column area and unchecking what I wanted to exclude and then drag it into the data area, but that didn't work either.

View 2 Replies View Related

Average Skipping Cells AND Not Counting Zeros?

Oct 31, 2012

Here are my cells and values

B38 C38 D38 E38 F38 G38 H38 i38 J38 k38 L38 M38 N38 O38
0.00
None
1.95
16
1.73
21
0.00
None
20.10
20
1.73
20
0.00
None

What I would like to do is average only every other cell AND also not count zeros. So =average(B38,D38,F38,J38,L38) generally the idea not averaging H38 & N38 since they are both zeros.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved