Forum Discussion
Solved
VLOOKUP #N/A error -- first time poster
Hello. I am getting an #N/A error when using a VLOOKUP formula.
Background:
I want to be able to calculate a person's z-score to give me a percentile based on their age and score.
B3: person's age
B5: person's score
A9:C17: 3x9 table that contains age ranges with their corresponding mean and standard deviation
In order to calculate a person's z-score ((score-M)/SD), I use the following formula in B19: =IF(B3<35,((B5-B9)/C9),IF(B3<45,((B5-B10)/C10),IF(B3<55,((B5-B11)/C11),IF(B3<60,((B5-B12)/C12),IF(B3<65,((B5-B13)/C13),IF(B3<70,((B5-B14)/C14),IF(B3<75,((B5-B15)/C15),IF(B3<80,((B5-B16)/C16),IF(B3<89,((B5-B17)/C17))))))))))
This gives me a number in B19. E.g., If age (B3) is 34 and their score (B5) is 57, B19 = 0.34 ((57-56)/2.9).
Next I want to convert the z-score/output from B19 into a percentile in B20. The 2x1001 table with z-scores and their corresponding percentiles are in a 'Supplemental' sheet.
B20 has this formula: =VLOOKUP(B19,Supplemental!$H$17:$I$1017,2,FALSE). I keep getting an #N/A error for B20. I think it has something to do with B19 being calculated from a formula. If I type 0.34 into another cell and change the B20 formula to pull from that cell, it works.
What I've tried:
-changed format of cells so they are all the same (I tried changing both to Number, both to General, both to Text)
-changed format of cells so that B19 is the same as Supplemental H, and so that B20 is the same as Supplemental I
-made sure decimal spaces were the same
Thank you for reading this far. Appreciate any advice you can give. I've played around with Excel a lot and have managed to figure things out by searching online when I get stuck, but I can't get around this error. I'm not a beginner, but I am no where near proficient with Excel, so please err on the side of overexplaining any recommendations. Thank you!
A B C
3 Age 34 (for this example)
5 Score 57 (for this example)
8 Age M SD
9 25-34 56 2.9
10 35-44 56.1 3.6
11 45-54 55.4 3.6
...etc.
19 z-score 0.34 (for this example; calculated from formula: =IF(B3<35,((B5-B9/C9), IF(B3<45.... )
20 %ile #N/A (should be a value ranging from >99 to <1, calculated using formula: =VLOOKUP(B19,Supplemental!$H$17:$I$1017,2,FALSE)
Supplemental sheet:
H I
16 z-score %ile
17 5.00 >99
18 4.99 >99
19 4.98 >99
...etc. (z-score ranges from 5.00 to -5.00, %ile ranges from >99,99,98,...3,2,1,<1)
Does this work?
=VLOOKUP(ROUND(B19, 2), Supplemental!$H$17:$I$1017, 2, FALSE)
3 Replies
Does this work?
=VLOOKUP(ROUND(B19, 2), Supplemental!$H$17:$I$1017, 2, FALSE)
It does! Thank you! I just looked up the Round function. If I'm understanding correctly, it seems that even though I changed the decimal spaces in the cell (using the Decrease/Increase Decimal tool in the Number section of the Home menu, that only changes what I see in the table, not the actual value in the cell. The ROUND function actually changes the value so it goes out to 2 decimal spaces to match the z-scores I'm "looking up."
Yes, that is correct. A number format only governs what you see on the screen, not the underlying value (*). The ROUND function modifies the value returned by the formula.
(*) There is an exception: if you select the check box "Set precision as displayed" in File > Options > Advanced > When calculating this workbook, then Excel will round all values as they are displayed on the screen.
