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
ADVERTISEMENT
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
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
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
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
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
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 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
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
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
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
Dec 2, 2008
I`m working on a compression of a picture. For most of you that probably is pea-nuts, but I really just stopped wearing diapers when it comes to excel. At this point I have a sheet in front of me with 300 rows (length of the picture) and 185 columns (breadth of the picture). The cells are pixels and contain values ranging from 0 to 255.
I now need to do an operation I simply can't figure out. I need to select four cells at once (A1:A2 and B1:B2) and replace A1 by the average of these cells and the other three cells should be emptied during that operation. This operation I should do for the whole sheet. In the end I should see a chart with values in every two cells (A1, A3, A5 ... for the first row) as the other cells will be emptied
View 9 Replies
View Related
Jan 23, 2007
In my Case Else, I have it set to take the Average of the values in cells M8 for all sheets other than the ones listed in my other Cases, same for M9, etc. for each worksheet I have.
The change I want to make is as follows: I'd like it say say if the value in Cell M8 is the same on all sheets then place x value in cell M8 on the active sheet, same for M9, on so on and so forth. Only if the values in Cell M8 for each sheet are not the same then take the average.
Private Sub CommandButton1_Click()
Dim sh As Worksheet, sum As Double, n As Long
n = 0
sum = 0
Dim arr() As Variant
Dim nbrOfAvgs As Long, i As Long
nbrOfAvgs = 19
i = 0 ....................
View 9 Replies
View Related
Apr 19, 2007
my main goal is come up with an average for a certain range of values...but if a cell equals "continuation" then do not count it's corresponding numerical value towards the average. i've attached a sample of the problem.
View 2 Replies
View Related
Oct 27, 2008
=IF($M272="DUPLICATES";SUMIF($D:$D;$D272;F:F);"") and
=IF($M272="DUPLICATES";SUMIF($D:$D;$D272;G:G);"")
it looks up duplicate values in D:D
well It considers #s to be duplicate too and then makes the summation
How can I exclude symbols like that # from the sum???
View 11 Replies
View Related
Aug 16, 2006
All I would like is to put the result of this Excel formula in the D column:
=TEXT(B10-C10,"mm:ss")
starting from row 10, provided that both B and C for the row exist (are nonblanks).
View 9 Replies
View Related
Oct 5, 2013
I have three columns named "name","start_date" and "end_date"
ex:
Name start_date end_date
AB 9/11/13 10:19 AM ??
CD 9/12/13 11:45 AM ??
All i need to find is,
If Name = AB, i want put start_date+3 days in the end_date column,
similarly for name = CD , start_date+7 days.
But the calculation should exculde weekends(sat and sun).
ex: For AB, start_date = 10/3/13 (3rd oct , thrusday), end_date should be start_date+3 ,so end_date = 10/6/13 but 6th october is sunday so the formula should give end_date as 10/7/13.
View 4 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
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
Mar 24, 2014
I have a problem and I found a good explanation of something similar here:
[URL]
In the original posting the following formula solves the problem (attached) TestData.xlsx
B1: =AVERAGE(INDEX(B3:B100,MATCH(TRUE,B3:B100<>0,0)):INDEX(B3:B100,MATCH(TRUE,B3:B100<>0,0)+4))
What I would like to return is the average of the 1st 5 values, but what if a number of cells after the first value were also blank
View 7 Replies
View Related
Mar 7, 2014
Can I take average in a row ignoring blank cells and zero values.
View 3 Replies
View Related
Mar 3, 2004
I am doing an average of a column of cells. How do I tell excel to ignore the blank cells in the column and not calculate them in the average?
View 9 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
Aug 15, 2007
I have a column of data that contains various blank cells where no data was measured. In the adjacent column I want to take the moving average of the last 4 data points including the most recent entry. My problem is i do not know how to handle blank cells where there was no data. I need it to average the last four in the column where data acutally exists. I am ok with using helper cells if needed and I am not worried about the first four results at this time.
Example data
A..................B
15
50
25
20................55
Blank............55
30................31.25
35................27.5
blank............27.5
blank............27.5
15................25
10................22.5
15................18.75
40................20
blank.............20
View 10 Replies
View Related
Jul 10, 2008
I've got a spreadsheet that I do every month with columns of numbers that I average. This sheet has to match about 10 others similar. The columns are divided by Weekdays, Saturdays, Sundays. But some months there are no entries for certain cells on Saturday or Sunday.
I thought that if I just used the Average function, it would dismiss and not count the blank cells. Alas, apparently not. I've highlighted in yellow the one column that I'm really having trouble with.
View 7 Replies
View Related
Oct 24, 2011
I am trying to get a rolling 8wk avg of a large group of data. I am trying to take the avg of 8 vlookups:
=AVERAGE(VLOOKUP($A$9,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-7,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-14,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-21,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-28,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-35,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-42,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-49,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE))
However, in some cases, the cell to be looked-up may be blank. Using the formula above, the result of these vlookups is "0". I want to take the average of these vlookups excluding the blanks from the 'Raw Data' sheet.
View 6 Replies
View Related
Sep 10, 2012
I have a sequence of 40 columns of data, representing 10 weeks worth of events with 4 values per week
I need to average the first pair for each week, ignoring zeros and blanks, and also separately the 2nd pair of cells, across the whole 40 columns
I am trying to come up with a neater solution which takes into account the zeros, at the moment I have the following which does not, for row 34
=IF($AR34>0,ROUND(AVERAGE(D34:E34,H34:I34,L34:M34,P34:Q34,T34:U34,
X34:Y34,AB34:AC34,AF34:AG34,AJ34:AK34,AN34:AO34)*2,2),0)
I could use SUM, COUNT COUNTIF etc but its gonna get messy
Using EXCEL 2010
View 5 Replies
View Related
Jul 25, 2013
Is it possible to show "0" zero in the total average column without inputing zeros in the blank cells in row B3:E3 & B4:E4? There are months we receive no boat & RV orders, so those months wll be zero most of the time. Instead of having #DIV/0! it show 0, without having to input zero in cells to compute the average.
A
B
C
D
E
F
1
Jan
Feb
Mar
April
Total Average
[Code] .......
View 1 Replies
View Related
May 26, 2009
Here is the formula I am using however it interprets the blank cells as 0% and includes it in the average. To make it more difficult there will be some 0%'s.
View 4 Replies
View Related
