Results 1 to 2 of 2
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Frequency Plot (XP)

    Morning All,

    I have a cumulative density function (see attached). I'm trying to come up with a simple method to produce the underlying frequency plot (see histogram chart). I've currently got a series of lookup functions and then an interpolation to get the incremental percentage.

    Am I missing something mind blowingly simple?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  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: Frequency Plot (XP)

    Since you have the list in BC and you want to interpolate, the there will not be a real simple way (though you could create a user function to eliminate the temp column).

    Instead of Col D and 3 VLOOKUP and a calc in L, I would use a match and 4 INDEX functions, this minimizes the number of lookups (INDEX is faster than LOOKUP since it doesn't have to search the list.

    In (eg) I5:
    =MATCH(F5,$C$5:$C$23)

    Then in J5:
    =INDEX($B$5:$B$23,I5)
    K5:
    =INDEX($C$5:$C$23,I5)
    L5:
    =INDEX($B$5:$B$23,I5+1)
    M5:
    =INDEX($C$5:$C$23,I5+1)

    Copy I:M5 down the rows
    This is more generic (you assume that A if always incremented by 0.05, the scheme I propose does not require this.

    The advantage of this scheme is a little speed and also the calcs are all for your new output, none is needed for the the "input" part (eg Col D). This scheme could also easily be used in a User function to give the value in G, if you don't want the Int Calcs

    <pre>Option Explicit
    Function Interpolate(vValue, rLookup As Range, rResult As Range)
    Dim lRow As Long
    Dim dRLo As Double
    Dim dLLo As Double
    Dim dRHi As Double
    Dim dLHi As Double
    lRow = Application.WorksheetFunction.Match(vValue, rLookup)
    dLLo = rLookup.Cells(lRow, 1)
    dLHi = rLookup.Cells(lRow + 1, 1)
    dRLo = rResult.Cells(lRow, 1)
    dRHi = rResult.Cells(lRow + 1, 1)
    Interpolate = (dRHi - dRLo) / (dLHi - dLLo) * (vValue - dLLo) + dRLo
    End Function</pre>


    Then in G5:
    =interpolate(F5,$C$5:$C$23,$B$5:$B$23)

    And copy it down the rows.

    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
  •