Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly Totals on sheet
I have a Worksheet that will contain a set of stock transactions. Sometimes
there will be multiple lines (transactions) per day, sometime none at all. On a second worksheet I would like to total the gains/losses by week with Sunday being the first of the week. Any ideas? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly Totals on sheet
I would be inclined to use a pivot table. Place your cursor in the middle of
the data set and select Data - Pivot Table. Follow the wizard that comes up (or just hit finish as the defaults will probably be correct). Drag quotes to the middle and place the dates in the left column. You will probably want to group based on the dates every 7 days. -- HTH... Jim Thomlinson "Jerry" wrote: I have a Worksheet that will contain a set of stock transactions. Sometimes there will be multiple lines (transactions) per day, sometime none at all. On a second worksheet I would like to total the gains/losses by week with Sunday being the first of the week. Any ideas? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly Totals on sheet
Thanks Jim ,
But I would like to stay away from pivots if at all possible. I would like to take the idea perhaps commerically (Keep it simple for the user) and would like to keep the summary on the second "tab". "Jim Thomlinson" wrote: I would be inclined to use a pivot table. Place your cursor in the middle of the data set and select Data - Pivot Table. Follow the wizard that comes up (or just hit finish as the defaults will probably be correct). Drag quotes to the middle and place the dates in the left column. You will probably want to group based on the dates every 7 days. -- HTH... Jim Thomlinson "Jerry" wrote: I have a Worksheet that will contain a set of stock transactions. Sometimes there will be multiple lines (transactions) per day, sometime none at all. On a second worksheet I would like to total the gains/losses by week with Sunday being the first of the week. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly Totals on sheet
How about using sum product formulas? Anything you can create with a pivot
you can create with sumproduct... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Jerry" wrote: Thanks Jim , But I would like to stay away from pivots if at all possible. I would like to take the idea perhaps commerically (Keep it simple for the user) and would like to keep the summary on the second "tab". "Jim Thomlinson" wrote: I would be inclined to use a pivot table. Place your cursor in the middle of the data set and select Data - Pivot Table. Follow the wizard that comes up (or just hit finish as the defaults will probably be correct). Drag quotes to the middle and place the dates in the left column. You will probably want to group based on the dates every 7 days. -- HTH... Jim Thomlinson "Jerry" wrote: I have a Worksheet that will contain a set of stock transactions. Sometimes there will be multiple lines (transactions) per day, sometime none at all. On a second worksheet I would like to total the gains/losses by week with Sunday being the first of the week. Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly Totals on sheet
That should work like a champ.
Wow - great function. I haven't used that one before, but I will now "Jim Thomlinson" wrote: How about using sum product formulas? Anything you can create with a pivot you can create with sumproduct... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Jerry" wrote: Thanks Jim , But I would like to stay away from pivots if at all possible. I would like to take the idea perhaps commerically (Keep it simple for the user) and would like to keep the summary on the second "tab". "Jim Thomlinson" wrote: I would be inclined to use a pivot table. Place your cursor in the middle of the data set and select Data - Pivot Table. Follow the wizard that comes up (or just hit finish as the defaults will probably be correct). Drag quotes to the middle and place the dates in the left column. You will probably want to group based on the dates every 7 days. -- HTH... Jim Thomlinson "Jerry" wrote: I have a Worksheet that will contain a set of stock transactions. Sometimes there will be multiple lines (transactions) per day, sometime none at all. On a second worksheet I would like to total the gains/losses by week with Sunday being the first of the week. Any ideas? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly Totals on sheet
Sumproduct would be the slow way to do it and the more formulas you used the
slower it would get - and not necessary in this case. =Sumif(A:A,"=01/01/2006",B:B)-Sumif(A:A,"01/07/2006",B:B) would be more efficient. -- Regards, Tom Ogilvy "Jerry" wrote in message ... That should work like a champ. Wow - great function. I haven't used that one before, but I will now "Jim Thomlinson" wrote: How about using sum product formulas? Anything you can create with a pivot you can create with sumproduct... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Jerry" wrote: Thanks Jim , But I would like to stay away from pivots if at all possible. I would like to take the idea perhaps commerically (Keep it simple for the user) and would like to keep the summary on the second "tab". "Jim Thomlinson" wrote: I would be inclined to use a pivot table. Place your cursor in the middle of the data set and select Data - Pivot Table. Follow the wizard that comes up (or just hit finish as the defaults will probably be correct). Drag quotes to the middle and place the dates in the left column. You will probably want to group based on the dates every 7 days. -- HTH... Jim Thomlinson "Jerry" wrote: I have a Worksheet that will contain a set of stock transactions. Sometimes there will be multiple lines (transactions) per day, sometime none at all. On a second worksheet I would like to total the gains/losses by week with Sunday being the first of the week. Any ideas? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly Totals on sheet
Tom -
so even if I copy that down 52+ rows and change the date, you think it might be faster? "Tom Ogilvy" wrote: Sumproduct would be the slow way to do it and the more formulas you used the slower it would get - and not necessary in this case. =Sumif(A:A,"=01/01/2006",B:B)-Sumif(A:A,"01/07/2006",B:B) would be more efficient. -- Regards, Tom Ogilvy "Jerry" wrote in message ... That should work like a champ. Wow - great function. I haven't used that one before, but I will now "Jim Thomlinson" wrote: How about using sum product formulas? Anything you can create with a pivot you can create with sumproduct... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Jerry" wrote: Thanks Jim , But I would like to stay away from pivots if at all possible. I would like to take the idea perhaps commerically (Keep it simple for the user) and would like to keep the summary on the second "tab". "Jim Thomlinson" wrote: I would be inclined to use a pivot table. Place your cursor in the middle of the data set and select Data - Pivot Table. Follow the wizard that comes up (or just hit finish as the defaults will probably be correct). Drag quotes to the middle and place the dates in the left column. You will probably want to group based on the dates every 7 days. -- HTH... Jim Thomlinson "Jerry" wrote: I have a Worksheet that will contain a set of stock transactions. Sometimes there will be multiple lines (transactions) per day, sometime none at all. On a second worksheet I would like to total the gains/losses by week with Sunday being the first of the week. Any ideas? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly Totals on sheet
It would be significantly faster than doing the same thing with sumproduct.
If you are queasy about use A:A, then don't use it. Use A1:A100 and B1:B100 if you wish - same as you would with sumproduct (of course these are example ranges, but it is always amazing how some people take things literally). -- Regards, Tom Ogilvy "Jerry" wrote in message ... Tom - so even if I copy that down 52+ rows and change the date, you think it might be faster? "Tom Ogilvy" wrote: Sumproduct would be the slow way to do it and the more formulas you used the slower it would get - and not necessary in this case. =Sumif(A:A,"=01/01/2006",B:B)-Sumif(A:A,"01/07/2006",B:B) would be more efficient. -- Regards, Tom Ogilvy "Jerry" wrote in message ... That should work like a champ. Wow - great function. I haven't used that one before, but I will now "Jim Thomlinson" wrote: How about using sum product formulas? Anything you can create with a pivot you can create with sumproduct... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Jerry" wrote: Thanks Jim , But I would like to stay away from pivots if at all possible. I would like to take the idea perhaps commerically (Keep it simple for the user) and would like to keep the summary on the second "tab". "Jim Thomlinson" wrote: I would be inclined to use a pivot table. Place your cursor in the middle of the data set and select Data - Pivot Table. Follow the wizard that comes up (or just hit finish as the defaults will probably be correct). Drag quotes to the middle and place the dates in the left column. You will probably want to group based on the dates every 7 days. -- HTH... Jim Thomlinson "Jerry" wrote: I have a Worksheet that will contain a set of stock transactions. Sometimes there will be multiple lines (transactions) per day, sometime none at all. On a second worksheet I would like to total the gains/losses by week with Sunday being the first of the week. Any ideas? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weekly Totals on sheet
literally
Yep - I would have been in that camp for a while at least ;-) Thanks Tom "Tom Ogilvy" wrote: It would be significantly faster than doing the same thing with sumproduct. If you are queasy about use A:A, then don't use it. Use A1:A100 and B1:B100 if you wish - same as you would with sumproduct (of course these are example ranges, but it is always amazing how some people take things literally). -- Regards, Tom Ogilvy "Jerry" wrote in message ... Tom - so even if I copy that down 52+ rows and change the date, you think it might be faster? "Tom Ogilvy" wrote: Sumproduct would be the slow way to do it and the more formulas you used the slower it would get - and not necessary in this case. =Sumif(A:A,"=01/01/2006",B:B)-Sumif(A:A,"01/07/2006",B:B) would be more efficient. -- Regards, Tom Ogilvy "Jerry" wrote in message ... That should work like a champ. Wow - great function. I haven't used that one before, but I will now "Jim Thomlinson" wrote: How about using sum product formulas? Anything you can create with a pivot you can create with sumproduct... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Jerry" wrote: Thanks Jim , But I would like to stay away from pivots if at all possible. I would like to take the idea perhaps commerically (Keep it simple for the user) and would like to keep the summary on the second "tab". "Jim Thomlinson" wrote: I would be inclined to use a pivot table. Place your cursor in the middle of the data set and select Data - Pivot Table. Follow the wizard that comes up (or just hit finish as the defaults will probably be correct). Drag quotes to the middle and place the dates in the left column. You will probably want to group based on the dates every 7 days. -- HTH... Jim Thomlinson "Jerry" wrote: I have a Worksheet that will contain a set of stock transactions. Sometimes there will be multiple lines (transactions) per day, sometime none at all. On a second worksheet I would like to total the gains/losses by week with Sunday being the first of the week. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create weekly totals | Excel Worksheet Functions | |||
Weekly Totals Based on Dates | Excel Worksheet Functions | |||
with weekly score sheet how do I column a weekly progressive aver. | Excel Worksheet Functions | |||
Summing Weekly Totals into Monthly Totals | Excel Worksheet Functions | |||
How can I subtotal my weekly totals by months? | Excel Worksheet Functions |