+ Reply to Thread
Results 1 to 6 of 6

Using a IF function to return data in single cell based off of a range

  1. #1
    Forum Contributor
    Join Date
    07-31-2009
    Location
    Lincoln, USA
    MS-Off Ver
    Excel 2007 and excel 2010
    Posts
    142

    Using a IF function to return data in single cell based off of a range

    Hello everyone and thank you in advance.

    Mainly I'm trying to have my spreedsheet return a value in a single cell based off of a range of data.

    what i want is to place a perctage value in cell A2 and have the spreedsheet compaire this to the date in the below table then place the corresponding number in cell A1

    Example = if i place 92.75% in cell A2 cell A1 is populated with a 3

    Example below

    Cell to return number = A1
    Cell with Data to compaire to table = A2

    Data in 3 collums

    5 100.00% 100.00%
    4.75 99.99% 99.00%
    4.5 98.99% 98.00%
    4.25 97.99% 97.00%
    4 96.99% 96.00%
    3.75 95.99% 95.00%
    3.5 94.99% 94.00%
    3.25 93.99% 93.00%
    3 92.99% 90.00%
    2.75 89.99% 89.00%
    2.5 88.99% 88.00%
    2.25 87.99% 87.00%
    2 86.99% 86.00%
    1.75 85.99% 85.00%
    1.5 84.99% 84.00%
    1.25 83.99% 83.00%
    1 82.99% 0.00%


    Any thoughts? thanks
    Last edited by fireguy7; 11-17-2010 at 02:29 PM.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Using a IF function to return data in single cell based off of a range

    ...... compaire this to the date in the below table .....
    I presume you mean data,

    In D3
    Please Login or Register  to view this content.
    To return a number from a given percentage
    or
    Please Login or Register  to view this content.
    To return a percentage from a given number

    Hope this helps
    Last edited by Marcol; 11-16-2010 at 03:11 PM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Forum Contributor
    Join Date
    07-31-2009
    Location
    Lincoln, USA
    MS-Off Ver
    Excel 2007 and excel 2010
    Posts
    142

    Re: Using a IF function to return data in single cell based off of a range

    close but not quite. Sorry the graph didn't copy very well into the thread.

    I'll use a 2 line chart to make this easier.

    What i want to do is put a percentage in Cell A2 and have the spreed sheet compaire this % to the chart and place the corresponding number in collumn A when the % in Cell A2 is between the appropirate percentages.

    With the below numbers if i put a 98% in A2, then A1 would populate a 4.



    A B C

    5 100% 100%

    4 95% 99%

    I was thinking a mutiple If then statment but i'm still new to excell formulas.

    Thanks a ton and sorry to be pain.

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Using a IF function to return data in single cell based off of a range

    Try this

    In A1
    Please Login or Register  to view this content.

    When your table of results is in Columns F:H, cloumn H is not really required.

    You are only allowed 7 nested IFS with 2003, so you cannot use that method for this example.

    Hope this helps
    Attached Files Attached Files
    Last edited by Marcol; 11-17-2010 at 01:23 PM. Reason: Accidentally left some macros from other threads in the attachment

  5. #5
    Forum Contributor
    Join Date
    07-31-2009
    Location
    Lincoln, USA
    MS-Off Ver
    Excel 2007 and excel 2010
    Posts
    142

    Re: Using a IF function to return data in single cell based off of a range

    That is perfect! You rock!

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Using a IF function to return data in single cell based off of a range

    Happy to help

    Note
    If you enter more than 100% the formula will return 5, if this is not correct change this

    =IF(ISNA(INDEX(F2:F18,MATCH(A2,G2:G18,-1),1)),5,INDEX(F2:F18,MATCH(A2,G2:G18,-1),1))

    You could use "" to return an empty string or maybe "Must be 100% or Less" to give an error message, or whatever you need returned.

+ 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