"Jeff" wrote in message
u...
In article ,
says...
Assuming you want *exact* Sum Insured amounts. For example: Northwest
$190,000 and not: Northwest $193,250.

See this screencap:

http://img211.imageshack.us/img211/2...productjl7.jpg



Biff,

Appreciate your prompt response , formula is exactly as required.

Out of interest , sum insureds are in $5000 increments , how would you
modify your formula to round the user input amount to the nearest $5000
sum insured.

Regards

Jeff

There are several ways to deal with that. How about making sure they enter
an amount that is a multiple of 5000? You can do this with data validation.

Based on my screencap:

The Sum Insured amount is entered in cell B14
Select cell B14
Goto the menu DataValidation
Allow: Custom
Formula: =AND(ISNUMBER(B14),B140,MOD(B14,5000)=0)
Click the Error Alert tab
You can enter a message like this:

Title: Sum Insured

Error Message: Amount entered must be in multiples of $5000
OK out

Now, if someone (manually**) enters *anything* other than a number that is a
multiple of 5000 this message will appear:

http://img513.imageshack.us/img513/3...tavalidqg7.jpg

** Data validation can be defeated by copy/paste or drag and drop.

--
Biff
Microsoft Excel MVP