Another zero question


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
 
Ad

Advertisements

D

Dav

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
 
G

Guest

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
 
Ad

Advertisements

G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top