Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
index question
Thanks Max, i was looking for this formula
please give me solution if in col. c1,c4& c5 having value <=0 then i need answer for all three it would be 1&4&5 can it possible. Thanks find the payment number (column A) where the ending balance is <=0 Assuming data as posted in cols A to C In say, E2, normal ENTER to confirm: =INDEX(A2:A100,MATCH(TRUE,INDEX(C2:C100<=0,),0)) Adapt to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
index question
Your scenario is different. This easy-to-understand formulas set-up (a potent
Criteria - "Extract n Pack-it-up" pair) will extract the desired results w/o the concatenation in a matter of seconds. Might appeal to you. Source data in cols A & C, from row1 down In D1: =IF(C1="","",IF(C1<=0,ROW(),"")) In E1: =IF(ROW()COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROW()) )) Copy D1:E1 down to cover the max expected extent of source data, say down to E100? Minimize col C. The results that you seek from col A will automatically appear neatly packed up at the top in col E. Col D = Criteria (devote your attention here to build-in all requirements) Col E = Extract n Pack-it-up (standard) Click YES below to celebrate .. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "ADIB" wrote: Thanks Max, i was looking for this formula please give me solution if in col. c1,c4& c5 having value <=0 then i need answer for all three it would be 1&4&5 can it possible. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
index question
Typo
.. Minimize col C Should be: Minimize col D -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
index question
Thanks again Max
"Max" wrote: Your scenario is different. This easy-to-understand formulas set-up (a potent Criteria - "Extract n Pack-it-up" pair) will extract the desired results w/o the concatenation in a matter of seconds. Might appeal to you. Source data in cols A & C, from row1 down In D1: =IF(C1="","",IF(C1<=0,ROW(),"")) In E1: =IF(ROW()COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROW()) )) Copy D1:E1 down to cover the max expected extent of source data, say down to E100? Minimize col C. The results that you seek from col A will automatically appear neatly packed up at the top in col E. Col D = Criteria (devote your attention here to build-in all requirements) Col E = Extract n Pack-it-up (standard) Click YES below to celebrate .. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "ADIB" wrote: Thanks Max, i was looking for this formula please give me solution if in col. c1,c4& c5 having value <=0 then i need answer for all three it would be 1&4&5 can it possible. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
index question
Welcome
-- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "ADIB" wrote in message ... Thanks again Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index Question | Excel Worksheet Functions | |||
Question on index | Excel Discussion (Misc queries) | |||
INDEX / MOD() question.. | Excel Discussion (Misc queries) | |||
Index() & Row() Question | Excel Discussion (Misc queries) | |||
Index question | Excel Worksheet Functions |