Average in Cells

[martins]

I am trying to work out a formula which calculates the average of cells in the following scenario -

Columns F3 to F5 contain formulas which calculate the "days delay" based on figues entered in cells A3/A5 & E3/E5; I would like to calculate the Average of F3 to F5 but only where figures are returned in cells F3/F5 e.g. if all 3 cells are populated you would divide by 3 but I need the formula to recognize that if figures are only present in 2 of the cells, it divides by 2 to get the average.

Hope this makes sense

[SAP PoD]

I think I understand.

Use the COUNT function to see how many cells are completed (per row) - then divide your total by this new columns value.

Example, if your data is in A1 to E1 and your total in F1

=F1\(COUNT(a1:e1))

[oldchippy]

I am trying to work out a formula which calculates the average of cells in the following scenario -

Columns F3 to F5 contain formulas which calculate the "days delay" based on figues entered in cells A3/A5 & E3/E5; I would like to calculate the Average of F3 to F5 but only where figures are returned in cells F3/F5 e.g. if all 3 cells are populated you would divide by 3 but I need the formula to recognize that if figures are only present in 2 of the cells, it divides by 2 to get the average.

Hope this makes sense

Hi martins,

Try this in F6

=SUM(F3:F5)/COUNTIF(F3:F5,"<>0")

this will exclude zero values

oldchippy