Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using VLookup, Displacement, SumIF to total and average
Hello all - I am trying to set up a spreadsheet that will track the performance of several people selling several different products. Each day, it will track the number of a certain product sold, and the profit, for many products. In the end, I would like to be able to analyze each salesman by product, against the group, for a date range etc.... In column A I have the date In column B I have profits product 1 In column C I have # sold Product 1 In column D I have profits product 2 In column E I have # sold Product 2 Trying to explain this the best I can... On a seperate sheet"Overview", I have a list of all the products. I would like to be able to enter the tab name in a cell and specify a date range, and have the results for those products populate on this "overview" tab. I'm having a problem with the vlookup and the conditional sum, formulas. Any help you could offer would be great. I will check back often to give details if necessary. Thanks much, Paul -- Paul987 ------------------------------------------------------------------------ Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850 View this thread: http://www.excelforum.com/showthread...hreadid=488794 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using VLookup, Displacement, SumIF to total and average
First, I'd rearrange my data.
Column A Date B Salesman C product d Qtysold e profit F DateOk Then I'd use a pivottable. But first I'd add one more column to the mix. One that returned true/false if the date was ok. =and(a2=date(2005,11,1),a2<=date(2005,11,30)) and drag down. (Give the header a nice "OkDate" title) Then I'd select the range and data|Pivottable Follow the wizard until you get to a dialog with "Layout" on it. click that layout button drag that okdate to the Page field Drag the salesman to the row field drag the product to the column field (more than 250'ish could cause trouble) drag the numbers (sold/profits) to the data field. and finish up. If you want to read more about pivottables... Here are a few links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Paul987 wrote: Hello all - I am trying to set up a spreadsheet that will track the performance of several people selling several different products. Each day, it will track the number of a certain product sold, and the profit, for many products. In the end, I would like to be able to analyze each salesman by product, against the group, for a date range etc.... In column A I have the date In column B I have profits product 1 In column C I have # sold Product 1 In column D I have profits product 2 In column E I have # sold Product 2 Trying to explain this the best I can... On a seperate sheet"Overview", I have a list of all the products. I would like to be able to enter the tab name in a cell and specify a date range, and have the results for those products populate on this "overview" tab. I'm having a problem with the vlookup and the conditional sum, formulas. Any help you could offer would be great. I will check back often to give details if necessary. Thanks much, Paul -- Paul987 ------------------------------------------------------------------------ Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850 View this thread: http://www.excelforum.com/showthread...hreadid=488794 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using VLookup, Displacement, SumIF to total and average
Dave Peterson - you are the man. I greatly under estimated the power of the mighty pivot table. It worked out well, and the date column solved the date range problem. Thanks for the help, you saved me hours of work. Paul -- Paul987 ------------------------------------------------------------------------ Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850 View this thread: http://www.excelforum.com/showthread...hreadid=488794 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using VLookup, Displacement, SumIF to total and average
Wow. That was fast. Glad you got it working.
Paul987 wrote: Dave Peterson - you are the man. I greatly under estimated the power of the mighty pivot table. It worked out well, and the date column solved the date range problem. Thanks for the help, you saved me hours of work. Paul -- Paul987 ------------------------------------------------------------------------ Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850 View this thread: http://www.excelforum.com/showthread...hreadid=488794 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting minutes and seconds to calculate a total average | Excel Worksheet Functions | |||
Pivot Table -- Add column to average Grand Total | Excel Worksheet Functions | |||
Pivot Table - Average of counts | Excel Worksheet Functions | |||
Average of count in pivot tables | Excel Worksheet Functions | |||
Limit or Exclude cells in Average and Sum formula | Excel Worksheet Functions |