Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another zero question
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:D7 7,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:D250,D162:D181,D136:D155,D110:D12 9,D84:D103,D58:D77,D32:D51,D6:D25)) But that doesn't work either, any suggestions? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another zero question
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:D1810,D162:D181)),MIN(IF(D136:D1 550,D136:D155)),MIN(IF(D110:D1290,D110:D129)),MI N(IF(D84:D1030,D84:D103)),MIN(IF(D55:D770,D55:D7 7)),MIN(IF(D32:D510,D32:D51)),MIN(IF(D6:D250,D6: D25))) I am still thinking on it Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=535562 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another zero question
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 "seedy3" wrote: 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:D7 7,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:D250,D162:D181,D136:D155,D110:D12 9,D84:D103,D58:D77,D32:D51,D6:D25)) But that doesn't work either, any suggestions? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Another zero question
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. "Dav" wrote: 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:D1810,D162:D181)),MIN(IF(D136:D1 550,D136:D155)),MIN(IF(D110:D1290,D110:D129)),MI N(IF(D84:D1030,D84:D103)),MIN(IF(D55:D770,D55:D7 7)),MIN(IF(D32:D510,D32:D51)),MIN(IF(D6:D250,D6: D25))) I am still thinking on it Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=535562 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding and deleting question marks | Excel Discussion (Misc queries) | |||
Summary Page Question | Excel Discussion (Misc queries) | |||
Pivot table question | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions |