|
How do I bin numbers by a date in an Excel sheet
I have imported the site vistor data into a spreadsheet. I wish to sort the
dates by number of visits for each day. So I would probably end up counting the visits for each day. What function in Excel allows me to do this? |
Assuming your data is something like 1-jun-05 25 2-jun-05 45 3-jun-05 18 and so on Simply select both the columns, go to sort, and sort on column B Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=379969 |
If the visitor sheet has multiple entries for each date, because each person
signs in with the day, try using a pivot table with count in your data area and date as a row heading. Or, if one line in the visitor book can have more than 1 visitor (e.g. a group is signed in), use sum in the data area. The pivot table can also be sorted by decreasing number of visitors. "Natarajan Ganesan" wrote: I have imported the site vistor data into a spreadsheet. I wish to sort the dates by number of visits for each day. So I would probably end up counting the visits for each day. What function in Excel allows me to do this? |
The vistor data is in a single column in Y/M/D format. I am kind of stuck
here. There are 800 such entries. A sorting function helps me only partially. After this I wish to count number of entries for each date "mangesh_yadav" wrote: Assuming your data is something like 1-jun-05 25 2-jun-05 45 3-jun-05 18 and so on Simply select both the columns, go to sort, and sort on column B Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=379969 |
the column just has entries in YMD format. I can change and sort the format.
Beyond this, I need to count how many entries are there for each date. Below is a portion of the data 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 14-Jun-05 14-Jun-05 "TRE" wrote: If the visitor sheet has multiple entries for each date, because each person signs in with the day, try using a pivot table with count in your data area and date as a row heading. Or, if one line in the visitor book can have more than 1 visitor (e.g. a group is signed in), use sum in the data area. The pivot table can also be sorted by decreasing number of visitors. "Natarajan Ganesan" wrote: I have imported the site vistor data into a spreadsheet. I wish to sort the dates by number of visits for each day. So I would probably end up counting the visits for each day. What function in Excel allows me to do this? |
Did you try the count functions. Also sumproduct could help, somthing like =SUMPRODUCT(--(A1:A200="17-Jun-05"),B1:B200) please check with the date format, or you could use DATE(2005,6,5) instead of "17-Jun-2005" Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=379969 |
Sorry. I've been unable to get onto this site for a few days. If you put a
Heading in the cell above your data column, you can still use a pivot pretty quickly. Just drag Heading into the Row Area and Heading again into the Data Area, using the Count function as your aggregation. This will give you something that looks like: Count of Dates Dates Total 14-Jun-05 2 15-Jun-05 12 16-Jun-05 25 17-Jun-05 7 Grand Ttl 46 Cheers, Trent "Natarajan" wrote: the column just has entries in YMD format. I can change and sort the format. Beyond this, I need to count how many entries are there for each date. Below is a portion of the data 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 14-Jun-05 14-Jun-05 "TRE" wrote: If the visitor sheet has multiple entries for each date, because each person signs in with the day, try using a pivot table with count in your data area and date as a row heading. Or, if one line in the visitor book can have more than 1 visitor (e.g. a group is signed in), use sum in the data area. The pivot table can also be sorted by decreasing number of visitors. "Natarajan Ganesan" wrote: I have imported the site vistor data into a spreadsheet. I wish to sort the dates by number of visits for each day. So I would probably end up counting the visits for each day. What function in Excel allows me to do this? |
Sorry. I've been unable to get onto this site for a few days. If you put a
Heading in the cell above your data column, you can still use a pivot pretty quickly. Just drag Heading into the Row Area and Heading again into the Data Area, using the Count function as your aggregation. This will give you something that looks like: Count of Dates Dates Total 14-Jun-05 2 15-Jun-05 12 16-Jun-05 25 17-Jun-05 7 Grand Ttl 46 "Natarajan" wrote: the column just has entries in YMD format. I can change and sort the format. Beyond this, I need to count how many entries are there for each date. Below is a portion of the data 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 17-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 16-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 15-Jun-05 14-Jun-05 14-Jun-05 "TRE" wrote: If the visitor sheet has multiple entries for each date, because each person signs in with the day, try using a pivot table with count in your data area and date as a row heading. Or, if one line in the visitor book can have more than 1 visitor (e.g. a group is signed in), use sum in the data area. The pivot table can also be sorted by decreasing number of visitors. "Natarajan Ganesan" wrote: I have imported the site vistor data into a spreadsheet. I wish to sort the dates by number of visits for each day. So I would probably end up counting the visits for each day. What function in Excel allows me to do this? |
| All times are GMT +1. The time now is 05:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com