NUMBERS

verluc

Well-known Member
Joined
Mar 1, 2002
Messages
1,451
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.
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

Well-known Member
Joined
Oct 17, 2002
Messages
943
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

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
Mar 1, 2002
Messages
1,451

ADVERTISEMENT

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
 

verluc

Well-known Member
Joined
Mar 1, 2002
Messages
1,451
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,958
Members
414,352
Latest member
macquarie_jchan58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top