Results 1 to 9 of 9

20051113, 15:27 #1
 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 "5099.99" or "0.00 to 49.99" or "100499.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.0049.99",IF(AND(D9>50,D9<99.99),"5099.99",IF(AND(D9>100,D9<499.99),"100499.99",IF(AND(D9>500,D9<999.99),"500999.99",">1000.00"))))
=IF(d9<0,"less than 0",AND(D9>0,D9<49.99),"0.0049.99",IF(AND(D9>50,D9<99.99),"5099.99",IF(AND(D9>100,D9<499.99),"100499.99",IF(AND(D9>500,D9<999.99),"500999.99",">1000.00"))))
The top one works fine until I try to add a "less than zero"test as shown on the one below.

20051113, 17:49 #2
 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

20051113, 22:13 #3
 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.

20051113, 22:18 #4
 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

20051113, 22:41 #5
 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.

20051113, 22:46 #6
 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 049.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

20051113, 23:10 #7
 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 049.99.
See the online help for VLOOKUP.

20051113, 23:13 #8
 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

20051114, 04:11 #9
 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!