Find Variance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dee - 21 Nov 2006 22:08 GMT

Ok.. don't know if the terminology is correct, but basically what I need to
do is... I have found all of the manufacturer's products where they have
charged us different unit prices. I will need to prioritize them so that we
work on the items with the most significant difference in price so we don't
waist our time on $0.01 difference instead of $100.00 difference.

Problem is there are 820 line items. One product number may have 3
different unit prices or another product number may have 2 different unit
prices. How can I find the difference between the unit prices when there are
820 line items and each product will have at least 2 unit prices, but may
have more than 2???? Please let me know if you have any ideas. Thanks.

Reply to this Message

JMB - 22 Nov 2006 04:18 GMT

You could use the Advanced Filter to generate a list of unique product codes.
Lets say the product codes are in A2:A821, unit prices are in B2:B821
(assuming you have column headers). Click Data/Filter/Advanced Filter, check
copy to another location and unique records only, list range is A1:A821, copy
to range is (let's say) D1. Then in E2, enter

=MAX((A$2:A$821=D2)*(B$2:B$821))-MIN(IF(A$2:A$821=D2,B$2:B$821))

array entered w/ Cntrl+Shift+Enter. Then copy the formula down. It will
give you the difference between the lowest price and the highest price for
each product code. Then sort this new table by the variance column.

change ranges as needed.

> Ok.. don't know if the terminology is correct, but basically what I need to
> do is... I have found all of the manufacturer's products where they have
[quoted text clipped - 7 lines]
> 820 line items and each product will have at least 2 unit prices, but may
> have more than 2???? Please let me know if you have any ideas. Thanks.

Reply to this Message

Prasadkakarla - 28 Nov 2006 19:30 GMT

Hello Mr.Dee
i am new person for this forum... i think u use pivot table u can easily
get the wht ever prices u have in one product it will show in different
columsn, and also u can use HLOOKUP AND VLOOKUP. so just u try with privot
table u will get the answer. sure.make sure u select the columns proper give
column name also.

try and tell me u r opinion to my id
prasadkakarla007@yahoo.com
prasadkakarla007@gmail.com

thank u

> You could use the Advanced Filter to generate a list of unique product codes.
> Lets say the product codes are in A2:A821, unit prices are in B2:B821
[quoted text clipped - 21 lines]
> > 820 line items and each product will have at least 2 unit prices, but may
> > have more than 2???? Please let me know if you have any ideas. Thanks.

Reply to this Message