Formula
Posted by Brian on November 11, 2001 12:07 PM
Aloha....
I am trying to enter a number between 1-99 in A1 and this number is converted to 20 in A2.
1-99 = 20
100-199 = 25
200-299 = 30
etc.
The converted numbers are all predetermined
Mahalo
Brian
Posted by Aladin Akyurek on November 11, 2001 12:32 PM
Aloha (whatever this might mean)
One with IF (this must be already familiar to you):
=IF(A1<99,20,INT(A1/100)*5+20)
Another with Boolean terms:
=(A1<99)*20+(A1>99)*(INT(A1/100)*5+20)
You can opt for one of the above. Even add some control:
=IF(ISNUMBER(A1),one-of-the-above-formulas,""
============
Posted by Richard S on November 11, 2001 5:57 PM
Re: VLOOKUP option
If you set up a table with the lower value of each range in one column and the expected results in the next, you can use VLOOKUP, ie from cell A1 to Cell B4
1 20
100 25
200 30
300 35
If you enter in cell C1 (or whatever) =VLOOKUP(C1,A1:B4,2) the formula should return the value you want. This give you a bit of flexibility if there are a lot number ranges.
HTH
Richard
Posted by Brian on November 11, 2001 7:03 PM
Re: VLOOKUP option
Aladin and Richard,
Much MAHALO for your time and efforts.
Brian
Posted by Richard S on November 11, 2001 7:33 PM
eh? (nt)
