Results 1 to 2 of 2

Thread: Formula Troubles

  1. #1

    Formula Troubles



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Gurus,

    This is my first time using this blog and I am hoping to get some much needed help. I am trying to figure out a good formula to look up the correct value in a table. Here's the problem. I have a value in a data set for a particular type of product (in this case fruit) and need to choose what percentile the value represents. For example, I have 4,000 crates of apples, I need to be able to reference the 4,000 crates of apples represents the 19th percentile. See my table below:


    Fruit 10th 11th 12th 13th 14th 15th 16th 17th 18th 19th 20th 21st 22nd 23rd 24th
    Apples 3,380 3,476 3,534 3,600 3,665 3,714 3,781 3,854 3,913 3,977 4,032 4,086 4,151 4,197 4,243
    Pears 2,553 2,639 2,661 2,734 2,827 2,865 2,911 3,000 3,086 3,203 3,291 3,344 3,402 3,435 3,501
    Grapes 3,894 3,978 4,050 4,095 4,148 4,222 4,280 4,347 4,436 4,533 4,610 4,695 4,765 4,808 4,863

    Look forward to your comments & recommendations.

    Thanks,
    Laura

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    If your table is in A1:P4, and you enter Apples in T1 and 4000 in T2, then try formula:

    =INDEX(B1:P1,MATCH(T2,INDEX(B2:P4,MATCH(T1,A2:A4,0),0)))


Posting Permissions

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