Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Viper5963
 
Posts: n/a
Default 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
  #2   Report Post  
RagDyeR
 
Posts: n/a
Default


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


  #3   Report Post  
Jerry W. Lewis
 
Posts: n/a
Default

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


  #4   Report Post  
Dana DeLouis
 
Posts: n/a
Default

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



  #5   Report Post  
K.S.Warrier
 
Posts: n/a
Default

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



  #6   Report Post  
Viper5963
 
Posts: n/a
Default

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



  #7   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"