| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Min Value
hello there,
xxx 0 yyy 45 yyy 70 xxx 5 xxx 0 70 --=SUMPRODUCT(MAX((A2:A6=A3)*(B2:B6))) the above formula is the MAX for yyy, how can i edit the above formula to get the MIN value from the list but not zero if column A is yyy. im expecting an answer of 45 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min Value
Try,
=MIN(IF(A2:A6=A3,IF(B2:B60,B2:B6))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "EricBB" wrote: hello there, xxx 0 yyy 45 yyy 70 xxx 5 xxx 0 70 --=SUMPRODUCT(MAX((A2:A6=A3)*(B2:B6))) the above formula is the MAX for yyy, how can i edit the above formula to get the MIN value from the list but not zero if column A is yyy. im expecting an answer of 45 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min Value
thank you mike..
it worked but it is possible to make it not an array formula? "Mike H" wrote: Try, =MIN(IF(A2:A6=A3,IF(B2:B60,B2:B6))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "EricBB" wrote: hello there, xxx 0 yyy 45 yyy 70 xxx 5 xxx 0 70 --=SUMPRODUCT(MAX((A2:A6=A3)*(B2:B6))) the above formula is the MAX for yyy, how can i edit the above formula to get the MIN value from the list but not zero if column A is yyy. im expecting an answer of 45 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min Value
If the min value for yyy is 0 do you want to exclude that value?
A non-array formula will be rather convoluted! -- Biff Microsoft Excel MVP "EricBB" wrote in message ... thank you mike.. it worked but it is possible to make it not an array formula? "Mike H" wrote: Try, =MIN(IF(A2:A6=A3,IF(B2:B60,B2:B6))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "EricBB" wrote: hello there, xxx 0 yyy 45 yyy 70 xxx 5 xxx 0 70 --=SUMPRODUCT(MAX((A2:A6=A3)*(B2:B6))) the above formula is the MAX for yyy, how can i edit the above formula to get the MIN value from the list but not zero if column A is yyy. im expecting an answer of 45 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Min Value
Also, what if all the associated values with yyy are 0 (if that's even
possible?)? -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... If the min value for yyy is 0 do you want to exclude that value? A non-array formula will be rather convoluted! -- Biff Microsoft Excel MVP "EricBB" wrote in message ... thank you mike.. it worked but it is possible to make it not an array formula? "Mike H" wrote: Try, =MIN(IF(A2:A6=A3,IF(B2:B60,B2:B6))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter. Mike "EricBB" wrote: hello there, xxx 0 yyy 45 yyy 70 xxx 5 xxx 0 70 --=SUMPRODUCT(MAX((A2:A6=A3)*(B2:B6))) the above formula is the MAX for yyy, how can i edit the above formula to get the MIN value from the list but not zero if column A is yyy. im expecting an answer of 45 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
