Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula To Bring The Best Name 2
I posted a question yesterday & got a good reply from Jacob & now I can't
find it. The ? was:- I have a worksheet that has 9 suppliers names in (C4:L4), there costs for a item in (C19:L19) I want to bring the cheapest supplier name into cell M4. Jacob replied:- =INDEX(C4:L4, MATCH(MIN(C19:L19),C19:L19,0)) I now need to exclude the $0.0000 values in C19:L19 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula To Bring The Best Name 2
I now need to exclude the $0.0000 values in C19:L19
Try this... =INDEX(C4:L4,MATCH(SMALL(C19:L19,COUNTIF(C19:L19,0 )+1),C19:L19,0)) -- Biff Microsoft Excel MVP "peterh" wrote in message ... I posted a question yesterday & got a good reply from Jacob & now I can't find it. The ? was:- I have a worksheet that has 9 suppliers names in (C4:L4), there costs for a item in (C19:L19) I want to bring the cheapest supplier name into cell M4. Jacob replied:- =INDEX(C4:L4, MATCH(MIN(C19:L19),C19:L19,0)) I now need to exclude the $0.0000 values in C19:L19 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula To Bring The Best Name 2
Use the following array formula:
=INDEX(C4:L4, MATCH(MIN(IF(C19:L19<0,MAX(C19:L19)+1,C19:L19)),C1 9:L19,0)) This assumes that there is at least one value in C19:L19 that is greater than zero. This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. You do not type in the braces -- Excel puts them in automatically. The formula will not work properly if you do not enter it with CTRL SHIFT ENTER. For much more information about array formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx. On Wed, 20 Jan 2010 13:06:01 -0800, peterh wrote: I posted a question yesterday & got a good reply from Jacob & now I can't find it. The ? was:- I have a worksheet that has 9 suppliers names in (C4:L4), there costs for a item in (C19:L19) I want to bring the cheapest supplier name into cell M4. Jacob replied:- =INDEX(C4:L4, MATCH(MIN(C19:L19),C19:L19,0)) I now need to exclude the $0.0000 values in C19:L19 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula To Bring The Best Name | Excel Worksheet Functions | |||
Formula to bring in the header | Excel Worksheet Functions | |||
How does one use a formula to bring over the formatting as well | Excel Worksheet Functions | |||
formula to bring value from another sheet with different spaces | Excel Discussion (Misc queries) | |||
Formula to bring up to # but not over | Excel Discussion (Misc queries) |