Solved

Excel - Weightage

Posted on 2016-09-04
3
149 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 31

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

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

627 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