Minimum results

[JD]

Column A has some numbers in it, Column B has some numbers in it, and Column
C=A*B respectivily. At the bottom of column C is the Min, Max ,and Average
functions of column C i.e C31 =Min(C1:C30). Not all rows in A and B have
data but all cells in C have the formula. I don't have a problem with getting
the Average or Maximum of column C just the Minimum because it seems to think
the blank cells in column C are the minumums therefore my minimum cell (C31)
is blank even though the formula for cell C31 is there. Also I do not want
to have to go into each cell of column C and remove the formula because A & B
do not have data. Please help. Thanks

JD

 

[Max]

Try in C31, array-entered*: =MIN(IF(C1:C30>0,C1:C30))
*Press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:58
xdemechanik

 

[David Biddulph]

You may wish to distinguish between blank cells and true values of zero.

Perhaps along the lines of
=MIN(IF(C1:C30<>"",C1:C30))
And C1 to have =IF(COUNT(A1:B1)=2,A1*B1,"")

 

[JD]

Works great. Thanks Max

 

[Max]

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300, Files:360, Subscribers:58
xdemechanik

 

[ShaneDevenshire]

Hi JD,

something to think about - in column C you have formulas =A*B, if A and B
are empty, the formula in column C returns 0. The AVERAGE of blank cells and
cells with 0 are not the same. So you may need to apply the same type of
logic to this calculation as you did to MIN. The problem is the MIN returns
0 so the issue is obvious while AVERAGE returns a number so it is not obvious.