Geezer wrote:
> USING EXCEL
> I need a simple formula..............
>
> I want to give a set value to a number within a given range
> e.g cell value is 1038
> - the look up range is below
> - so the formula should return the value 'A'
> - because the example cell value is between 1000 and 1500
>
> Value Range Result
> <999
> 1000-1500 A
> 1501-2500 B
> 2501-3500 C
> 3501-4500 D
> 4501-5500 E
> 5501-7000 F
> 7001-10000 G
> 10001-15000 H
> 15001-20000 I
> 20001-30000 J
>
>>30001 K
>
>
> There are over 10,000 cells to run the formula through
>
> Help Please you can reply directly if you wish
> Many Thanks
> G
This comes up from time to time. One method that will use a fast
divide-and-conquer behind the scenes is evaluation of an
InterpolatingFunction, which we create with InterpolatingOrder->0 to
make it piecewise constant. We use numbers 1,2,... for the second value.
You then need a fast method to convert these to your own values e.g.
A,B,... but this only requires a constant time table lookup. To indicate
speed we make a test function below.
points[n_,hi_] := Union[Table[Random[Integer,{1,hi-1}],{n}]]
lookup[n_,hi_] := Module[{pts=points[n,hi],len},
len = Length[pts];
Interpolation[
Transpose[{Prepend[Append[pts,hi],0],Range[len+2]}],
InterpolationOrder->0]
]
Create a lookup table with around 10^4 entries with random values from 1
to 10^7-1, augmented by the endpoints (the actual size is a bit less as
per the birthday paradox).
ltab10k = lookup[10^4,10^7];
Now we look up 10^4 random values in this range.
In[13]:= Timing[Do[ltab10k[Random[Integer,10^7]], {10^5}]]
Out[13]= {1.65 Second, Null}
We do the same, but for a table with 10^5 entries in that range.
ltab100k = lookup[10^5,10^7];
In[15]:= Timing[Do[ltab100k[Random[Integer,10^7]], {10^5}]]
Out[15]= {1.84 Second, Null}
Similar ideas show up in notes at the URLs below, and others in their
respective threads.
http://forums.wolfram.com/mathgroup/archive/2000/Nov/msg00342.htmlhttp://forums.wolfram.com/mathgroup/archive/2002/Sep/msg00277.htmlhttp://forums.wolfram.com/mathgroup/archive/2003/Feb/msg00375.html
Daniel Lichtblau
Wolfram Research