# VBA To Perform Weighted Ranking From Array Of Data

Oct 12, 2011
I'm trying to achieve a table of weighted ranking based on correlations, a table I already have like the one given below

John / Products Owned 1 2 3 4

Shoes Socks Polish Brush Lace

Trousers Shoes Socks Belt Polish

Wallet Belt Trousers Shoes Socks

What I'm trying to achieve is this:

for all the top 4 correlations given for each product owned, i need to consider the ones not owned by John and recommend overall rank of the following nature:

For instance, the rank of socks here will be (1+2+4)/3 = 2.33, the rank of belt will be (5+3+1)/3 = 3 [ I'm considering the rank of belt as 5 for shoes, since it doesn't occur in the top 4 ]. Shoes, here will not be ranked since it is already owned by John.

I'm struggling to translate the logic into code here. Currently I perform it in a cumbersome manner, copying all columns to one column, then performing a pivot to find repetitions and so on.

Perhaps there's a function that could scan a dynamic sized table here, find the unique items and number of times they're repeated?

View 2 Replies
ADVERTISEMENT
May 10, 2006

trying to loop through an array, with each loop calculating a formula (VLOOKUP) for one cell based on the values of a cell in another sheet.

Not sure but I think it has something to do with looking up a text value while the loop returns a value.

Private Sub ExpandFormula()

'calculates Counttarget formula in COUNTTARGETS sheet across required range

Dim CellsDown As Long, CellsAcross As Integer

Dim i As Long, j As Integer

Dim TempArray() As String

Dim TheRange As Range

Dim CurrentValue As String

'get the dimensions

CellsDown = Worksheets("SEPARATE").Range("D9").Value

CellsAcross = Worksheets("SEPARATE").Range("E9").Value

'redimension temporary array

Redim TempArray(1 To CellsDown, 1 To CellsAcross) .....................

View 9 Replies
View Related
Feb 3, 2010

How can I make this macro easier (smaller)? The same action is performed on an array of sheets (not all sheets).

View 4 Replies
View Related
Feb 17, 2009

Sum every instance of [SPREAD]*[SIZE] where [NAME] occurs within a text string.

Please note, I'm trying to do this without creating a new column [SPREAD]*[SIZE].

View 11 Replies
View Related
Mar 30, 2014

Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.

Example:

20

18

18

15

12

11

11

9

The ranking I am looking for is:

1

2

2

3

4

5

5

6

View 2 Replies
View Related
Jan 12, 2010

I'm trying to use the 2007 histogram tool (or frequency) to show me the frequency of data, but my data is in a different form than required for either of these tools. Let me use the following simple case -- to show the frequency of student grades both tools assume I have a list of student's test grades. What if I instead have more than one column, e.g., "grade" and "number students with that grade"??

My application is actually different, but it's still 2 columns, a numeric property and a weighting factor (e.g., fraction of the total).

View 13 Replies
View Related
Feb 4, 2014

Possible to have two spreadsheets and then perform the following tasks

1. Check If the cell in column I from cell I4 and the spreadsheet varies in length is blank and if it is - see item 2

2. The data equals certain values i.e. CDE or CDE - QUO or CDE - PRO - see item 3

3. Perfom a VLOOKUP to another spreadsheet

If the routine stops at any of the items then move to the next cell

Data is on spreadsheet - Allocations.xls

Vlookup is performed on this spreadsheet - 001 - Allocations - Blocks

The Vlookup for example is: =VLOOKUP(A5,'[001 - Allocations - Blocks.xls]CurrentDayAll'!$1:$65536.9,FALSE)

View 3 Replies
View Related
Oct 4, 2006

i am preparing my self to enter the microsoft Excel 2003 Exam.....

one of the mentioned points in the preparation list is: (Perform data analysis using automated tools) how to use the automated tools? and if possiable to get a file that i can apply these tools on it

View 2 Replies
View Related
Nov 5, 2013

On the spreadsheet there is 31 days (a separate worksheet per day).

at the bottom of each sheet there is the case notes for that day. I would like all that information collating on the last sheet of the workbook (named Cases).

I would like to be able to sort through that data with the use of some drop down menus.

All i need to sort is the Brand and if the case is open/closed.

what to do so I can try myself or using the attached spreadsheet be able to sort this problem out. If you choose to use the spreadsheet to meet the requirements

View 14 Replies
View Related
Aug 12, 2014

I am facing some problem with ranking formulae. Attached is a file illustrating them.

Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.

Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.

This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.

Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.

View 1 Replies
View Related
May 22, 2012

In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.

View 6 Replies
View Related
Jun 24, 2014

I have a workbook of data that I created a pivot table with. From there I need to rank each value of the NES, column C, within the corresponding Reporting Structure, column A. The table that I created will be update with data on a monthly basis and I don't want to have to redo the rankings every month and just have it refresh.

