Lookup z-score in z-table

Hello,

If you have a Z-table in excel in table A12:K43, how would you get excel to look up the right z score.

Say we need to look up the probability of .45, we go to the table and look at where .45 intersects, it intersects between 1.64 and 1.65. We can choose either one. Or we can even do an exact match such as .0389 has a Z of .1...

How can we create a formula where I can just type in the probability and and it gives me the z-score.

It would help those of us unfamiliar with z-tables to have a workbook. It sounds like a Vlookup or potentially an Index/Match solution is in order, but I'd have to see it to give you the right answer. If the data is sensitive, could you please post a version with dummy data?

yes just a second

Here it is, this shows the Z table and a T-table... both would be great.

Basically input a number that shown in the intersecting data.. in the Z table is all the numbers below .4990 and the formula would bring up the z-score... If I typed in .4990 it would bring up 3.09

or .4986 = 2.99

Also the reverse...

If i type in 1.40 it would return .4192

Well, the reverse is easy. =INDEX(B13:K43,MATCH(LEFT(N11,3)+0,A13:A43,0),MATCH((RIGHT(N11)+0)/100,B12:K12))

I'm not sure I'm good enough to give you the other version, but I'll see what I can do. The formula above assumes you put 1.42 (for example) in N11. Change that as necessary.

That works really well! Thanks!

The other way would be awesome too!

If the value is in M11, I think this should work for most cases. It's sloppy and not fool-proof (if anyone wants to show me a more efficient and accurate way, I'd be grateful, but I think it should do what you want in most (if not all) cases.

Please Login or Register  to view this content.

Brilliant, DonkeyOte, on that first one.
And you're right on the second one. I didn't consider what would happen if he entered 1.40. It would work well enough if that cell was formatted as text, but if it was general or a number, mine would have returned the value for 1.44 instead.

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.