Results 1 to 2 of 2
Thread: Frequency Plot (XP)
2004-10-13, 03:26 #1
- Join Date
- Feb 2001
- New York, New York, USA
- Thanked 0 Times in 0 Posts
Frequency Plot (XP)
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"
2004-10-13, 09:43 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- 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:
Then in J5:
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
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
Then in G5:
And copy it down the rows.