Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Retreive a Value from a Chart with Ranges (Excel 97)

    Hi!
    I'm sure someone will find this to be very easy.... I'm needing to retrieve a value from a chart that has ranges. Normally I do a lot of vlookups, and nested if's, however there has to be an easier, more appropriate way to determining the value when there is a "range" as opposed to a defined value. Below is an example of what I'm trying to do:

    I need to refer to the chart and determine what % discount to apply (below is the chart)... so if the bill is $2,500, I'll need a formula to refer to the cell with the $2,500 in it and look up the low end of the range and the high end of the range and determine which range it falls into, and then select the proper percentage. The percentage I would need in this case is 30.5%. Any help on this one would be great!
    Thanks!!
    LJM <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    $0.01 $1,809.99 0.0%
    $1,810.00 $2,069.99 15.0%
    $2,070.00 $2,329.99 21.4%
    $2,330.00 $2,849.99 30.5%
    $2,850.00 $3,369.99 31.4%
    $3,370.00 $100,000.00 32.0%

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Retreive a Value from a Chart with Ranges (Excel 97)

    How about something like
    <pre>=VLOOKUP(D1,$A$1:$B$6,2)</pre>

    Assuming it is set up like the table

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><tr><td align=center valign=bottom>1</td><td align=right valign=bottom>$0.01 </td><td align=right valign=bottom>0.00%</td><td align=right valign=bottom>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retreive a Value from a Chart with Ranges (Excel 97)

    What do you want to do if the amount is greater than $100,000?
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Retreive a Value from a Chart with Ranges (Excel 97)

    Anything over $100,000 would also be at the 32%.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Retreive a Value from a Chart with Ranges (Excel 97)

    Hi, thanks for the response. The vlookup formula you replied with works if I'm looking for a specific value in the chart, however these are ranges.... for example the $2,500 fits into the fourth range I listed ($2,330.00 to $2,849.99), so I'd want the percentage of 30.5% to be returned as my value. With the vlookup formula you mentioned the $2,500 is not in the data range so a #NA would show up as there was no match. If you have any other ideas I'll take them... thanks!!
    LJM

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retreive a Value from a Chart with Ranges (Excel 97)

    Then Steve's formula and table WILL work. Have you tried it?
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Retreive a Value from a Chart with Ranges (Excel 97)

    Silly me... I always use the false command in my vlookup formulas... I wasn't thinking outside the box... Steve will need to ignore my silly email saying it didn't work... it does work.
    Thanks to both of you!!
    LJM

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Retreive a Value from a Chart with Ranges (Excel 97)

    False is for an exact match. Without the FALSE it works as you wanted it to work, to allow a range of values to give a single value. You only need the "lower limit" of each "region" and the "value" and the vlookup will do the rest...

    Steve

Posting Permissions

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