Another zero question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Earlier I posted a question about ignoring zero values, Well when I posted
that it helped, I had not fully tried the suggestion with zeros and > zero
values. Well here is what I have in the cell that is giving me the problem.
=MIN(D162:D181,D136:D155,D110:D129,D84:D103,D58:D77,D32:D51,D6:D25) many of
these cells contain a 0 (Zero) value from another formula so I cannot just
leave them blank. I tried
=MIN(If(D162:D181,D136:D155,D110:D129,D84:D103,D58:D77,D32:D51,D6:D25>0,D162:D181,D136:D155,D110:D129,D84:D103,D58:D77,D32:D51,D6:D25))
But that doesn't work either, any suggestions?

Thanks
 
Not a complete answer but the following give the correct answer if each
of your 7 ranges contains a value greater than 0, otherwise if any of
the ranges doesnot it returns 0

if entered as an array crt sht enter

=MIN(MIN(IF(D162:D181>0,D162:D181)),MIN(IF(D136:D155>0,D136:D155)),MIN(IF(D110:D129>0,D110:D129)),MIN(IF(D84:D103>0,D84:D103)),MIN(IF(D55:D77>0,D55:D77)),MIN(IF(D32:D51>0,D32:D51)),MIN(IF(D6:D25>0,D6:D25)))

I am still thinking on it

Regards

Dav
 
You could use the DMIN function but your cells have to be next to each other.
Perhaps you can create on another part of the spreadsheet links to your
cells. Example in cell A1 have = D162, in cell A2 have = D181, in cell A2
have = D136. Then run the DMIN function off of cells A1, A2, A3, etc.

=DMIN(A1:A13,A1,B1:B2)

Where B1:B2 has your criteria (column heading in B1 and >0 in B2).

Hope this helps.

Bill Horton
 
Thanks,

This formula worked, with the exception I couldn't do all 7 ranges in one
formula, I had to sum each array and then set results into 7 cells next to
each other then min the results.
 
Back
Top