View 2 Replies
View Related
Apr 4, 2014

I am trying to rank via data in two columns to eliminate any tied rankings.

I need to use the data in columns "AW" & "AX" to compile the rankings.

The ranking needs to be calculated by the highest number in column "AW" being ranked #1 and the second highest ranked #2 etc etc......any ties as a result of this ranking need to be broken by the lowest number in column "AX", the lower number being the higher in value and thus being the higher ranked.

For example:

AW AX RANK

13 89 7

13 63 4

14 89 2

14 81 1

13 90 8

13 66 5

13 58 3

13 87 6

I have attached the spreadsheet that I am using.

View 7 Replies
View Related
Aug 19, 2014

here is an example of what my data looks like:

Book1.xls

the numerical data under the colors is the cost to make an item that color. i need a way to say that i have "n" of each color available to make and then a way to tell me which items to make which color for the lowest cost.

View 5 Replies
View Related
Sep 30, 2008

Which formula do you recommend I can use to rank the below fields in yellow. I need to find the following:

1 - Which sales advisor (their name) has got the highest MPPI conversion and place it in cell K32.

2 - Which sales advisor (their name) has got the highest HC conversion and place it in cell K34.

3 - Which sales advisor (their name) has got the highest Highest Apps conversion and place it in cell K36.

View 5 Replies
View Related
Feb 14, 2014

I have a spreadsheet with values as per below

I can rank the data based on descending values in Columns B & C using the formula in cell B2

=RANK(B2,$B$2:$B$11)+SUMPRODUCT(--($B$2:$B$11=B2),--($C$2:$C$11>C2))

What I trying to do is also rank the data based on the values in column D as an additional tie-breaker

RankScore 1Score 2Score 3

1412315

2402420

2402422

4402030

5392015

View 3 Replies
View Related
Aug 20, 2014

Ranking in excel.

Column A - List of students Names

Column B - Grades of Students

Column C - Rank

The problem is, when I rank the students (order highest to lowest) the data in A and/or in B also moves.

What I want to accomplish is that the data input in columns A and B stay as is and Column C shall indicate what is the ranking of that student according to the number of data entry. i.e. 1st, 2nd, 3rd or 1,2,3 respectively.

View 9 Replies
View Related
Mar 18, 2013

I have 4,300 cells populated with data. I have sorted the data and now I need to rank the data in 1% increments. For example, the first 1% of data (43 cells) will be ranked #1, the second 1% of data (43 cells) will be ranked #2 etc. How could I automate this process? I was trying to figure something out with the "Rank" command but that doesn't seem to work in this situation.

View 3 Replies
View Related
Mar 19, 2009

I have a spreadsheet with a list of ID's in column A. In Column B I have a list of ratings. Each ID has more than one rating. For example there may be 8 rows with ID 1, 12 rows with ID 2, 6 rows with ID 3 etc......

I want to rank the ratings in Column B with highest being 1 but only when the ID's are the same. For example there would be 8 ratings ranked for ID 1, 12 for ID 2 etc.....

The problem is I am running a monte carlo simulation on the rating of around 10,000 simulations. Every simulation the rank for each row will of course change because the rating has changed. The spreadsheet has around 70,000 rows and over 3000 ID's.

How can I do this without either the computer becoming a crawl or freezing up and it not taking 30 minutes for each step of the simulation?

View 8 Replies
View Related
Jul 11, 2007

On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8

What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.

In other words I want

=sumif(A84:A89;"x";'X-ref'!T4:Y4)

but it doesn't work since the first range is an column-array and the second range is an row-array.

View 5 Replies
View Related
Sep 15, 2005

Item Quantity

A 2

S 7

D 3

F 6

I am looking for a ranking formula that will pull the data from the

first column based on the ranking of the second column

so that the end result will look like this

1 S

2 F

3 D

As S has the largest quantity, F 2nd largest etc

The list I will pull this from is variable in length but in the

hundreds.

View 9 Replies
View Related
Nov 2, 2006

Is there any way to do a weighted median in Excel?

Consider this example: ....

View 7 Replies
View Related
Jul 25, 2007

I have a large dataset that includes information on the investment payout ratios of US companies over the period 2004-2006. I need to calculate the weighted average investment ratio for each company---see sample below

Microsoft Excel - example.xls ....

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The corresponding weight for each year must be as follows: 0.5 for 2006, 0.3 for 2005 and 0.2 for 2004. Then, for company A, the weighted average will be: (0.5*B2)+(0.3*C2)+(0.1*D2) = 7.8 (see cell E2)

The problem is that for some companies there are missing data for some years. For example, for company B, there are no data available for year 2004. In this case , the weights have to change to 0.6 for 2006 and 0.4 for 2005. Then, the weighted average investment for company B will be (0.6*B3)+(0.4*C3) = 16 (see cell E3). Similarly, in cases when there are data for only 1 year , the weight has to change to 1. Then, for company C the weighted average is 35 (see cell E4)

