# Take Average Eliminating Blank And Zero Cells

Mar 7, 2014Can I take average in a row ignoring blank cells and zero values.

View 3 RepliesCan I take average in a row ignoring blank cells and zero values.

View 3 RepliesADVERTISEMENT

I am using windows 7 64bit sp1 and excel 2010 32bit sp1

I am trying to fix this formula (it takes a range in a unique column, and multiple row:

Code:

=IFERROR(INDEX($B$1:$B$6,SMALL((IF(LEN($B$1:$B$6),ROW(INDIRECT("1:"&ROWS($B$1:$B$6))))),ROW(A1)),1),"")

from here

I want to make it work on my array in a row, like this: (i need to remove the blank cells and the output have to be still in a row)

value1

empty cell

empty cell

value 2

value 3

empty cell

output:

value1

value2

value3

I need a function becouse I have dynamic table with something like 10k rows and 50 columns.

I have 5 columns and 1000 rows of data,

All cells has formulas, resulting blank or value,

I need a formula to copy only the rows with values

building another column (list) with no blank cells

Is it possible without macro?

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.

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

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

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?

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

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.

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.

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

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

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 RelatedI'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).

I have a formula that is dependent upon a column of cells containing text. Cells within this column randomly have an additional space (" ") following the words. With this invisible space, the formula doesn't work as intended.

Since I have an extremely long list of names, is there a way to easily remove additional spaces after words without manually going through each cell and deleting them?

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.

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

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.

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.

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

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

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 RelatedI 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 Relatedsumif 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.

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.

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 RelatedI 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 RelatedTrying to manipulate basketball statistics copied from websites ... each cell has a leading space before the number(s) ... is there any way to globally eliminate the leading space from the cells

Tried find/replace, finding [space] replacing with 0 (which would then be disregarded ... didn't work

Tried converting all cells to number format, didn't work

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