Looking for formula (Possibly If and)??

[Fbs1960]

I am looking for a formula that will provide the following:

In cell A1 I have the following values that are available via a drop down box:
Value
100%
<99.90%
<98.90%
<97.90%
<96.90%

In cell B1 I am trying to have a multiplier for each value come up automatically
100% = a multiplier value of 0
<99.99% = a multiplier value of 1
<99.90% = a multiplier value of 2
<98.90% = a multiplier value of 3
<97.90% = a multiplier value of 4
<96.90% = a multiplier value of 5

Appreciate any assistance
Fbs

[Brumbot]

Hi fbs1960,

You can use a CHOOSE function for this one. Assuming the dropdown is pulling from a list of values and outputting a number between 1 and 6 in cell A1 based on the selection in the dropdown box, the formula will be as follows:

In cell B1,

Please Login or Register  to view this content.

Let me know if this works for you.

[Fbs1960]

Did not work. Cell A1 dropdown box has values from 100% to <96.5% as above. Do I need to have a second list of values between 0 and 5 that cell B1 looks at before determining value. Eg. If value in A1 is <98.9% then cell B1 will look at a list of values between 0 and 5 and select 3. That is why I thought it may be an "IF" formula.

[samba_ravi]

Please Login or Register  to view this content.

try this