CODEWITHSUNDEEP
excelexcel-formula
Chris
Chris

Reputation: 23

Average formula in Excel

I'm working on a spreadsheet and I need to have a set of cells ignored if they are blank. I tried a regular average formula as well as if statements and nothing seems to be working. The values need to be adjusted so they come to a common value. Here is the formula I am trying to use to calculate the average. 

=(AVERAGE(D4,(F4*0.6),(H4*(6/7))))

This formula will calculate the average but is not ignoring cells that do not have a value.

Upvotes: 2

Views: 130

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

Excel should automatically ignore truly blank cells when using the =Average() formula. However, if you do have some "0" data or space characters, you could use the following to find the average of anything that is numeric:

=SUM(A:A)/COUNT(A:A)

Where A:A is your range.

EDIT: Simplified to use Count which only finds numeric cells.

EDIT 2: Given your specific example, the average function could look as follows:

=IFERROR((D4 + (F4*0.6) + (H4*(6/7)))/COUNTA(D4,F4,H4), 0)

Upvotes: 2

Related Questions