Services & ResourcesWolfram ForumsMathGroup Archive

[Date Index] [Thread Index] [Author Index]

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

• References:
  • Multiple > and < calculations [great than & less than]
    • From: "Geezer" <clive@gemnet.co.uk>