NUMBERS

[verluc]

I have a number in A1 : 158
How can I right a formula that give me the difference between the first number end the second number and the second number and the third number:
Example: in B1 = 1-5 = -4
in C1 = 5-8 = -3
Thanks for help.

 

[ROBINSYN]

If you email you spreadsheet I will take a look.


SynDee@sympatico.ca

 

[Juan Pablo González]

On 2002-11-02 10:36, verluc wrote:
I have a number in A1 : 158
How can I right a formula that give me the difference between the first number end the second number and the second number and the third number:
Example: in B1 = 1-5 = -4
in C1 = 5-8 = -3
Thanks for help.

Use one of the following (in B1)
=LEFT(A1)-MID(A1,2,1)
=INT(A1/100)-INT(MOD(A1,100)/10)
=LEFT(TEXT(A1,"000"))-MID(TEXT(A1,"000"),2,1)

the second and third one will work ok if you put a two digit number, assuming that the real first digit is 0

 

[indiantrix]

This will work if all numbers are 3-digits.

=VALUE(LEFT(TEXT(A1,"0"),1)-MID(TEXT(A1,"0"),2,1))
and
=VALUE(MID(TEXT(A1,"0"),2,1)-RIGHT(TEXT(A1,"0"),1))

/s/ Larry

 

[Juan Pablo González]

You can drop the VALUE formula from there, as well the TEXT formula, since you're already assuming 3 digits, and the ",1" in the LEFT and RIGHT formulas. That way, it would look much nicer.

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-11-02 11:08

 

[Aladin Akyurek]

On 2002-11-02 10:50, Juan Pablo G. wrote:

On 2002-11-02 10:36, verluc wrote:
I have a number in A1 : 158
How can I right a formula that give me the difference between the first number end the second number and the second number and the third number:
Example: in B1 = 1-5 = -4
in C1 = 5-8 = -3
Thanks for help.

Use one of the following (in B1)
=LEFT(A1)-MID(A1,2,1)
=INT(A1/100)-INT(MOD(A1,100)/10)
=LEFT(TEXT(A1,"000"))-MID(TEXT(A1,"000"),2,1)

the second and third one will work ok if you put a two digit number, assuming that the real first digit is 0

Have a look at...

http://www.mrexcel.com/board/viewtopic.php?topic=27456&forum=2

http://www.mrexcel.com/board/viewtopic.php?topic=27486&forum=2

http://www.mrexcel.com/board/viewtopic.php?topic=17575&forum=2&start=10

 

[verluc]

On 2002-11-02 11:04, indiantrix wrote:
This will work if all numbers are 3-digits.

=VALUE(LEFT(TEXT(A1,"0"),1)-MID(TEXT(A1,"0"),2,1))
and
=VALUE(MID(TEXT(A1,"0"),2,1)-RIGHT(TEXT(A1,"0"),1))

/s/ Larry

Hi,this formule give me an error: #NAME
Any idea?
Thanks

 

[Juan Pablo González]

Try the other ones I suggested.

 

[verluc]

On 2002-11-02 13:23, Juan Pablo G. wrote:
Try the other ones I suggested.

Hi,still the same error.
Must be for example:
A1 = 691
B1 = 3 ( formule 9-6)
C1 = 8 ( formule 9-1)
Thanks for suggestions.

 

[Juan Pablo González]

Book3
	A	B	C	D
1	691	-3	8
2		-3	8
3		-3	8
Sheet1

If you need a positive answer, just use ABS().