How to calculate weighted score with NA cells in excel? 1

[gohym]

Hello,

Need some help here... I'm going crazy with all the numbers...

I need to calculate the weighted score for a long list of items, which is organised into a hierarchical tree with max of 5 levels, i.e. item 1 has 5 items under it (1.1, 1.2, 1.3) and item 1.1 has 2 items (1.1.1, 1.1.2) and so on...

However, each item has a weight attached to it, e.g. item 1.1 has weight of 0.2, item 1.2 0.3, and item 1.3, 0.5 (all the weights of lower level items under the same higher level item adds up to 1). Furthermore, each item has a rating given by someone. However, sometimes the rating can be "NA", i.e. not applicable.

To calculate the score for item 1, we need to multiply the lower levels' (e.g. 1.1, 1.2 etc) rating by the corresponding weight to get the weighted score, and then add up all the weighted score and divide by the total weights. Note that NA items' weights will not be included. For e.g. for item 1, the score will be based on 1.1 (Rating =1, weight =0.2), 1.2 (Rating =NA, weight =0.3), and 1.3 (Rating =1, weight =0.5)... since 1.2 is NA, the score for 1 will be (1 X 0.2 + 1 X 0.5)/(0.2 + 0.5).

Is there a smart way to do this in excel? I have a small sample file, but don't know how to attach here... anyone knows how to attach the file here? For me the problem is how to handle the NA...

 

[xlbo]

You can't attach files here

Need a bit more info to answer the question - namely - how is the data laid out and are the weightings set alongside the items ??

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[gohym]

Hi geoff!

Thanks for responding.

The columns that I have are as follows:

|Level|ItemNo.|Description|Weights|Rating|Score|

ItemNo. goes in this way
1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
1.3
2
2.1
2.1.1
2.1.2
....
The level indicates the... how deep in the tree the item is... e.g. 1, 2 and 3 would be level 1, 1.1, 1.2 would be level 2

Is the above clear? Please feel free ask more qns... I'm leaving for some well-deserved rest... but I'll reply ASAP. Thanks!!

 

[xlbo]

And you want to get the weighted average for 1,2,3 etc etc....??

Also, does the top level (ie 1,2 etc ) have a score attached as well - or just the lower levels ??

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[gohym]

Yup I need the weighted average for 1, 2, 3. actually there is an overall score which will be based on 1,2,3... the lower levels haveall the ratings, top levels do not have it... but there are total of 6 levels, and the ratings are spread across level 3 to 6, not fixed to any levels. let me give an e.g.

|item | Weight|Rating|Score|
-----------------------------
1 | 0.5 | ? | ? |
1.1 | 0.4 | ? | ? |
1.1.1 | 0.3 | 0.5 |0.15 |
1.1.2 | 0.4 | 0.9 |0.36 |
1.1.3 | 0.3 | NA | NA |
1.2 | 0.2 | ? | ? |
1.2.1 | 0.1 | NA | NA |
1.2.2 | 0.4 | ? | ? |
1.2.2.1| 0.5 | 0.2 | 0.1|
1.2.2.2| 0.5 | 0.9 | 0.45|
1.2.3 | 0.5 | 0.5 | 0.25|
1.3 | 0.3 | NA | NA |

Basically... The users will input the ratings of items that requires them to input and the score is calculated based on Wt X Rating... I can't figure out how to handle the "NA". When there are NA I'll need to exclude the weight of that item in the calculation. For example, for item 1.1... the rating = (0.15+0.36)/(0.3+0.4)
=> weight 0.3 of item 1.1.3 has to be left out in this case... it is harder for items' that have lower items that are not adjacent... for e.g. 1 will be baed on 1.1, 1.2 and 1.3, but 1.3 is "NA" so 1.3 should be left out... how do I do that without going thorugh all the rows myself?

Phew... it quite hard to explain when I can't attach files here... I hope things are clearer now... if not pls feel free to ask. Thanks!

 

[gohym]

the "?" means cells that are to be calculated... the rest of the ratings are all input by user... got to go now... I'll reply when I'm back. Thanks!!!

 

[xlbo]

ok - tricky but I think it can be done. I gotta leave shortly but I'll try and knock you something up tomorrow - if someone else hasn't answered by then

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[gohym]

Hey geoff... thanks!! Really really appreciate it!

 

[xlbo]

Ok - see if this makes things any easier - set your cells to have a custom format of:
#,##0.0;-#,##0.0;"NA"

And get them to enter 0 instead of NA

This format masks 0 to make it look like "NA"
You can therefore not worry about the NAs as they will be 0's and anything multiplied by 0 = 0 so they are effectively removed from the equation

