Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minimum array formula.
Can anyone help me please.
I am trying to find the minimum figure (excluding zeros) from a column using an array formula. My formula is as follows: =MIN(IF(A10:A300=Title,IF(B10:B3000,C10:C300,0),0 )) The figures in column c are formulas driven from another tab, and the title is a drop down list to choose job titles. For some reason my formula always returns a minimum of zero when zero's are present. Hope this makes sense. Can anyone help, it's driving me insane????? Many thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minimum array formula.
Don't use the 0s as the conditional returns:
=MIN(IF(A10:A300=Title,IF(B10:B3000,C10:C300))) HTH, Bernie MS Excel MVP "Confused_Euffy" wrote in message ... Can anyone help me please. I am trying to find the minimum figure (excluding zeros) from a column using an array formula. My formula is as follows: =MIN(IF(A10:A300=Title,IF(B10:B3000,C10:C300,0),0 )) The figures in column c are formulas driven from another tab, and the title is a drop down list to choose job titles. For some reason my formula always returns a minimum of zero when zero's are present. Hope this makes sense. Can anyone help, it's driving me insane????? Many thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minimum array formula.
try this
=MIN(IF(A10:A300="Title",IF(B10:B3000,C10:C3000, ),)) "Confused_Euffy" wrote: Can anyone help me please. I am trying to find the minimum figure (excluding zeros) from a column using an array formula. My formula is as follows: =MIN(IF(A10:A300=Title,IF(B10:B3000,C10:C300,0),0 )) The figures in column c are formulas driven from another tab, and the title is a drop down list to choose job titles. For some reason my formula always returns a minimum of zero when zero's are present. Hope this makes sense. Can anyone help, it's driving me insane????? Many thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minimum array formula.
Try this:
=MIN(INDEX(10^10-(A10:A300="Title")*(B10:B3000)*(10^10-C10:C300),)) Just press Enter "Confused_Euffy" wrote: Can anyone help me please. I am trying to find the minimum figure (excluding zeros) from a column using an array formula. My formula is as follows: =MIN(IF(A10:A300=Title,IF(B10:B3000,C10:C300,0),0 )) The figures in column c are formulas driven from another tab, and the title is a drop down list to choose job titles. For some reason my formula always returns a minimum of zero when zero's are present. Hope this makes sense. Can anyone help, it's driving me insane????? Many thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minimum array formula.
Thank you Bernie, this worked perfectly.
Thank you to Mike and Teethless mama as well, but I must admit I couldn't get either of those to work, but thank you anyway. It may be that I haven't paid enough attention to them. "Bernie Deitrick" wrote: Don't use the 0s as the conditional returns: =MIN(IF(A10:A300=Title,IF(B10:B3000,C10:C300))) HTH, Bernie MS Excel MVP "Confused_Euffy" wrote in message ... Can anyone help me please. I am trying to find the minimum figure (excluding zeros) from a column using an array formula. My formula is as follows: =MIN(IF(A10:A300=Title,IF(B10:B3000,C10:C300,0),0 )) The figures in column c are formulas driven from another tab, and the title is a drop down list to choose job titles. For some reason my formula always returns a minimum of zero when zero's are present. Hope this makes sense. Can anyone help, it's driving me insane????? Many thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Minimum array formula.
Hi,
You might also try =MIN(IF((A10:A300=Title)*(B10:B3000),C10:C300)) Entered as an array If this helps please click the Yes button Cheers, Shane Devenshire "Confused_Euffy" wrote: Can anyone help me please. I am trying to find the minimum figure (excluding zeros) from a column using an array formula. My formula is as follows: =MIN(IF(A10:A300=Title,IF(B10:B3000,C10:C300,0),0 )) The figures in column c are formulas driven from another tab, and the title is a drop down list to choose job titles. For some reason my formula always returns a minimum of zero when zero's are present. Hope this makes sense. Can anyone help, it's driving me insane????? Many thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Minimum Excluding Text | Excel Discussion (Misc queries) | |||
Minimum Formula | Excel Discussion (Misc queries) | |||
How to lookup the minimum, 2nd minimum and 3rd minimum......... | Excel Worksheet Functions | |||
Minimum Distance Calculation using Array and Geographical Coordinates | Excel Discussion (Misc queries) | |||
Finding minimum value across selected rows of an array | Excel Worksheet Functions |