Solved

Excel - Weightage

Posted on 2016-09-04
3
73 Views
Last Modified: 2016-09-05
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
Comment
Question by:happy 1001
3 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 250 total points
ID: 41784193
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
 
LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41784208
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
 

Author Comment

by:happy 1001
ID: 41784568
@ 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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now