 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / August 2007dates max & min
hi all, please help A B --------- ---------- date item --------- ---------- 02/14/04 azm 07/22/05 naf 11/21/05 naf 03/26/06 naf 04/18/07 naf 04/20/07 azm 05/19/07 azm 06/08/07 ktm 08/22/07 ktm i like to find the number of days between max and min dates for a given item in column B This is an array formula that must be entered using ctrl+shift+enter vs just enter. ans=1190 =MAX(IF(B6:B14="azm",A6:A14))-MIN(IF(B6:B14="azm",A6:A14))  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > hi all, please help > [quoted text clipped - 15 lines] > item > in column B Works fine Don, in my case our subject (number of days) is just a part of a bigger formula, how to overcome using an arry formula. Thanks > This is an array formula that must be entered using ctrl+shift+enter vs just > enter. ans=1190 [quoted text clipped - 19 lines] > > item > > in column B > Works fine Don, > in my case our subject (number of days) is just a part of a bigger > formula, how to overcome using an arry formula. Give this non-array formula a try... =SUMPRODUCT(MAX((B2:B10=C2)*(A2:A10)))+SUMPRODUCT(MAX((B2:B10=C2)*(99999-A2:A10)))-99999 where I assumed the dates started in A2, the code items in B2 and the specified code item you wanted to calculate the date difference for in C2. Rick > > Works fine Don, > > in my case our subject (number of days) is just a part of a bigger > > formula, how to overcome using an arry formula. Thanks Rick, just perfect that's does it, many thanks again > Give this non-array formula a try... > [quoted text clipped - 4 lines] > > Rick Try this. Kind of tricky in that the max and min are DIFFERENT. =SUMPRODUCT(MAX((B6:B14="azm")*(A6:A14)))-SUMPRODUCT(MIN(B6:B14="azm",A6:A14)) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Works fine Don, > in my case our subject (number of days) is just a part of a bigger [quoted text clipped - 25 lines] >> > item >> > in column B I do not get the 'min' part of your formula to produce the correct result. Yes, for the data given and for item "azm" it works, but that appears to be because of the particular layout of the data. Try changing "azm" to "naf" to see the problem. Rick > Try this. Kind of tricky in that the max and min are DIFFERENT. > =SUMPRODUCT(MAX((B6:B14="azm")*(A6:A14)))-SUMPRODUCT(MIN(B6:B14="azm",A6:A14)) [quoted text clipped - 29 lines] >>> > item >>> > in column B Actually, I wondered why it would work. Sometimes I forget to FULLY test. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >I do not get the 'min' part of your formula to produce the correct result. >Yes, for the data given and for item "azm" it works, but that appears to be [quoted text clipped - 36 lines] >>>> > item >>>> > in column B many thanks Don, this formula does not yield an ok numbers for the given data , may be because of the min part of it , so please fully test and revert. > Try this. Kind of tricky in that the max and min are DIFFERENT. > =SUMPRODUCT(MAX((B6:B14="azm")*(A6:A14)))-SUMPRODUCT(MIN(B6:B14="azm",A6:A14)) [quoted text clipped - 28 lines] > >> > item > >> > in column B Just wondering aloud why array-entering your combined formula (ie after combining the original array suggested into your other, presumably non-array formula) didn't work for you. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- galdly yes, there is a non array formula solved my issue, please refer to Rick Rothstien's suggested non array formula above, it's just great excelFan > Just wondering aloud why array-entering your combined formula (ie after > combining the original array suggested into your other, presumably non-array > formula) didn't work for you. Assume source data within A2:B10, dates in A2:A10, items in B2:B10 Assume unique items are listed in D2 down, viz: azm, naf, etc In E2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula): =MAX(IF(B$2:B$10=$D2,A$2:A$10))-MIN(IF(B$2:B$10=D2,A$2:A$10)) Format E2 as general/number. Copy down. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > hi all, please help > [quoted text clipped - 14 lines] > i like to find the number of days between max and min dates for a given item > in column B |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.