This should make the formula much easier to write as it doesn't need to take account of NAs

If this isn't suitable, I can still probably come up with something but as a starter for 10, have a look at he SUMPRODUCT function

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[gohym]

hmmmm.... I think putting 0 instead of NA only solves part of the problem... because I need a way to remove items rated as NA totally from the calculation of the weighted average, i.e. the weights for NA need to be removed from the denominator...

Using the same e.g. from above... item 1.1 will be based on 1.1.1, 1.1.2 and 1.1.3, but 1.1.3 is "NA" so 1.1.3 should be left out... that means weighted average for 1.1 should be = (0.3 X 0.5 + 0.4 X 0.9)/(0.3 + 0.4)... note that the denominator does not include the weight for 1.1.3

if I make NAs into 0... we'll be getting
(0.3 X 0.5 + 0.4 X 0.9 + "0.3 X 0")/(0.3 + 0.4 + "0.3") where "0.3" in the denominator is the weight of item 1.3... which should have been excluded...

I have tried using SUMIF with criteria set to "<>NA", but the function only works if the range is continuous (right?) . so I have problems for items like itme 1, which has sub-items which are not adjacent to each (i.e. 1.1, 1.2, 1.3)...

 

[xlbo]

riiiiight - forgot about having to divide up again

For once, I'm gonna have to say that this will be much easier using a UDF (user defined function)

This is like code but you can use it on a worksheet

Press Alt + F11 to open the VBE
Go Insert>Module

Copy the following and paste into the module:

Function Aggregate(Comparison As Range, TestRng As Range)
Dim numChar As Integer, Rating As Double, Item As Double
Application.Volatile
Rating = 0
Item = 0
numChar = Len([Comparison])
For Each c In TestRng
    If Left(c.Text, numChar) = Format([Comparison], "@") Then
        If c.Address <> [Comparison].Address Then
        Select Case c.Offset(0, 3).Text
            Case "?", "NA"
            Case Else
                Rating = Rating + c.Offset(0, 3).Value
                Item = Item + c.Offset(0, 1).Value
        End Select
        End If
    End If
Next
Aggregate = Rating / Item
End Function

You would then use this on a spreadsheet by entering

=Aggregate(A4,A4:A14)

where A4 contains 1.1

I put it in D4 and got 0.728571
I then entered it into D3 and got 0.784066 for "1" - which would include the 0.728571 for "1.1"

Hope this is correct and makes sense

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[gohym]

Hi Geoff,

Thanks for the code!! I'll give it a try and get back to you...I really hope this works... was trying not to do coding... something I'm not very familiar with... but it's good to learn...

 

[gohym]

Hi Geoff,

I took the opportunity to learn something from the code... I digested the code and did a bit of modifications... the initial code did not give the results I wanted because the conditions for the calculations is based on having the front part of the item being the same as the same as the [comparison] right... however, that would mean include too many items even items that are of too low levels... e.g. for item 1, we should calculate based on 1.1, 1.2 and 1.3, but the conditions will cause 1.1.1, 1.1.2... to be included too.

What I did is to include one more condition... "If ((Len(c.Text) - numChar) = 2 Or (Len(c.Text) - numChar) = 3) Then" in this way we ensure that the item that is included is of the right level... not too low not too high... just nice!!

Based on my checks the function seems to be working fine...

Really appreciate your help and effort!! I really learned something and it solved my problem... Thank you! thank you! thank you! see you around!

Function Aggregate(Comparison As Range, TestRng As Range)
Dim numChar As Integer, TotRating As Double, Wt As Double
Application.Volatile
TotRating = 0
Wt = 0
numChar = Len([Comparison])
For Each c In TestRng
    If Left(c.Text, numChar) = Format([Comparison], "@") Then
        If c.Address <> [Comparison].Address Then
            If ((Len(c.Text) - numChar) = 2 Or (Len(c.Text) - numChar) = 3) Then
                Select Case c.Offset(0, 2).Text
                    Case "NA"
                    Case Else
                        Rating = Rating + c.Offset(0, 3).Value
                        Wt = Wt + c.Offset(0, 1).Value
                End Select
            End If
        End If
    End If
Next
Aggregate = Rating / Wt
End Function

 

[gohym]

Strange... I tried to give geoff a star... clicked the link a few times, but nothing happened... Anyway, Here're a few stars for you Geoff... Thanks! * * * * *

 

[xlbo]

Looks like it's come through ok. Very pleased you've managed to learn something and that you've got your solution. Best possible outcome all round really

Rgds, Geoff