CODEWITHSUNDEEP
excelexcel-formula
DuduArbel
DuduArbel

Reputation: 1178

Average including blank cells

I would like to average a range but some of the cells are blanks. The cells should remain blanks, is there a way of including them in an AVERAGE?

For example, if I have 2 cells, one is empty and the other is 100, I want the average to be 50

How can I do this?

Upvotes: 6

Views: 13625

Answers (3)

Ravi
Ravi

Reputation: 84

You could also Array Formulas Type

=AVERAGE(0+A1:A2)

and Press Ctrl + Shift and Enter

Upvotes: 3

Then Enok
Then Enok

Reputation: 661

If you have a column that is never blank (like name of a person) then:

=SUM(B1:B100)/COUNT(A1:A100)

Where B is the column to average and A is the name column (never blank). Careful as some people tend to leave A1 and B1 as descriptions of the column and if that is the case with you then use A2:A100 and B2:B100 otherwise row 1 will be counted to the average.

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

If your values are in A1:A2 (for example), try this:

=SUMPRODUCT(AVERAGE(0+A1:A2))

it forces your blank values to be converted to 0 before taking the average

Upvotes: 4

Related Questions