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
ADVERTISEMENT
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
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
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
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
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
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
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
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
View Related
May 3, 2009
I have this calculation which works fine if all cells have a value. But it will happen that cells in the range are empty.
=SUMPRODUCT(--(1*LEFT('Courses input'!C4:D6,1)-C3<=0), --(1*MID('Courses input'!C4:D6,3,1)>=2))/E3
What can i do to exclude empty cells in the range from calculation?
View 10 Replies
View Related
Feb 3, 2014
Is there a way to do a Formula (SUM) but exclude cells with invalid values? I would like to add up all values in attached spreadsheet for cells A14 to A28 and again for A49 to A63, as well as get an average C-14 to 28 and again for C49-63.
As some of the cells contain text rather than numbers, the formula doesn't work. how to exclude these cells?
View 4 Replies
View Related
Jun 20, 2014
I currently have a code that for combines cells within a range together and places them into another cell.
I want the code to only include cell that have a value in and exclude those within the range that are blank.
I have included the code below:
[Code] .....
View 2 Replies
View Related
Mar 8, 2012
Say I have the following:
Cell A1: male
Cell A2: male
Cell A3: female
Cell A4: male
I want to conduct a search using the value in cell B1, and return the row numbers for which the value in B1 exists.
So my formula in C1 is:
=small(if(isnumber(search($B$1, $A$1:$A$A4)), row($A$1:$A$A4)), row(1:1))
and I make it an array by CTRL+SHIFT+enter
So in B1 I type "male" (without the quotes). From C1 to C4 the values populate as 1,2,3,4
However, I only want the rows where "male" exists, in other words, i want an exact match and I want to exclude those cells that have "female"
I'm wanting:
C1 = 1
C2 = 2
C3 = 4
C4 = blank
Is there a way to do this?
View 9 Replies
View Related
Jun 14, 2013
E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).
E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.
I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.
View 14 Replies
View Related
Mar 21, 2014
I am writing a formula for finding a maximum out of three average values. The cells that will be populated with data are A1 to A5; B1 to B5; C1 to C5. The final cell where I want to write the formula needs to show the maximum of the three averages (average of data in each row).
The difficulty is that sometimes only the A and B rows are populated (and sometime only A row is populated) and if B and/or C are blank, the formula fails and shows "#DIV/0!".
How can I write so that if C is blank, then evaluate only A and B data. And If both B and C are blank, then just display the average of row A.
View 3 Replies
View Related
Jan 31, 2014
I have a column that contains combined data of numbers and letters. I want to filter that column to exclude cells that contain the letter "d" or "f". I am trying to create a pivot table from the results of the filter to only display the ID #s that do not contain the letters "f" or "d".
The data inside the fields is not the same length and is not listed in a particular order. I have over 14,000 records I need to filter.
Data set example: The items in red are an example of the cells I would like to exclude from my worksheet. Is there a way to filter without deleting the data?
445-0706634F946
5886-K949-D001
445-0716622F946
445-0706634F887
497-0480473
445-9995967F025
6569-9788-2342
9016-1300
9016-C765-875
123-S765-87645
View 1 Replies
View Related
Dec 4, 2006
I have a single column of data that is, perhaps, 100 rows long.
I have ticks, crosses and 'N/A' in various cells within this column. There are no cells that are blank.
I want to count the number of ticks within the column and want to be able to say:
If the number of ticks is = 100% then it's green.
If the number is >75% and <100% then it's amber.
If the number <75% then it's red.
I also want to exclude the N/A cells from the % calculation.
View 9 Replies
View Related
Feb 3, 2012
I have the following line in a macro:
Range("b22:L" & Range("J" & Rows.Count).End(xlUp).Row).AutoFilter
This selects rows based on whether they contain data and creates an autofilter of the range.
The data Im using this on will have blank cells as the bottom rows but they will contain formulas that blank the cells based on ISNA() conditions,
How do I get the above code to only select cells with visible data and ignore those that are blank but contain formulas.
View 9 Replies
View Related
May 14, 2013
I am trying to finish conditionally formatting a tracking spreadsheet. If column O contains a zero, I want the row highlighted. The problem I can't stop is the enpty cells are being treated as a zero. The formula I am using is =$O3=0 the range it applies to is =$A$#:$P$92
View 2 Replies
View Related
Aug 29, 2013
How can i exclude Blank or cells that have a 0 when plotting my graph? Right now everything is very small because i have about 50 cells, some with quantities and some with zero quantities.
View 9 Replies
View Related
Aug 6, 2014
Any way to exclude colored cells from addition in a column. I will attach a simple example document.
I am building a spreadsheet to keep track of my monthly spending, and debt balance. I keep track of the total amount of bills I pay each month. As I pay them, their respective line is highlighted green in the spreadsheet. I would like to create a macro/formula which would sum the remaining cells in the column which are not highlighted to display the remaining monies which need to be paid out. I'm not sure if this matters but I am using office 2010.
billSample.xlsx
View 4 Replies
View Related
Sep 11, 2006
Im using an advanced filter that uses the following criteria
Days Late Note(s) Note(s)
>90 <>*agreement* <>*QTR*
This shows all data over 90 that do not incl the words agreement or QTR in a column marked Note(s).
My problem is that I do not want to show records that are Null in the Note(s) column.
Note(s)
<> Does not work (possibly because it is text and not Numbers)
as this does work if used on records that contain numbers.
View 9 Replies
View Related
Jul 17, 2014
What I want to do is Average out a range of cells "F4:F343". That's the easy part =average(F4:F343). However I want to exclude any numbers for that average if data in coloumn "Q" = "FMC"
Would that look anything like:
=average(F4:F343)/countif(Q4:Q343, "<>FMC")
I'm also displaying it as minutes in a day so i added *24*60 to the end of the formula and formatted to Number/.00. I've entered the formula and it will calculate but when i enter FMC to the appropriate area the average wont change.
View 2 Replies
View Related
Aug 31, 2012
I have a macro which autofilters a range of cells.
I can count the number of visible rows by using the following code
Code:
lcountActive = Range("BC34:BC" & x).SpecialCells(xlCellTypeVisible).Count
What I would like to do is both calculate the Average value of the visible cells and the Sum as well. I have tried to adapt the code I have but my knowledge is just not good enough to do this.
View 1 Replies
View Related
Sep 22, 2009
I have 1300 vertical cells.
Is there a quick way to get the average of every ten cells? i.e. to end up with 130 averages of successive groups of ten cells (cell 1-10, 11-20, 21-30 etc.)?
View 2 Replies
View Related
Apr 25, 2014
I am having a dilemma on getting the average for the first 3 non blank cells. This is how my data looks like :
excel average.jpg
I don't know any formula that will disregard the non blank cell and will get the average of the firs 3 non blank cells from the right.
View 2 Replies
View Related
Feb 9, 2009
I have imported a text file using a macro. And I want to calculate the average of two columns, and put this into a new one. But how to do this and let the macro stop if one of the two input values is blank?
ActiveCell.FormulaR1C1 = "=(RC[-3]+RC[-2])/2)" but then when I use the autofill i get a lot of zero's, but I can't delete them using a macro.
View 14 Replies
View Related
Jan 15, 2010
Columns L, M and N are always fully populated.
Columns D, E and F may have some missing values, along with H, I and J.
Lets just concentrate on D.
I need to create a formula where I get the anser in C1 This is an average of L only where D has a value.
View 3 Replies
View Related
Jul 19, 2012
I want to be able to find the last non empty row in column C and then average the last three inputs. Thus utilzing this as a means for a projection of the remaining periods left in the year. All for projecting figures for the remainder of the year.
Bill
Period
Date
Amount
1
1/6/2012
3982.76
[Code] ..........
View 2 Replies
View Related
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
