How can I subtract 111 from a number like 097 and get 987, using .
 

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

097 in A1
111 in A2

=A1+1000-A2
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Viper5963" wrote in message
...
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

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

Not sure since 97-111 equals -14. Would this work?

=MOD(97-111,1000)

returns 986.

--
Dana DeLouis
Win XP & Office 2003


"Viper5963" wrote in message
...
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

hi,
The question is not clear.
Supposing A1=097,A2=111, 097-111 gives -14.
If you mean that if the result is negative, add 1000 to that,then a formula
can be arrived as
=if(A1-A2<0,A1-A2,A1-A2+1000)
Thank you,
K.S.Warrier


"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

I am trying to subtract 1 from each position in my number, example position
1=0, position 2=9, position 3=7. 0-1 = -1, 9-1=8, 7-1=6. If the number in
any positon is a zero(0), then I want the new number to become a 9, instead
of a -1. I'm sorry I didn't make that clear in my first post.

Thanks,
Viper5963

"RagDyeR" wrote:


097 in A1
111 in A2

=A1+1000-A2
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Viper5963" wrote in message
...
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

On Mon, 8 Nov 2004 07:18:01 -0800, Viper5963
wrote:

I am trying to subtract 1 from each position in my number, example position
1=0, position 2=9, position 3=7. 0-1 = -1, 9-1=8, 7-1=6. If the number in
any positon is a zero(0), then I want the new number to become a 9, instead
of a -1. I'm sorry I didn't make that clear in my first post.

Just add 1000. Or, if the number of digits being subtracted can vary, add
10^numdigits


--ron