Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Lookup & Return Adjacent Value From Left

  1. #1
    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. #2
    Join Date
    27th January 2003
    Location
    Swindon, England
    Posts
    116

    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    ________________

    Check out my band...
    http://www.myspace.com/suspicions
    facebook

  3. #3
    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.

    Please ask if what I am describing/asking doesn't make sense!

    Att. is an example of the data I have
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    27th January 2003
    Location
    Swindon, England
    Posts
    116

    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    ________________

    Check out my band...
    http://www.myspace.com/suspicions
    facebook

  5. #5
    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. #6
    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. #7
    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. #8
    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. #9
    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;

    May I also add:

    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. #10
    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Lookup Left Most Column & Return Adjacent Value
    By Maryhasfour in forum Excel General
    Replies: 2
    Last Post: October 1st, 2007, 11:14
  2. Lookup In Column & Return Value To Left
    By addlemand in forum Excel General
    Replies: 1
    Last Post: September 29th, 2007, 03:58
  3. Lookup & Return Value On Right Or Left
    By Pedrohern in forum Excel General
    Replies: 5
    Last Post: September 5th, 2007, 20:00
  4. Lookup & Return Left Corresponding Cell
    By rap1 in forum Excel General
    Replies: 1
    Last Post: August 13th, 2007, 02:40
  5. Lookup & Return Adjacent Result
    By sgraves in forum Excel General
    Replies: 6
    Last Post: December 15th, 2006, 03:06

Bookmarks

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