+ Reply to Thread
Results 1 to 2 of 2

Complex Array Formulas for cost of goods sold valuation

  1. #1
    Registered User
    Join Date
    08-20-2007
    Posts
    10

    Complex Array Formulas for cost of goods sold valuation

    Hi,
    I pasted 2 related tables below and I need to create 2 array formulas:
    1) In Table 2 under "units sold" a formula in that returns the number of units of a particular product sold that has a given unit cost. It shows that 2 units with the serial no. 7001 costing $156 each were sold. Then I need to copy this formula down to the rest of the cells in the units "units sold column". As a result, the second cell down would show that 4 units of 7001 costing $142 each were sold, etc. This formula will also produce a result in the "Cost of Goods Sold" column that reflects the total fcost for those 2 units sold, $156 * 2 = 312

    2) a formula in "prices column" (also in table 2) that returns the cumulative selling price obtained for those units sold. For example, relative to the first cell in this column, for the sales of 2 units of no. 7001 costing $156 each, a total price of $388 ($199 + $189) is obtained. Then I need to copy this formula down the rest of the "prices" column.

    I have been racking my brain on this problem for 2 days and I am really desperate. ANy help would be greatly appreciated.

    Thanks in Advance!
    Jenny

    Table 1. Sales Records
    Product Name ID Serial Number Unit Cost Selling Price

    Kodak EasyShare P850 7001 5244270 156 199
    Kodak EasyShare P850 7001 3058375 156 189
    Kodak EasyShare P850 7001 4786766 142 177
    Kodak EasyShare P850 7001 6982965 142 177
    Kodak EasyShare P850 7001 9889905 142 165
    Kodak EasyShare P850 7001 4366908 142 165
    Kodak EasyShare V70 5656 5646262 201 220
    Kodak EasyShare V70 5656 6247505 199 234
    Casio Exilim EX-Z850 9812 2846843 178 241
    Casio Exilim EX-Z850 9812 3671198 178 241
    Casio Exilim EX-Z850 9812 8447425 178 225
    Sony DSC-N1 7753 261209 234 267
    Sony DSC-N1 7753 5891987 222 258
    Pentax Optio W10 2387 839551 256 305
    Pentax Optio W10 2387 1281852 233 301
    Pentax Optio W10 2387 2498589 233 299
    Pentax Optio W10 2387 9939138 233 267
    Panasonic Lumix 4444 9347263 300 388
    Panasonic Lumix 4444 498636 300 355

    total 4673


    Table 2. Cost of Goods Sold Computation
    Product Unit Units Cost of Goods
    ID Cost Sold Sold Prices
    Kodak EasyShare P850 7001 156
    Kodak EasyShare P850 7001 142
    Kodak EasyShare V70 5656 201
    Kodak EasyShare V70 5656 199
    Casio Exilim EX-Z850 9812 178
    Sony DSC-N1 7753 234
    Sony DSC-N1 7753 222
    Sony DSC-N1 7753 200
    Pentax Optio W10 2387 256
    Pentax Optio W10 2387 233
    Panasonic Lumix 4444 300

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606
    I don't fully understand but I suggest you have a look at pivot tables. With the Wizard they're quite easy to follow. Have a go and see how far you get.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1