Which formula almost works?

Tip: a formula either works (returns the correct result) or it doesn't.
There is no "almost works"!!!!! <g

Create a 2 column table like this:

...........A..........B
1.....0.000......1
2.....0.100......2
3.....0.200......3
4.....0.275......4
5.....0.350......5
6.....0.425......6
7.....0.500......7
8.....0.575......8
9.....0.650......9
10...0.750......10

Then:

=IF(F1<=100,"Inc",VLOOKUP(AC1,A$1:B$10,2))

If F1 is *empty* it will return "Inc" since an empty cell evaluates to 0 and
0 <=100.

If any value in AC = 0.750 the result will be 10.

If any cell in AC is *empty* the result will be 1 since an empty cell
evaluates as 0. If you need to account for empty cells let us know.

Biff

"bob" wrote in message
...
This almost works. But it yields values that are 1 less than they should
be.
Examples: .409 in column AC yields 4 instead of 5; .609 equals 7 instead
of
8; and so on.

Thanks,
Bob

"ShaneDevenshire" wrote:

Hi,

I like using LOOKUP but in most cases that is not an option because you
have
more than 2 columns in the lookup table or you want an exact match, in
which
case I would suggest the standard approach:

=IF(F1<=100,"Inc",VLOOKUP(AC1,BA$1:BB$10,2))

Where AC1 is the value you want to look up and BA1:BB10 is the range
containing the lookup table.

This formula can also be done as a stand alone formula:

=IF(F1<=100,"Inc",VLOOKUP(AC1,{0.099,1;0.199,2;0.2 74,3;0.349,4;0.424,5;0.499,6;0.574,7;0.649,8;0.749 ,9;1,10},2))

One final comment on VLOOKUP verses LOOKUP, you will find that most users
and companys use VLOOKUP and far fewer use LOOKUP, given a choice then it
is
better to use what other user's are comfortable with. However, there are
some very niffty uses of LOOKUP which can't be duplicated with VLOOKUP!
So
kodos to RD for using it!
--
Cheers,
Shane Devenshire


"bob" wrote:

In column AC I have values that range from .000 to 1.000.
In column F I have values that range from 1 to 2,000.

In column AV, I want to place a value from 1 to 10, depending on the
corresponding value in column AC. The only exception is that if column
F
<=100, the correspinding value in AV should be "Inc." (for Incomplete)

The values in AC should correspond to the values in AV, as follows:

.000 to .099 = 1
.100 to .199 = 2
.200 to .274 = 3
.275 to .349 = 4
.350 to .424 = 5
.425 to .499 = 6
.500 to .574 = 7
.575 to .649 = 8
.650 to .749 = 9
.750 to 1.000 = 10

thanks,
Bob