Same Question on Probit

Hi all,

To elaborate my previous thread, this is what I have:

Probit %
0 0
2.67 1
2.95 2
3.12 3
3.25 4
3.36 5
3.45 6
3.52 7
3.59 8
3.66 9
3.72 10
3.77 11
3.82 12
3.87 13
3.92 14
3.96 15
4.01 16
4.05 17
4.08 18
4.12 19
4.16 20
4.19 21
4.23 22
4.26 23
4.29 24
4.33 25
4.36 26
4.39 27
4.42 28
4.45 29
4.48 30
4.5 31
4.53 32
4.56 33
4.59 34
4.61 35
4.64 36
4.67 37
4.69 38
4.72 39
4.75 40
and so on... up to 100%

From my calculations, say, if the probit value is 4.54, manual lookup would give a percentage of 32% - 33%, 32.33% from interpolating the data. How can I get Excel to do a VLOOKUP on this, ie. to give me the figures without manual calculations?

Help appreciated. Thanks.

Regards,
Soo Ting

This may not be pretty, but hopefully it will get you on the right track:

Put your list (0 through 4.75) in cells A1:A41.
In cell B1 put =A2, then copy that formula down through B41.
Next put your probit list (0 through 40) in cells C1:C41.

Next, Enter 4.54 in cell D1 and put this forumula in Cell D2:
=VLOOKUP(D1,$A$1:$C$41,3,1)+(D1-VLOOKUP(D1,$A$1:$C$41,1,1))/((VLOOKUP(D1,$A$1:$C$41,2,1)-VLOOKUP(D1,$A$1:$C$41,1,1)))

It will interpolate the way you described.

Regards,
Ron