       Re: Multiple > and < calculations [great than & less than]

• To: mathgroup at smc.vnet.net
• Subject: [mg55334] Re: [mg55271] Multiple > and < calculations [great than & less than]
• From: Daniel Lichtblau <danl at wolfram.com>
• Date: Sat, 19 Mar 2005 04:46:14 -0500 (EST)
• References: <200503181033.FAA14605@smc.vnet.net>
• Sender: owner-wri-mathgroup at wolfram.com

```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
>
> 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

ltab10k = lookup[10^4,10^7];

Now we look up 10^4 random values in this range.

In:= Timing[Do[ltab10k[Random[Integer,10^7]], {10^5}]]
Out= {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:= Timing[Do[ltab100k[Random[Integer,10^7]], {10^5}]]
Out= {1.84 Second, Null}

Similar ideas show up in notes at the URLs below, and others in their

http://forums.wolfram.com/mathgroup/archive/2000/Nov/msg00342.html
http://forums.wolfram.com/mathgroup/archive/2002/Sep/msg00277.html
http://forums.wolfram.com/mathgroup/archive/2003/Feb/msg00375.html

Daniel Lichtblau
Wolfram Research

```

• Prev by Date: Re: Re: J/Link problem on Mac OS X
• Next by Date: Re: Distance from point to set
• Previous by thread: Multiple > and < calculations [great than & less than]
• Next by thread: Re: Multiple > and < calculations [great than & less than]