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.

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$100 0)))
Low: =MIN(IF(Sheet1!$A$1:$A$10000A2),Sheet1!$B$1:$B$100 0)))

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" wrote in message
...
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.