Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel - Weightage

Posted on 2016-09-04
3
Medium Priority
?
169 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 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 1000 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 33

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

885 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