Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    selecting range description (Excel2003)

    I have a large data table with a column of per unit costs. I want to be able to use descriptive ranges in the Pivot tables generated by this list. I think I should add another column for pivoting that would give a description such as "50-99.99" or "0.00 to 49.99" or "100-499.99" but I am not sure which function would be best to do that. I will try playing around with nested IFs but I am concerned my logic might get mixed up "in there" and I would get bad results on my table. Is there a better function?

    =IF(AND(D9>0,D9<49.99),"0.00-49.99",IF(AND(D9>50,D9<99.99),"50-99.99",IF(AND(D9>100,D9<499.99),"100-499.99",IF(AND(D9>500,D9<999.99),"500-999.99",">1000.00"))))
    =IF(d9<0,"less than 0",AND(D9>0,D9<49.99),"0.00-49.99",IF(AND(D9>50,D9<99.99),"50-99.99",IF(AND(D9>100,D9<499.99),"100-499.99",IF(AND(D9>500,D9<999.99),"500-999.99",">1000.00"))))

    The top one works fine until I try to add a "less than zero"test as shown on the one below.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: selecting range description (Excel2003)

    Hi there

    I think this one will work for you, but I have yet to get the less than 0 problem sorted quite yet, so let's hope someone else can tweak and assist <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    I have used a mixture of if, match and index in my formula and a seperate table for the values you require(these can be hidden away on a seperate ws if you want.

    The attached workbook should assit ypu with my thoughts but I used:

    =IF(B2>MATCH(B2,$I$4:$I$8),INDEX($J$4:$J$8,MATCH(B 2,$I$4:$I$8)),"")
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: selecting range description (Excel2003)

    Please don't see this as criticism. VLOOKUP can be used here (it combines INDEX and MATCH in one function). If you add a sufficiently large negative value to the lookup table, you can handle negative values too. See attached version.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: selecting range description (Excel2003)

    Excellent, I was looking to use VLookup but couldn't work out the issue of the negative aspect in my function.

    I saw -9.99999999999999E+307 in another thread the other day and this seems to be a little "trick" that has passed me by, can you explain please?


    PS Sorry about the other night I was like a bear with a sore head and took it the wrong way <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: selecting range description (Excel2003)

    9.99999999999999E+307 is the largest positive number and -9.99999999999999E+307 the largest negative number that can be entered in an Excel worksheet, see Specifications > Specifications for calculations in the online help. So using -9.99999999999999E+307 as the first entry in the lookup table ensures that any numeric value will result in a valid lookup.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: selecting range description (Excel2003)

    Thank you, that one is now emblazened in the back of my mind, but I still can't work out how your vlookup sees that say 2.50 is 0-49.99 value when 2.50 is not in the list!!!

    (or should I accept it just does <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    Jerry

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: selecting range description (Excel2003)

    VLOOKUP has 4 arguments:
    - search value
    - lookup table
    - lookup column
    - approximate (TRUE/FALSE)

    Case A: If the 4th argument is TRUE or if it is omitted, VLOOKUP searches the first column of the lookup table and stops at the first value that is less than or equal to the search value, then returns the corresponding value from the lookup column. If there is no value in the first column less than or equal to the search value, VLOOKUP returns #N/A. This only works correctly if the first column of the lookup table is sorted in ascending order.

    Case B: If the 4th argument is FALSE, VLOOKUP looks for an exact match in the first column, and if there is no exact match, it returns #N/A. The first column does not have to be sorted in this situation.

    If you look at the formulas in the workbook I attached, you'll see that the 4th argument of VLOOKUP has not been specified, so Case A applies. If the search value is 2.50, the search stops at the first value less than or equal to 2.50, i.e. at 0, and VLOOKUP returns the corresponding value 0-49.99.

    See the online help for VLOOKUP.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: selecting range description (Excel2003)

    Thank you very very much...and all these years I have been..... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: selecting range description (Excel2003)

    I *knew* there was a simpler way..I had seen that before but the idea got through one of the holes in my head and I had to be reminded. Thanks to you both!

Posting Permissions

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