+ Reply to Thread
Results 1 to 2 of 2

calculate % change in price for each of 200+ products

  1. #1
    Jeff
    Guest

    calculate % change in price for each of 200+ products

    I work for a school system. Schools order custoidal supplies from a catalog
    of 200+ items. Some items are ordered monthly, others periodically. I have
    a spreadsheet of ALL the orders for each product type, it is 9000+ lines.
    Prices have changed several times for many products. I want to summarize the
    list of each product, identify the highest and lowest price paid and the % of
    change in the price for each item.
    Item Hi Low % Incr
    Paper towels $4 $3 33%
    5 gal Plastic bags $6 $4 50%
    etc.

  2. #2
    Bob Phillips
    Guest

    Re: calculate % change in price for each of 200+ products

    Assuming product in column A, prices in column B. On another sheet list all
    products in A2:A201, then use

    High: =MAX(IF(Sheet1!$A$1:$A$10000A2),Sheet1!$B$1:$B$1000)))
    Low: =MIN(IF(Sheet1!$A$1:$A$10000A2),Sheet1!$B$1:$B$1000)))

    which are array formulae, it should be committed with Ctrl-Shift-Enter, not
    just Enter.

    %Incr: =(B2/A2)-1 and format as a percentage.


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Jeff" <[email protected]> wrote in message
    news:[email protected]...
    > I work for a school system. Schools order custoidal supplies from a

    catalog
    > of 200+ items. Some items are ordered monthly, others periodically. I

    have
    > a spreadsheet of ALL the orders for each product type, it is 9000+ lines.
    > Prices have changed several times for many products. I want to summarize

    the
    > list of each product, identify the highest and lowest price paid and the %

    of
    > change in the price for each item.
    > Item Hi Low % Incr
    > Paper towels $4 $3 33%
    > 5 gal Plastic bags $6 $4 50%
    > etc.




+ 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