asked on

Excel - Weightage

Hi Experts

I have a data set which has got 190 Products arranged in Columns, and 4 Rows, which show the Value % Change over 4 different time duration respectively.

I need to do 2 tasks in this worksheet.

First I need to give the Weights to each of the 4 different time duration values, based on this method -
The calculation is weighted, with the most recent three months assigned a 40% weight, while the previous nine months receive a 60% weight.
The basis of rank calculation is this formula -
40 * A% + 20 * B% + 20 * C% + 20 * D% - 100%

The second step -
Once we have the FINAL WEIGHTS based on the method explained above, then we need to RANK these final values on a scale of 1 to 100.
The biggest final weight value will get the rank of 100 and the least value will get the rank of 0.

Please suggest how to do it. If you need more clarification on any point, then please ask, and I will explain more.

I am using the following software versions -
Microsoft SQL Server Management Studio version- 12.0.2000.8,
Microsoft Office 2016 x64
and Windows 7 x64

I have attached the Excel File having the data.
Excel---Weights.xlsx

Snapshot of the Sheet -

Thanks

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

happy 1001

ASKER

@ Wayne Taylor, thank you so much. You have correctly understood the weighting requirement and those formulas are working great.

@ Subodh Tiwari Ji, thank you so much for your help.
I have one doubt, if I have a formula in the cell D9 and I want to copy it across to the whole range "D9 to GK9" in our example, then what keyboard shortcuts should I use in order to copy it across ? I find it pretty hard to drag the mouse to large ranges, and I have to do such thing quite often. Hopefully there would be an easier way to copy across such formulas.

Thanks a lot