Blank until at least 1 variable is entered, but stil calculate average

[gocolonel77]

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

Thanks

[zbor]

Try

Please Login or Register  to view this content.

[Pete_UK]

Try this:

=IF(COUNT(D4,D11,D18,D25,D32)=0,"",AVERAGE(D4,D11,D18,D25,D32))

The AVERAGE function ignores blanks.

Hope this helps.

Pete

[Fotis1991]

One way...

=(D4+D11+D18+D25+D32)/COUNTA(D4,D11,D18,D25,D32)

[gocolonel77]

Thanks guys once again you have come through for me