1. ## 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?

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