Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2008
    Posts
    2

    Unanswered: Evaluating a value to get score from a range

    I am trying to assign a score based on a value from a cell by using a formula.
    I tried to use a "nested if" but I have 10 possible values and the limit is 7.
    Plus it is messy and hard to read. Is there a easier way?

    Below is an example:
    Column B has my values and C is where I want the score
    B C
    Value1 Score
    10
    20
    30
    25
    15

    The score is based on a range stored on another worksheet:
    A B C
    Score MinValue MaxValue
    1 0 10
    2 11 20
    3 21 30
    ...up to
    10 91 100
    Last edited by Roo06; 09-19-08 at 16:57.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you tried a VLookup?
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    VLOOKUP would work, but you'd need to change your Scores table a little. Move the Score column to the end of the range, and name it for ease of reference.

  4. #4
    Join Date
    Sep 2008
    Posts
    2

    Thanks

    That worked like a charm. I have never used VLookUp but its seems really powerful.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    VLOOKUP is great. I have a suite of forms that use it to validate and translate user input!
    HLOOKUP is similarly powerful, and works at right angles to VLOOKUP. It gets used less, though, because we're more familiar with lists and tables that are vertical rather than horizontal.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Personally I hate VLookup, but only because when I've used it it's been across worksheets containing 60K records and any update anywhere forces full cell recalculation... Taking upwards or 15 minutes
    George
    Home | Blog

Posting Permissions

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