Formula for growth chart?

D

DORI

We have a chart that we use to check the growth in babies. The first column
(A1:A250) is the baby's age (text format in weeks and days, ie 25w3d). The
columns B2:J2 are titles as 1st, 5th, 10th, 25th, 50, 75th, 90th, 95th, and
99th percentile. The cells B3:J250 are numbers related to babies weight. We
measure the baby's weight and enter the value in a cell K2. We also enter the
age of the baby in another cell K3.
Is there a formula that when we enter the values for K2 and K3, the result
appear in another cell K4 and show us the closest percentile for a that baby?
The formula must look at the age first and go along that row to find closest
number to the weight and then move up in that column to get to the title of
that column(for example 50th Percentile) and show the "50th Percentile" in K4
cell.
Thank you in advance,
DORI
 
M

Max

One way, perhaps this might suffice ..

Sample construct at:
http://cjoint.com/?lwkWZBtppZ
GrowthChart_Formula_Dori_wks.xls

Put in K4:
=INDEX($A$2:$J$2,MATCH(K2,OFFSET($A$2:$J$2,MATCH(K3,A3:$A$250,0),),1))

The above assumes that the wts are in ascending order from the 1st to 99th
percentiles (logically so? <g>), and uses match_type 1 to locate the largest
value that is less than or equal to lookup_value for the percentile
 
M

Max

Oops, lines:
Put in K4:
=INDEX($A$2:$J$2,MATCH(K2,OFFSET($A$2:$J$2,MATCH(K3,A3:$A$250,0),),1))

should have read as:

Put in the formula bar for K4,
then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=INDEX($A$2:$J$2,MATCH(K2,OFFSET($A$2:$J$2,MATCH(K3,A3:$A$250,0),),1))

(The formula needs to be array-entered)
 
M

Max

Try this revised array formula which provides better results ..

Revised sample construct at:
http://cjoint.com/?lwoVBRGV6q
Revised_GrowthChart_Formula_Dori_wks.xls

Put in K4, then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):

=INDEX($A$2:$J$2,
MATCH(INDEX(OFFSET($B$2:$J$2,MATCH(K3,A3:$A$250,0),),
MATCH(MIN(ABS(OFFSET($B$2:$J$2,MATCH(K3,A3:$A$250,0),)-K2)),
ABS(OFFSET($B$2:$J$2,MATCH(K3,A3:$A$250,0),)-K2),0)),
OFFSET($A$2:$J$2,MATCH(K3,A3:$A$250,0),),1))
 
D

DORI

Dear Max,
Thank you SO MUCH. You area a genius! You saved us a lot of work. I did what
you gave me and it works great.
Thanks again,
Dori
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top