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


ADVERTISEMENT

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

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

VBA Code To Exclude Cells That Are Blank

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

Finding Maximum Value (but Exclude Blank Cells)

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

Get Autofilter To Exclude Blank Cells That Contain Formulas

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

Exclude Blank Cells While Plotting A Graph

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

Advanced Filter: Exclude Blank/Empty Cells Criteria

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

Average Next 5 Non Blank Values Of A Row

May 11, 2009

I would like cell B2 to show the average of the next 5 non blank values in row 2 (C2-Z2). The problem is there are varying blank cells (non data points) depending on each row. So i need the forumula to account for just the first 5 immediate non blank cells.

View 14 Replies View Related

Average Based On Last 7 Values (Non-blank / Zero)?

Aug 6, 2014

Book7.xlsx

The workbook is attached.

Columns B,C,D,E have the desired result in row 18.

What I want the formula to do is starting from row 13, go up and average the next 7 values that are not blank. If its easier, the starting point could be row 4 and I could adjust my data.

View 2 Replies View Related

How To Get Average Of First 3 Right Non Blank Cells

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

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

How To Get Average Of First 5 Non-Blank Cells In A Column

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

Take Average Eliminating Blank And Zero Cells

Mar 7, 2014

Can I take average in a row ignoring blank cells and zero values.

View 3 Replies View Related

Ignore Blank Cells In A Average

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

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

Moving Average Ecluding Blank Cells

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

Average Function And Not Count The Blank Cells

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

Average Of VLookups Ignoring Blank Cells

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

Excel 2010 :: Average Non-Blank Or Zero Cells

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

Compute Average Formula With Blank Cells

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

Formula To Average Numbers Leaving Out Blank Cells

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

Prevent #DIV/0 Error In Average When All Cells Are Blank/Empty

May 19, 2008

I'm looking for a function that will display the average of a row of cells, while at the same time not displaying any error messages. It's easy to average cells without blank values, but to combine that with no errors is difficult for me. I saw many ways to do the average, one of which is:

= SUM(A1:E1)/COUNTIF(A1:E1,">0")

That function doesn't work for a row of blank cells (i.e., hidden rows), though. The result is an error message.

I also read about a way to ignore an error in a computation:

=IF(ISERROR(F1),"",F1)

The problem is when I combine those functions I get a blank cell no matter which function I put first, and without regards to cell values or not. The reason I want this to be error-free is that I have to average the "average column" at the bottom of the table, too (i.e., F100).

View 9 Replies View Related

Limiting Filters To Exclude Blank Columns

May 24, 2013

I have a spreadsheet with data in every other column (i.e. A, C, E, etc.). I am using the "filter" option in Excel on the aforementioned range of cells although the little filter icon shows up on all columsn, including the blank columns in between my data. Is there a way to only have the filters show up on the columns with data?

View 2 Replies View Related

Exclude Blank Date Fields For Vaildation

May 2, 2006

I am using the standard validation from the tool bar. What I am trying to do is in
check the date in a cell and if the new date is Greater and or equal to another date in another cell. The problem is I am having is when the cell that I am checking for a date is Blank the validation does not work and one can put in a date. I tried unchecking the "Ingore Blank"

Spreadsheet example attached.

View 6 Replies View Related

Average Of Every N Cells With Overlapping Values

May 29, 2014

I need to get the average of every 6 cells, but the first value of each group of 6 is the last of the previous group. I'd also like the output to be displayed beside the last value in each group. i.e.

CELL B7 =AVERAGE(A2:A7)
CELL B12 =AVERAGE(A7:A12)
CELL B17 =AVERAGE(A12:A17)
etc.

I saw this "=AVERAGE(OFFSET(A$1,(ROW()-ROW(B$1))*10,,10,))" in another post which seems similar but how to modify it.

View 4 Replies View Related







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