# 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
ADVERTISEMENT
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
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
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
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
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
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
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
Jan 13, 2014

Ok so my named range looks like this:

[Code]....

However, I want to ignore the "" cells and the truly blank cells... However, I think all of them will have "" since I have this formula in all of the ones I'm putting in the range:

[Code]....

How do I go about getting these results into a named range so I can use it on validation since validation only seems to ignore truly blank cells and not the "" ones.

View 4 Replies
View Related
Jun 20, 2014

So in Cell K12 there is a rather large formula (I condensed it for this example).

This formula is to average out the respective cells in column J. For each cell there is a possibility of 9 different entries. NRT, N/O, 1,2,3,4,5,6,7

So based on the formula in K12 I need Cell J12 to Display the answer.

if any one of the Cells referenced in the formula are NRT then I need it to Display NRT. Which the Formula does now.

The problem comes in when a Cell is N/O (Not Observed)

How do I get the formula to Ignore N/O currently I have it set up to recognize N/O as 0. But excel averages 0 in and it affects the answer.

I know in a simple formula I can add <>0 to the formula to ignore zeros. How to write it into a bigger formula such as in K12.

For example the current numbers in Cells J17:J25 should average 5 however the formula averages it as 1 because it calculates N/O as 0.

View 14 Replies
View Related
May 28, 2009

I was done but the formula works great except if the cell is zero or blank I don't want it to include that cell in the average. in this case it still counts.

=IF(SUM(H32>0,H67>0,H102>0,H137>0)=0,0,SUM(H32,H67,H102,H137)/SUM(H32>0,H67>0,H102>0,H137>0))

View 3 Replies
View Related
Jun 7, 2013

Cell D39 has the formula

=(D4+D11+D18+D25+D32)/5

I want the cell to remain blank until at least on variable is entered, but then I want it to calculate the average of only the cells that have a variable in them.

Just so I am clear

D4 = 20

D11 = 50

D 18 = BLANK

D25 = BLANK

D32 = BLANK

Average would be 35

View 4 Replies
View Related
Aug 30, 2006

I have a list of 400 cells all in column A. Is there a way to have XL divide them up so that 100 are in column A, 100 in column B, etc., while still treating them like a single column (e.g. Sort will sort them all together)?

View 8 Replies
View Related
Jun 5, 2007

I need to get a formula to calculate the average of the best 3 scores out of 4, but there is some that do not have a value in a cell (so some are only out of 3 scores not 4) and if i simply drop the lowest value and sum the rest, it will incorrectly calculate the average.

View 9 Replies
View Related
May 1, 2008

sumif problem but it wont work with a countif or average if.

Column A has various names and Column B has amounts, what I need is to count the number of occurances "John Smith" has an amount in Column B. The previous formula I tried was

=sumif(A:A,"John Smith",B:B) but with either countif or averageif it errors too many arguements.

I wasn't sure if Dcount or an array would be suitable but have not used them before.

Pivot tables I'm sure will be the future with this but haven't got to the foot of that mountain yet.

View 4 Replies
View Related
Mar 13, 2013

I have searched and trying to manipulate various formulas with no success...

I am entering an AverageIF formula into cell J15

I want to calculate the average of the folllowing ranges cells J5:J8,J10:J13

However, I want the cell J15 to display blank if cell J14=0

On reflection I am unsure an AverageIF formula is even correct.

View 5 Replies
View Related
Aug 2, 2014

I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.

View 8 Replies
View Related
Apr 17, 2008

I have sporadic cell values in a column. I will be describing the lamen logic I use to fill in these blanks manually. Find the first nonblank cell in the column, then look for the next nonblank cell in the column. I count the number of blank cells in between. If even # of blank cells, then I give the first half of the blanks the value of the first nonblank cell and the second half of the blank cells the value of the next nonblanck. If odd # of blank cells, then I do the same with the exception that the odd cell that falls in the middle will be randomly designated the value of the either first or second nonblank cell.

View 4 Replies
View Related
Jul 15, 2008

I can count the blank cells withiin a range using

=COUNTBLANK(C6:AD2506)

But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.

It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.

View 14 Replies
View Related
Mar 12, 2014

I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.

View 8 Replies
View Related
Mar 22, 2014

I presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.

What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).

This all needs to be done in Arial, 10pt, white.

View 10 Replies
View Related
Feb 21, 2006

Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting

to count nonblanks. However, the data was imported from Access and Oracle,

and Excel treats what appear to be blank cells as nonblanks. I've tested

this theory by highlighting a couple of "blank" cells and deleting them, and

my count changes. So, can I get Excel to put a value into my "blank" cells,

so then I could filter it out, or create a formula that would only count

dates in my columns (which is what I'm after).

This is what I'm looking at:

A B C

1 2/4/2006 2/6/2006 ("blank")

2 ("blank") 12/13/2005 1/7/2006

3 2/20/2006 1/15/2006 ("blank")

In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,

3 for B and 1 for C.

View 14 Replies
View Related