Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Bronze Lounger
    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 On-Line 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>

  4. #4
    Bronze Lounger
    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 it-ok?
    Thanks,
    Jeff

  5. #5
    Bronze Lounger
    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

  6. #6
    Plutonium Lounger
    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

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (?) (2000)

    Thanks, Hans-right on the money!

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP (?) (2000)

    Hans,
    One last question-suppose I want to multiply the volumes by a weighting that woul be in another column. How would I accomplish that?
    Thanks,
    Jeff

  9. #9
    Plutonium Lounger
    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.

  10. #10
    Bronze Lounger
    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.

  11. #11
    Plutonium Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •