+ Reply to Thread
Results 1 to 6 of 6

Formula for growth chart?

  1. #1
    DORI
    Guest

    Formula for growth chart?

    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

  2. #2
    Max
    Guest

    Re: Formula for growth chart?

    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
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "DORI" <DORI@discussions.microsoft.com> wrote in message
    news:E6311A43-A417-41EF-9915-16AD0FF4B0FB@microsoft.com...
    > 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




  3. #3
    Max
    Guest

    Re: Formula for growth chart?

    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)
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  4. #4
    Max
    Guest

    Re: Formula for growth chart?

    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))

    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  5. #5
    DORI
    Guest

    Re: Formula for growth chart?

    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

    "Max" wrote:

    > 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))
    >
    > --
    > Rgds
    > Max
    > xl 97
    > ---
    > Singapore, GMT+8
    > xdemechanik
    > http://savefile.com/projects/236895
    > --
    >
    >
    >


  6. #6
    Max
    Guest

    Re: Formula for growth chart?

    Thanks for the feedback !
    Pleased to hear it worked for you.
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "DORI" <DORI@discussions.microsoft.com> wrote in message
    news:223982F5-8C44-4718-87CA-A0FBE91E9F9D@microsoft.com...
    > 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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1