#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Analysis disappears from the Data ribbon for no reason Giles Excel Discussion (Misc queries) 0 November 18th 09 09:24 PM
Excel 2002 Analysis ToolPak Regression Analysis Help Requested MH Excel Worksheet Functions 1 February 28th 09 08:16 AM
Analysis Toolpak-Confidence Level and data analysis questions MH Excel Worksheet Functions 0 January 3rd 09 07:15 PM
Why " data analysis plus " override " data analysis " once instal. Alfred H K Yip Excel Worksheet Functions 1 March 20th 05 09:10 AM
Analysis ToolPak installed but no Data Analysis option Eric Stephens Excel Discussion (Misc queries) 3 February 2nd 05 10:17 PM


All times are GMT +1. The time now is 09:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"