I can't figure out how you would get -186 instead of -14, but
=MOD(97-111,1000)
is 986.

Jerry

Viper5963 wrote:

I am trying to take a number like 097, subtract 111. I don't want my new
number to become -186, instead I want 986. I am trying to use an IF
statement that looks like this:

=IF(LEFT(A1)
="0","9",LEFT(A1)-1&IF(MID(A1,2,1)="0","9",MID(A1,2,1)-1&IF(RIGHT(A1)="0","9",RIGHT((A1)-1))))

This works fine for the first number, but if I enter new numbers in the
column and copy the formula down, I get invalid data . Please help