Results 1 to 11 of 11
Thread: VLOOKUP (?) (2000)

20030310, 20:10 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
VLOOKUP (?) (2000)
I have a spreadsheet with several columns of data. Column B represents an amount of a commodity I purchase on a given day (column A is the date, with the possibility of multiple purchases on the same date). Column C represents the location where I purchase the commodity, and column D the price. I would like to summarize on one line (say at the top of the spreadsheet before any purchases are listed) the total of purchases at each location for the month (i. e., the locations in column C), and the average price of the purchases for that month.
Any help?

20030310, 20:58 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: VLOOKUP (?) (2000)
Add a column that gives the month
=month(date)
for example in E4:
=Month(A4)
Now use the pivot table wizard to make a table.
row = location
column = month
data = sum of amount and average of price
Steve

20030310, 21:01 #3
 Join Date
 Jun 2001
 Location
 New York, New York, Lebanon
 Posts
 1,449
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: VLOOKUP (?) (2000)
<img src=/S/hello.gif border=0 alt=hello width=25 height=29> jlkirk
Would you like to use a Pivot Table for this project, or subtotaling, or maybe some VBA to really do multiple things all at once?
I would prefer the Pivot Table, so check it out in the OnLine Help, and if you need any help let us know.
Post an example, with factious data no doubt; workbook and we'll try to get you a starting point.
Wassim<img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

20030310, 21:07 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: VLOOKUP (?) (2000)
Steve,
I have never used pivot tables before. What say I put together a smaller version of my worksheet, and let you walk me through itok?
Thanks,
Jeff

20030311, 09:08 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: VLOOKUP (?) (2000)
Steve,
Here is a sample month, with a section at the top of the worksheet where I would like to have the results appear.
Thanks for your help.
Jeff

20030311, 09:38 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VLOOKUP (?) (2000)
Jeff,
If filling in the yellow section at the top is all you want, it is relatively easy; you don't need a pivot table for that.
To calculate the total volumes per location in C3:C6, you can use a SUMIF formula. For example, the formula in cell C3 would be
=SUMIF($G$11:$G$50,"o",$P$11:$P$50)
G11:G50 is the range containing the locations to test on; "o" is the code for the Onshore location, and P11:P50 is the range to sum. Formulas for C4, C5 and C6 are similar, with "r", "t" and "c" instead of "o".
To calculate the total cost per location in D36, you can also use a SUMIF formula. In D3:
=SUMIF($G$11:$G$50,"o",$V$11:$V$50)
and analogously in D4, D5 and D6.
Finally, the average cost is (if I understand correctly) total cost/total volume, so the formula in E3 would be
=D3/C3
and analogously in E4, E5 and E6

20030311, 11:01 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: VLOOKUP (?) (2000)
Thanks, Hansright on the money!

20030311, 12:13 #8
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: VLOOKUP (?) (2000)
Hans,
One last questionsuppose I want to multiply the volumes by a weighting that woul be in another column. How would I accomplish that?
Thanks,
Jeff

20030311, 12:19 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VLOOKUP (?) (2000)
There are several possible solutions. For example: place the product of volume and weight factor in a column, and use that column as the last argument in the SUMIF function. You can hide this column if you don't want users to see the intermediate results.

20030311, 12:41 #10
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: VLOOKUP (?) (2000)
Thanks, Hans. I got a little creative and got my results via an array formula.

20030311, 12:42 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VLOOKUP (?) (2000)
Good for you. That was one of the other options I had in mind. Should work just as well.