Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
Join Date
2nd April 2008
Posts
7

## Lookup & Return Adjacent Value From Left

I have two worksheets. The first one contains 500 rows:

Column A - Company Name
Column J - Formula that calculates a 'company score' based on other data in columns B-H
Column K - Number of directors

The second worksheet contains over 3000 rows:

Column A - Name
Column B - Company
Column C - Designation

I want to caculate a score for each person (from worksheet 2) that does the following:

Personal Score = ('Company Score' / No. of directors) * Designation Weighting

Where Designation Weighting (numerical value) is determined by Designation (text). There are 10 different and mutually exclusive types of text in the Designation column.

My problem is compounded by the fact that each person (in worksheet 2) can be directors on several companies. The Personal Score that I want to calculate needs to recognise which different companies they are directors of and sum the scores derived from each company.

Help!

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Formula Multiple Worksheets/conditions

Have a look at INDEX & MATCH. Here's a really simple example.

And use a VLOOKUP for the Designation (text).

Cheers

Lee

3. I agreed to these rules
Join Date
2nd April 2008
Posts
7

## Re: Formula Multiple Worksheets/conditions

Lee, thank you for your suggestion. I am very much a beginner in using the various functions, so apologies if my question is asking the obvious!

What you are suggesting returns a particular number from a table. I'm not sure how this helps me calculate personal score for each individual on worksheet 2. Corporate score and no of directors are on a different worksheet to individual names and designation.

I don't know how to record the information about multiple directorships for each individual, hence I don't know how to get excel to calculate the share of corporate score for each company that a person is a director.

Att. is an example of the data I have

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Formula Multiple Worksheets/conditions

I'm not convinced I know what you're trying to achieve, but hopefully this should start you off.

Lee

5. I agreed to these rules
Join Date
2nd April 2008
Posts
7

## Re: Formula Multiple Worksheets/conditions

Lee, thank you very much for the example. My knowledge of excel is not good enough to figure out what your formula is doing, but I shall play around with the numbers to see if it does what I want.

One thing I noticed immediately is that the personal power number does not change if I change the number of directors. The idea is that the corporate power score is divided between each of the directors, but depending on the designation one director gets more than another.

Excel Video Tutorials / Excel Dashboards Reports

6. Member
Join Date
24th July 2007
Posts
29

## Re: Formula Multiple Worksheets/conditions

Have you ever used code in Excel? I don't think you're going to be able to do this very easily (if at all) using formulas.

Are you saying that, if a company has 4 directors, that you don't want to necessarily divide the corporate score by 4 but, instead, you want to have some sort of weighting depending on their title? That's going to be ugly, although I am pretty sure it can be done.

Let us know.

Scott

Excel Video Tutorials / Excel Dashboards Reports

7. I agreed to these rules
Join Date
2nd April 2008
Posts
7

## Re: Lookup & Return Adjacent Value From Left

Yes, exactly Scott! Depending on the designation (these are finite and mutually exclusive, see below) the personal score a person gets for a company directorship will be different.

The weights I want to apply to the director designations are:

Chariman and Managing Director 3.00
Chairman and Promoter Director 2.00
Chairman 1.5
Chairman Non Executive 1.00
Deputy Chairman Executive 1.25
Deputy Chairman Non Executive 0.50
Managing Director 2.00
Executive Director 1.00
Non Executive Director 0.25
Promoter Director 1.50

So, the personal power score needs to take into account the number of directors and their title. It also needs to add up all the different directorships a person might hold on different companies.

Let us take a hypothetical example:

Company A - Corporate Power Score - 100, No of directors - 5 (One Chairman, 3 Executive Directors, 1 Non Executive Director)
Company B - Corporate Power Score - 200, No of directors - 3 (One Chairman and Managing Director, 2 Non Executive directors)

Person:

Person X - Title Chairman of Company A, Non Executive Director of Company B

The person power score for Person X will be:

[(3*100)/4.75] + [(0.25*200)/3.5]

No, I have not used code before. I will have a look, but I am going from using the basic functions (sum, average etc.) to this! Any help would be very much appreciated.

Excel Video Tutorials / Excel Dashboards Reports

8. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Lookup & Return Adjacent Value From Left

Hi,

[(3*100)/4.75] + [(0.25*200)/3.5]
How you get 3 and 0.25 ?

9. I agreed to these rules
Join Date
2nd April 2008
Posts
7

## Re: Lookup & Return Adjacent Value From Left

Sorry, my mistake, it should be 1.5 which is the weight for being Chairman of Company A.

0.25 is the weight for being Non Exec Director of Company B.
Auto Merged Post Until 24 Hrs Passes;

I don't know how to get the information about how many directorships a person holds in my datasheet. Nor do I know how I can get excel to calculate the 4.75 for Company A and 3.5 for Company B in my hypothetical example. This depends on knowing the designations of all the board members of a company.

At present this is static data. I don't have plans as yet to update the corporate power score (which is a calculation based on company performance), directors changing over time, designations changing over time. But it would be great to allow for that in the future ....
Last edited by Ajit Nayak; April 3rd, 2008 at 19:36. Reason: Auto Merged Doublepost

Excel Video Tutorials / Excel Dashboards Reports

10. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Lookup & Return Adjacent Value From Left

Hi,

OK. Here is a UDF.

VB:
```Option Compare Text
Function PSCORE(CoRng As Range, WghtRng As Range, Co, Design) As Double
Dim a, b, c, x, y, tmp, i As Long
x = Split(Co, ",")
y = Split(Design, ",")
For i = 0 To UBound(x)
a = GETWGHT(Trim\$(y(i)), WghtRng)
b = GETSCORE(Trim\$(x(i)), CoRng)
c = GETWGHT(Design, WghtRng)
tmp = tmp + (a * b / c)
Next
If tmp > 0 Then PSCORE = tmp
End Function
Private Function GETSCORE(Co As Variant, CoRng As Range)
Dim i As Long, a
a = CoRng
For i = 1 To UBound(a, 1)
If a(i, 1) = Co Then
GETSCORE = a(i, 10)
Exit Function
End If
Next
End Function
Private Function GETWGHT(Design As Variant, WghtRng As Range)
Dim i As Long, a, x, j  As Long
a = WghtRng
x = Split(Design, ",")
For j = 0 To UBound(x)
For i = 1 To UBound(a, 1)
If a(i, 1) = Trim\$(x(j)) Then
GETWGHT = GETWGHT + a(i, 2)
Exit For
End If
Next
Next
End Function

```
See the attachment.

HTH

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno