Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Lookup & Return Adjacent Value From Left

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #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
    Attached Files
    ________________

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

    Comment


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

      Comment


      • #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
        Attached Files
        ________________

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

        Comment


        • #5
          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.

          Comment


          • #6
            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

            Comment


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

              Comment


              • #8
                Re: Lookup & Return Adjacent Value From Left

                Hi,

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

                ExcelFox

                Comment


                • #9
                  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, 19:36. Reason: Auto Merged Doublepost

                  Comment


                  • #10
                    Re: Lookup & Return Adjacent Value From Left

                    Hi,

                    OK. Here is a UDF.

                    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
                    Kris

                    ExcelFox

                    Comment


                    • #11
                      Re: Lookup & Return Adjacent Value From Left

                      Hi Kris,

                      Wow and thank you! But ...

                      it hasn't sorted out my problem. It's my fault for not loading an excel file with all directors of companies a person is on. Here is a file that has all the information for one person - Anil Ambani.

                      The personal power score for Anil Ambani should be:

                      For Reliance Communications - [(236.917*2)/2.75] = 172.303
                      For Reliance Energy - [(230.949*3)/8.5] = 81.511

                      giving him a total personal power score of 253.303.

                      In the spreadsheet att, having included your calculations, I don't get that answer. I would not know where to begin to address your calculations. Where is it going wrong?
                      Attached Files

                      Comment


                      • #12
                        Re: Lookup & Return Adjacent Value From Left

                        Hi Ajit,

                        Option Compare Text
                        Function PSCORE(CoScRng As Range, WghtRng As Range, _
                        CoDgnRng As Range, Co, Design) As Double
                        Dim a, b, c, x, y, i As Long

                        x = CoDgnRng.Value
                        Design = Trim$(Design)
                        Co = Trim$(Co)
                        a = GETWGHT(Design, WghtRng)
                        b = GETSCORE(Co, CoScRng)
                        For i = 1 To UBound(x, 1)
                        If Trim$(x(i, 1)) = Co Then
                        c = c + GETWGHT(x(i, 2), WghtRng)
                        End If
                        Next
                        If a > 0 Then PSCORE = a * b / c
                        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$(Replace(x(j), Chr(160), "")) Then
                        GETWGHT = GETWGHT + a(i, 2)
                        Exit For
                        End If
                        Next
                        Next
                        End Function


                        Try in D2,

                        =PSCORE(Company!$A$2:$J$6,Sheet1!$A$1:$B$10,$B$2:$C$13,B2,C2)

                        HTH
                        Kris

                        ExcelFox

                        Comment


                        • #13
                          Re: Lookup & Return Adjacent Value From Left

                          Dear Kris,

                          Thank you very much! It now comes up with the personal score that I calculated by hand. I really appreciate your help and am in awe of your expertise.

                          I'm going to try and use this for the full dataset. I still have to add board of directors for each company, which will take me some time. Is there anything I should bear in mind while I am doing this?

                          One question that I have is issues of sorting the company/directors worksheets. If I change the order, will that cause problems? I'm sure I'll find out when I try!

                          valare upakaram

                          Ajit

                          Comment


                          • #14
                            Re: Lookup & Return Adjacent Value From Left

                            One question that I have is issues of sorting the company/directors worksheets. If I change the order, will that cause problems?
                            No.

                            valare upakaram

                            Ajit
                            Ente santosham
                            Kris

                            ExcelFox

                            Comment

                            Trending

                            Collapse

                            There are no results that meet this criteria.

                            Working...
                            X