Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A Better Way (XP; SR2)

    I am trying to determine the best approach to determine the variances between two numbers. I have a matrix; departments in column A and departments (prefixed with the letter "D") in row 5.

    Using C8 and D7 the analysis would look something like this:


    <table border=1><td>DeptList</td><td>DeptListwPrefix</td><td>Amt_01</td><td>Amt_02</td><td>Amt_01-Amt_02</td><td>60060</td><td>D60060</td><td>-393,877.81</td><td>393,877.81</td><td>0</td></table>

    The DeptList column would of course account for every department in the matrix.

    Any assistance would be appreciated,
    John

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: A Better Way (XP; SR2)

    Do you mean the "difference" (a-[img]/forums/images/smilies/cool.gif[/img] not the variance (the average of the sum of the squares of the deviation from the mean)?

    Where are the numbers located?

    If you are trying to read from the table, INDEX might look with a MATCH to find the row and a MATCH to find the column.

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Better Way (XP; SR2)

    Steve,

    It is the difference between the cells ie C8 + D7. In this example the difference is zero. There may be other departments where it is not zero.

    The matrix is in the Excel spreadsheet as displayed in my previous thread.

    John

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: A Better Way (XP; SR2)

    Are you looking for something like this?
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>G</td><td align=center valign=bottom>1</td><td valign=bottom>DeptList1</td><td valign=bottom>DeptList2</td><td valign=bottom>DeptList1wPrefix</td><td valign=bottom>DeptList2wPrefix</td><td valign=bottom>Amt_01</td><td valign=bottom>Amt_02</td><td valign=bottom>Amt_01+Amt_02</td><td align=center valign=bottom>2</td><td align=right valign=bottom>60060</td><td align=right valign=bottom>60056</td><td valign=bottom>="D"&A2</td><td valign=bottom>="D"&B2</td><td valign=bottom>=INDEX(Sheet1!$C$6:$CX$100,MATCH($A2 ,Sheet1!$A$6:$A$100,FALSE),MATCH($D2,Sheet1!$C$5:$ CX$5,FALSE))</td><td valign=bottom>=INDEX(Sheet1!$C$6:$CX$100,MATCH($B2 ,Sheet1!$A$6:$A$100,FALSE),MATCH($C2,Sheet1!$C$5:$ CX$5,FALSE))</td><td valign=bottom>=E2+F2</td></table>

    Just fill in the combinations in cols A and B for the intersection of the 2 DeptLists. Note you want Amt_01+Amt_02 not Amt_01-Amt_02 if you are looking for zero. The 2 should be equal in magnitude but have opposite signs

    Steve

Posting Permissions

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