Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
Hi !
My data looks like this col1 col2 1/1/2014 species 1 2/1/2014 species 1 2/1/2014 species 2 3/1/2014 species 2 4/1/2014 species 1 8/1/2014 species 1 8/1/2014 species 3 15/1/2014 species 3 15/1/2014 species 4 21/1/2014 species 5 11/2/2014 species 1 26/2/2014 species 5 And so on... I need to figure out a way to get the time period in which I have got all the species. For eg in the above dataset, I get all 5 species by 21/1/2014. species1- 1/1/2014 species 2 - 2/1/2014 species 3 -8/1/2014.. My dataset is huge so i cannot do it manually, i basically need the period in which i get a unique list of the complete species set. Please help! Thanks in advance! Nitya |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
Hi Nitya,
Am Mon, 11 Apr 2016 21:57:41 -0700 (PDT) schrieb Nitya Satheesh: col1 col2 1/1/2014 species 1 2/1/2014 species 1 2/1/2014 species 2 3/1/2014 species 2 4/1/2014 species 1 8/1/2014 species 1 8/1/2014 species 3 15/1/2014 species 3 15/1/2014 species 4 21/1/2014 species 5 11/2/2014 species 1 26/2/2014 species 5 I hope I understood your problem. The first date for species 1: =MINIFS(A:A,B:B,"species 1") The last date similiar: =MAXIFS(A:A,B:B,"species 1") If that is not the solution, please post the expected output here. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
On Tuesday, April 12, 2016 at 10:41:15 AM UTC+5:30, Claus Busch wrote:
Hi Nitya, Am Mon, 11 Apr 2016 21:57:41 -0700 (PDT) schrieb Nitya Satheesh: col1 col2 1/1/2014 species 1 2/1/2014 species 1 2/1/2014 species 2 3/1/2014 species 2 4/1/2014 species 1 8/1/2014 species 1 8/1/2014 species 3 15/1/2014 species 3 15/1/2014 species 4 21/1/2014 species 5 11/2/2014 species 1 26/2/2014 species 5 I hope I understood your problem. The first date for species 1: =MINIFS(A:A,B:B,"species 1") The last date similiar: =MAXIFS(A:A,B:B,"species 1") If that is not the solution, please post the expected output here. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hi Claus! Thanks a lot for your reply ! What I was looking for is the entire species set. In my above dataset, my first date is 1/1/2014 where i have got my first species and the last date would be 21/1/2014 where i have got species 5. so my time period would be 21 days. i just need a period in which i get all my species. i have 5 species in the above list, so the minimum time period in which i have got all my 5 species. I hope i have explained better this time. Thanks again. Nitya |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
Hi Nitya,
Am Mon, 11 Apr 2016 22:25:55 -0700 (PDT) schrieb Nitya Satheesh: What I was looking for is the entire species set. In my above dataset, my first date is 1/1/2014 where i have got my first species and the last date would be 21/1/2014 where i have got species 5. so my time period would be 21 days. i just need a period in which i get all my species. i have 5 species in the above list, so the minimum time period in which i have got all my 5 species. if you have a new Excel version try: =MINIFS(A:A,B:B,"species 5")-MINIFS(A:A,B:B,"species 1")+1 With older versions try: =INDEX(A:A,SMALL(IF(B1:B1000="species 5",ROW(1:1000)),1))-INDEX(A:A,SMALL(IF(B1:B1000="species 1",ROW(1:1000)),1))+1 and enter the last formula with CRTL+Shift+Enter Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
On Tuesday, April 12, 2016 at 11:06:05 AM UTC+5:30, Claus Busch wrote:
Hi Nitya, Am Mon, 11 Apr 2016 22:25:55 -0700 (PDT) schrieb Nitya Satheesh: What I was looking for is the entire species set. In my above dataset, my first date is 1/1/2014 where i have got my first species and the last date would be 21/1/2014 where i have got species 5. so my time period would be 21 days. i just need a period in which i get all my species. i have 5 species in the above list, so the minimum time period in which i have got all my 5 species. if you have a new Excel version try: =MINIFS(A:A,B:B,"species 5")-MINIFS(A:A,B:B,"species 1")+1 With older versions try: =INDEX(A:A,SMALL(IF(B1:B1000="species 5",ROW(1:1000)),1))-INDEX(A:A,SMALL(IF(B1:B1000="species 1",ROW(1:1000)),1))+1 and enter the last formula with CRTL+Shift+Enter Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hi Claus! Thanks a lot!This worked!,the only thing is in my data set species 5 can come int he beginning too like how i have shown below.. col1 col2 1/1/2014 species 1 1/1/2015 species 5 2/1/2014 species 1 2/1/2014 species 3 3/1/2014 species 2 4/1/2014 species 1 8/1/2014 species 1 8/1/2014 species 3 15/1/2014 species 3 15/1/2014 species 4 21/1/2014 species 5 11/2/2014 species 1 26/2/2014 species 4 so what i need is the minimum time period in which i get all the species (species 1, species 2 , species 3 , species 4 and species 5). so here my output would be 15 days. i have got species 1 and 5 on 1/1/14, species 2 on 3/1/2014 , species 3 on 2/1/2014 and species 4 on 15/1/2014. so my time period would be between 1/1/2014 and 15/1/2014. so the minimum time period i which i get a unique list of all species. I am really sorry I haven't been able to explain it better, and thank you so much for your time. Thanks Nitya |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
On Tuesday, April 12, 2016 at 12:02:18 PM UTC+5:30, Nitya Satheesh wrote:
On Tuesday, April 12, 2016 at 11:06:05 AM UTC+5:30, Claus Busch wrote: Hi Nitya, Am Mon, 11 Apr 2016 22:25:55 -0700 (PDT) schrieb Nitya Satheesh: What I was looking for is the entire species set. In my above dataset, my first date is 1/1/2014 where i have got my first species and the last date would be 21/1/2014 where i have got species 5. so my time period would be 21 days. i just need a period in which i get all my species. i have 5 species in the above list, so the minimum time period in which i have got all my 5 species. if you have a new Excel version try: =MINIFS(A:A,B:B,"species 5")-MINIFS(A:A,B:B,"species 1")+1 With older versions try: =INDEX(A:A,SMALL(IF(B1:B1000="species 5",ROW(1:1000)),1))-INDEX(A:A,SMALL(IF(B1:B1000="species 1",ROW(1:1000)),1))+1 and enter the last formula with CRTL+Shift+Enter Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hi Claus! Thanks a lot!This worked!,the only thing is in my data set species 5 can come int he beginning too like how i have shown below.. col1 col2 1/1/2014 species 1 1/1/2015 species 5 2/1/2014 species 1 2/1/2014 species 3 3/1/2014 species 2 4/1/2014 species 1 8/1/2014 species 1 8/1/2014 species 3 15/1/2014 species 3 15/1/2014 species 4 21/1/2014 species 5 11/2/2014 species 1 26/2/2014 species 4 so what i need is the minimum time period in which i get all the species (species 1, species 2 , species 3 , species 4 and species 5). so here my output would be 15 days. i have got species 1 and 5 on 1/1/14, species 2 on 3/1/2014 , species 3 on 2/1/2014 and species 4 on 15/1/2014. so my time period would be between 1/1/2014 and 15/1/2014. so the minimum time period i which i get a unique list of all species. I am really sorry I haven't been able to explain it better, and thank you so much for your time. Thanks Nitya Is there any way I can get this output? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
Hi Nitya,
Am Tue, 12 Apr 2016 01:43:22 -0700 (PDT) schrieb Nitya Satheesh: Is there any way I can get this output? have a look: https://onedrive.live.com/redir?resi...=folder%2cxlsm for "CountOfDays" Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
On Tuesday, April 12, 2016 at 2:40:54 PM UTC+5:30, Claus Busch wrote:
Hi Nitya, Am Tue, 12 Apr 2016 01:43:22 -0700 (PDT) schrieb Nitya Satheesh: Is there any way I can get this output? have a look: https://onedrive.live.com/redir?resi...=folder%2cxlsm for "CountOfDays" Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hi Claus, So sorry for the late reply and thank you for your result.The code you have given me calculates the minimum period for each species. What I needed was the minimum period in which i get all the species together.I am so sorry i haven't been able to explain this better. Is there anyway i can modify the formula you have given me so that it encompasses the whole set of species? Thank you, Nitya |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
Hi Nitya.
Am Thu, 14 Apr 2016 02:45:54 -0700 (PDT) schrieb Nitya Satheesh: So sorry for the late reply and thank you for your result.The code you have given me calculates the minimum period for each species. What I needed was the minimum period in which i get all the species together.I am so sorry i haven't been able to explain this better. Is there anyway i can modify the formula you have given me so that it encompasses the whole set of species? try: =SMALL(A2:A100,SUMPRODUCT((B2:B100<"")/COUNTIF(B2:B100,B2:B100&""))*2)-SMALL(A2:A100,1)+1 Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
Hi Nitya,
Am Thu, 14 Apr 2016 12:10:00 +0200 schrieb Claus Busch: try: =SMALL(A2:A100,SUMPRODUCT((B2:B100<"")/COUNTIF(B2:B100,B2:B100&""))*2)-SMALL(A2:A100,1)+1 or use a Pivot table. That's more reliable. Have another look in OneDrive Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
On Thursday, April 14, 2016 at 4:12:12 PM UTC+5:30, Claus Busch wrote:
Hi Nitya, Am Thu, 14 Apr 2016 12:10:00 +0200 schrieb Claus Busch: try: =SMALL(A2:A100,SUMPRODUCT((B2:B100<"")/COUNTIF(B2:B100,B2:B100&""))*2)-SMALL(A2:A100,1)+1 or use a Pivot table. That's more reliable. Have another look in OneDrive Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hi claus, thank you so much for the result and sorry for the late reply again. I couldn't find it in one drive, I will try using a pivot table. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
Hi Nitya,
Am Mon, 25 Apr 2016 23:28:33 -0700 (PDT) schrieb Nitya Satheesh: thank you so much for the result and sorry for the late reply again. I couldn't find it in one drive, I will try using a pivot table. sorry, I load the file into a wrong folder. Please look he https://onedrive.live.com/redir?resi...=folder%2cxlsm for "CountOfDays" Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
data analysis
On Tuesday, April 26, 2016 at 12:20:45 PM UTC+5:30, Claus Busch wrote:
Hi Nitya, Am Mon, 25 Apr 2016 23:28:33 -0700 (PDT) schrieb Nitya Satheesh: thank you so much for the result and sorry for the late reply again. I couldn't find it in one drive, I will try using a pivot table. sorry, I load the file into a wrong folder. Please look he https://onedrive.live.com/redir?resi...=folder%2cxlsm for "CountOfDays" Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Thanks Claus, that worked perfectly! Thank you for your time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Analysis disappears from the Data ribbon for no reason | Excel Discussion (Misc queries) | |||
Excel 2002 Analysis ToolPak Regression Analysis Help Requested | Excel Worksheet Functions | |||
Analysis Toolpak-Confidence Level and data analysis questions | Excel Worksheet Functions | |||
Why " data analysis plus " override " data analysis " once instal. | Excel Worksheet Functions | |||
Analysis ToolPak installed but no Data Analysis option | Excel Discussion (Misc queries) |