MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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)