Need to create a new number depending a value of the original number

[[email protected]]

I need to create a new number based on conditional findings of the original number, using 3 sets of criteria as follows:

If a number (no decimals) ends with:

a) 0, 9, 8 or 1 = the new number ends with 8

b) 2 or 3 = the new number ends with 3

c) 4, 5, 6, or 7 = the new number ends with 7

For Example:

400 new number = 408

312 new number = 313

544 new number = 547

Please help, I very much appreciate it!!

Thanks kindly, Dale

[alansidman]

=if(or(A1=0,A1=9,A1=8,A1=1),Left(A1,2)&8,if(or(A1=2,A1=3),Left(A1,2)&3,Left(A1,2)&7))

[Pete_UK]

With your number in A1, you can use this formula (e.g. in B1):

=INT(A1/10)*10+IF(MOD((A1-2)/10,1)*10<2,3,IF(MOD((A1+2)/10,1)*10<5,8,7))

Hope this helps.

Pete