Formulas in linked sheets

[KevinE]

Hi guys,

I have 3 sheets feeding data into a master sheet, say columns A, B & C
with the calculations being done in D. The problem is with the COUNT or
COUNTIF functions and the zeros that occur if all cells aren't filled
because of the reference back to a blank cell in one of the feeder
sheets. Hiding the zeros does not work as these functions still seem to
pick up the reference to the other sheet.

What I'm trying to achieve is.....
a) only one cell with data - use that figure,
b) only 2 cells with data - return the higher figure,
c) all 3 cells with data - return the average of the 2 highest
figures.

I have created the following formula but it doesn't seem to
work when there are only 2 figures. I think the problem is with SMALL,
but I'm not sure how to make it disregard the zeros.

=IF(COUNTIF($A1:$A3,">0.0")=1,$A1,(SUM($A1:$A3)-(SMALL($A1:$A3,1)))/(COUNTIF($A1:$A3,">0.0")-1))

Any ideas?

regards,
Kevin

 

[Frank Kabel]

Hi Kevin
one way try
=IF(COUNTIF($A1:$A3,">0.0")>2,(MAX($A1:$A3)+LARGE($A1:$A3,2))/2,MAX($A1
:$A3))


HTH
Frank