Vector lookup with 2 criteria

The ranges are fixed for now. I am limiting this between ages 10 and 19, since I am working with adolescents. The CDC table actually run between 2 and 20 years. If I can get this working, I can go back and include the entire age range.

Let me try pasting the code in my real worksheet. Thanks.

It is still pulling the same exact L value for two kids at 161.5 months, one male and one female. The L value should be different due to gender.

I tried the =sumproduct code from NBVC in K2 and it diplayed .00000000000

Thanks NBVC, it works. I am elated. I have one final thing a forumla to compute the Z score in the worksheet example.

I think I got it write, because I checked it against a website that has a z score calulator, but if you could look at the forumula I wrote, I would appreciate it.


I am attaching a seperate sheet of the Z-score calculation.

Something isn't computing right. My z-scores should be ranging from -3 to +3, and most are centering around -0.02 to 0.03. Let me check my formula from the CDC website.

I copied and pasted directly from the CDC page on computing Z-Score.

To obtain the z-score (Z) and corresponding percentile for a given measurement (X), use the following equation:

((X/M)**L) - 1
Z = -------------------------, Lš0
LS

Where X is BMI Which is J2
M is the median which is L2
L is the BoxCox Transformation factor K2
S is the Standard Deviation which is S2.

I know I should be getting a wider range of zscores since 60% of these kids will be overweight or obese.

If you could check this one more time, I would appreciate it. From Original Sample

Tim

I remember I had to put parentheses in the denominator and it works. Thanks, I am through.

Tim

Hello.

I'm a new member, and I have a question about the proposed solution involving IF(Gender=1 [Column I], LOOKUP X, LOOKUP Y). I appreciate the suggestions because I'm dealing with some large sheets that I have to do embedded Lookups in, and I can't get the lookups to sort for criteria properly if you put a gun to my head!

In the example, the vectors in both the "Value if True" and the "Value if False" Segments of the IF Formula have to be reduced to 1/2 of the actual columns, which are B[LastName] and C[FirstName].

That is, the writer says that in the first Lookup, you would only search through B1 to B110/C1 to C110; and in the second Lookup, you would only search through B111 to B119/C111 to C119.

Obviously, this would require sorting by Male and Female binary values in Column I; but my real question is whether this means the solution is *only* viable if you can do such binary sorting on the IF value, e.g., Gender in this case?

For instance, if column I weren't gender, and it were something with 10 or 20 different values, like individual purchase prices, would I be able to use an IF(value, LOOKUP1, LOOKUP2) formula to select them?

Also, what if there were more than 2 values to search?

Thanks much if anyone can help,

S