Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

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 -
Weights.png
Thanks
0
happy 1001
Asked:
happy 1001
2 Solutions
 
Wayne Taylor (webtubbs)Commented:
Not entirely sure I understand your weighting requirements. But again, you use the RANK function to determine the rank. Take a look at the attached and see if it is what you are after.
Excel---Weights.xlsx
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try this...

In D9
=SUMPRODUCT((D5:D8)*(($G$15:$G$18/100)))

Open in new window

and then copy across.

To get rank
In D10
=RANK(D9,$D$9:$GK$9,1)

Open in new window

and copy across.
0
 
happy 1001Author Commented:
@ 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now