PDA

View Full Version : Sleeper: Auto-Adjust Ranking



Kate
01-05-2007, 12:13 PM
Hello,

Using a very rough approximation of the 80/20 applied to 2 years of Sales data, results in a set of Part Numbers that accounted for 80% of the Net Sales in each year. These Part Numbers would be combined and used to set the Vendor's Discount structure.

I don't know of a better way to do this and I invite your suggestions.

Part Number usages vary from year to year, so the formula I apply to Rank Top 80% of Net Sales is not very flexible.

Meaning if Year-X had 6 Part Numbers and Year-Y had 14 Part Numbers accounting for 80% of Net Sales I would need a formula or macro to work out the ratio and Rank the (PartNo's) Net Sales independently.

For now, I manually change the Ranking formula in Columns "G" & "O" to match the Part No's (rows) making up the approx 80% of Net Sales.

My formula only allows for fixed evaluation and is set to show the Top 4 items. This is useless and why I need some help.

Please look at the sheet "Combined - Copy" in the attached file to get a better grasp of my problem. :help

Thank you,

Kate