On 15 Mar 2007 16:04:11 -0700, "Steve" wrote:

On Mar 15, 3:29 pm, Ron Rosenfeld wrote:

Thanks for the input. Maybe I didn't fully set out the issue. The
State sets rates from zero to 100,000. Premiums are 229 up to and
including 10,000; 233 up to and including 10,500, and so on to 833 for
up to and including 100,000. This means I have 180 entries in the
column of policy amounts and the same number of premium amounts. I
tried to set up the formula for policy amounts exactly matching the
180 breaks set by the state and everything else in between. If my
formula had to deal with multiples of 500 and everything else, there
would be ten million iterations. That's what I'm trying to avoid.

You either didn't try or didn't understand my response.

If the first "bracket" is from 0-10000, then merely set the first entry to 0
instead of the 9500.

Since you wrote that the premiums did not occur in any particular pattern, you
will have to have a separate entry in your table for each bracket. I don't
know of any way around that if there is no mathematical relationship between
the amount and the premium.

So you still just need to offset your table as I wrote.

For example:

$ 0.00 229
$10,000.01 233
$10,500.01 235

=VLOOKUP(amt,tbl,2)

where amt is the amount you are checking; tbl is the two column table set up as
above and extended through to $100,000
--ron