Is there any formula to give me the weighted average as it appears in column E without having to adjust it for each single case?

View 9 Replies
View Related
Mar 15, 2008

I'm doing a GCSE project for which I need a little help with Excel.

I have a set of data about a persons monthly spending habits on

Food 300

Entertainment 200

Clothing 100

Transport 260

The pre calculated budget allowance for the month was 500

I would like to write a formula that would do the following; give the person an score out of 1.1-10 based on how well he spent on the above items which are in order of priority (i.e. it is better to spend more on food than on entertainment etc) And then an over all score (1.1-10) that will reflect how well they spent.

View 9 Replies
View Related
May 1, 2009

In Col DL are digits ranging from 1-36, in Col Dm are the number of times they were drawn in 36 draws. I have a weighted avg formula in Col DN, that I found thru search, but it doesn't appear to be working right or maybe I don't understand the formula. I have no idea if this can be done.

************************************************************************>Microsoft Excel - F5 HOT-COLD.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutDN3DN4DN5DN6DN7=DLDMDNDO2DIGITSHITS 34125 47105 53395 62185 72885 DIGITS [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies
View Related
Nov 10, 2009

I have a spreadsheet which basically looks like this

2, 5, 15, 20

2, 8, 12, 13

2, 7, 19, 46

F, 1, 10, 26

P, 1, 10, 39

P, 6, 22, 89

P, 7, 14, 46

P, 2, 11, 14

...

Column A is the key. If multiple rows have the same key, they're related.

The last column (DX on my spreadsheet) represents minutes participated, and is the weight variable. The middle columns are data that I'd like a weighted average for.

Here is the output I'm looking for given the above example:

2, 6.66, 16.83, 79

F, 1, 10, 26

P, 4.91, 16.73, 188

Column A remains the same, with one row for each key. The middle columns have their weighted average (where 6.66 = (5*20+8*13+7*46)/(20+13+46)), and the final column (DX) is the sum of minutes for all like keys.

View 9 Replies
View Related
Nov 16, 2009

I have long lists of data which has name of manufacturer, number of items and fault rates of items.

E.G

Manufacturer.......Number of Items......Fault Rate

Dell....................40000....................0.2%

Dell....................42200....................0.4%

Sharp.................303003..................0.1%

Microsoft.............312340..................0.04%

Dell....................3249321..................1%

Panasonic............300020...................2%

I need to find the weighted average of each manufacturers fault rate for just themselves, and also how the average of all the manufactueres would be if they weren't in it.

So for example for Dell I would need their weighted average of their three data entries and also then the weighted average of Sharp, Microsoft, Panasonic together.

I have been doing this by splitting the data into sections so for the example above I would do:

Manufacturer.......Number of Items......Fault Rate

Dell....................40000....................0.2%

Dell....................42200....................0.4%

Dell....................3249321..................1%

Panasonic............300020...................2%

Sharp.................303003..................0.1%

Microsoft.............312340..................0.04%

I would sum the Dell items, then sum the number of items excluding Dell then do two weighted average calculations giving me the ones that include then the ones that exclude Dell.

Then I would do...

Manufacturer.......Number of Items......Fault Rate

Sharp.................303003..................0.1%

Microsoft.............312340..................0.04%

Dell....................3249321..................1%

Dell....................40000....................0.2%

Dell....................42200....................0.4%

Panasonic............300020...................2%

And do the same process but with Sharp totals etc then move onto the next one.....

This example is with a very small data set so I'm sure you can see that this process is very time consuming, but I don't know any other way of doing it without splitting out the data first.

View 9 Replies
View Related
Jan 22, 2008

I am trying to find a formula that will calculate a running total of employee time off. Time off is recorded in Excel spreadsheets that I created. V = vacation, S = sick, PD = personal day, and so on.

For example: If a V is entered in any cell in their attendance worksheet, I would like for a 1 to display under the vacation total column. The next time a V is entered in, I would like it to update the cell containing the vacation days used for the year to 2. If a 1/2 V is entered, a .5 would be added to the total used.

View 2 Replies
View Related
Dec 25, 2013

it is more than 5 days now that I try to perform calculation on pivot.

problem: I have one cell that I set to show values as "% of the column total" when try to perform the calculation through insert calculation field with other cell the calculation is not correct/ it seems like the "show values as "% of the column total"" disturbing the calculation resolve this thing

View 14 Replies
View Related
Jul 29, 2013

I have a large spreadsheet set up with multiple rows, I have the formula -

=a5&"repeated text"&c5

this brings back 2 results

test repeated text test

repeated text

In the instance this is the latter with no combined cell vale, how would I mass delete them?

View 2 Replies
View Related