Average with blank cells

[dine9]

I really am stuck need to average the answers across a row sometimes these will be blank, but they are alternate eg, h5,j5,l5,n5,r5 need to get a accurate average figure.if somebody can help i am new to excel. I am using 2007. Thanks in advance.

 

[kanton]

When using the AVERAGE function, empty cells (or blank cells) are not counted. Anything with a zero value is though. (type "average'" into Help on Excel and it explains further).

So, taking this into account, and assuming your 'blank' cells really are blank, you can average by the following:

=AVERAGE(H5:R5)

Of course, anything from H5 to R5 gets averaged (unless, as I stated, it is truely blank).

Hope this helps.

 

[baitmaster]

welcome to the board

you can get around this by going back to first principles of averaging - i.e. average = sum divided by count

sum(cell1:cell2) will be correct

but count(cell1:cell2) can be wrong in certain circumstances, such as when the formula returns "" (which annoyingly, is not the same as an empty cell). Instead, you could replace this with e.g. countif(cell1:cell2,<>"") or countif(cell1:cell2,">0")

 

[AlphaFrog]

=SUM(h5,j5,l5,n5,r5) / 5

If you have column headers for the answers in say row 1 maybe something like "Answer 1, Answer 2, Answer 3... etc

=SUMIF($1:$1, "*Answer*", 5:5) / COUNTIF($1:$1, "*Answer*")

 

[dine9]

they work but if a number is inputed between the rows it gives a different answer. sorry i cant give a example as the information on the sheet is confidential.

 

[kanton]

Couldn't you name the range with the specific cells you want averaged then average them that way, or are you not always averaging the same cells?

 

[dine9]

tried =IF(COUNTIF(H4:J4:L4:N4:P4:R4,">0")=0,"",SUM(H4,J4,L4,N4,P4,R4)/COUNTIF(H4:J4:L4:N4:P4:R4,">0"))
but it does the same.

 

[AlphaFrog]

Excel 2007 also has two new functions called AVERAGEIF and AVERAGEIFS

=AVERAGEIF( Range, Criteria, Average Range)

=AVERAGEIFS ( Average_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

 

[dine9]

Thanks for the help will try them.

 

[Aladin Akyurek]

I really am stuck need to average the answers across a row sometimes these will be blank, but they are alternate eg, h5,j5,l5,n5,r5 need to get a accurate average figure.if somebody can help i am new to excel. I am using 2007. Thanks in advance.

Control+shift+enter, not just enter:

=AVERAGE(IF(MOD(COLUMN(H5:R5)-COLUMN(H5),2)=0,IF(ISNUMBER(H5:R5),H5:R5)))

This assumes that you also want to include P5 in the average.