grouping in a pivottable

liorsitkovsky

New Member
Joined
Sep 15, 2006
Messages
9
I am sorry I am posting the same question for the second time but i think i wasn't clear enough so i have included a line from my data

SYMBOL PRICE SHARES GATEWAY PER_SHARE DATE TIME
SIRI 4.3000 300 ARCA .0000 08/01/ 09:13

There are more columns but for simplicity I included only this. There are about 12000 rows of data. I am able to group all fields in my pivot table but not the TIME field. I can group it manually but the implication of that is that when I had new data it is not included in my time grouping.
I am interested to group it into 3 time intervals (9:30 to 11:30, 11:30 to 14:00, and 14: to 16:00). When I right click on the TIME filed in the pivot table I get an error saying I can't group this field. On the other hand I am able to group my DATE field into months, days, years etc.
I can group it manually but then when new data is added the time of the trades is excluded from the groups. This is important to me because I am trying to get my trading performance based on time of day. Any suggestions please. I am desperate and tried everything by myself so far.

Lior
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
Lior,

I have answered your previous post on the same subject. Since you want to group your data into three zones of time, I suggest you include a formula based column in your pivot and use the Vlookup function with the True argument to segregate your times into 3 zones.

Your Vlookup table can be setup as

Time Zone
09:30:00 09:30 to 11:30
11:30:01 11:30 to 14:00
14:00:01 14:00 to 16:00

Doing a Vlookup(cell reference, Data Table as given above, 2, True) will segregate your times in the required zone. You can then drag and drop this newly created column of data into your pivot.

Hope this helps..
 

Forum statistics

Threads
1,136,879
Messages
5,678,327
Members
419,756
Latest member
vincent86kapelski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top