WHY THEY ARE THE SAME

E

EXCEL$B!!(BNEWS

HI,
I calculate surplus bwtween two values,


for instance, subtract a from b as follows

22000000002500025200 -22000000002500025000

the result is 0,

why,

how can I solve this problem.

thank you
 
D

David McRitchie

Excel only maintains 15 significant digits, look at specifications in HELP.
Why do you want such numbers, If you aren't going to use them arithmetically
then they are probably IDs and should be text not numbers.
 
O

Opinicus

[email protected] wrote


I calculate surplus bwtween two values,
Click to expand...
for instance, subtract a from b as follows
Click to expand...
22000000002500025200 -22000000002500025000
Click to expand...



This is the same as 2.2E+19 - 2.2E+19. Therefore:


the result is 0,


why,
Click to expand...



From Help:



About calculation in workbooks >

Precision of calculation >



15 digit precision: Excel stores and calculates with 15
significant digits of precision.


how can I solve this problem.
Click to expand...

I don't think you can unless someone has a macro or plug-in
that will increase Excel's precision.
 
J

JulieD

Hi

basically because excel can only handle 15 significant figures and you've
got 20, however, here's a UDF that you can use to find the difference
between the two numbers as long as the value you put in first is greater
than the value you put in second and the two text strings are the same
lengths.

Please note, this isn't fully tested so please check your results carefully.
'----------
Function subtracttext(firstval As String, secondval As String)
If Len(firstval) <> Len(secondval) Then
MsgBox "values need to be of same length"
Exit Function
End If
k = 0
For i = Len(firstval) To 1 Step -1
a = Mid(firstval, i, 1)
b = Mid(secondval, i, 1)
If Int(a) - k <> Int(b) Then
If Int(a) - k > Int(b) Then
j = CInt(a) - k - CInt(b)
k = 0
Else
j = CInt(a) - k + 10 - CInt(b)
k = 1
End If
Else
j = 0
k = 0
End If
Output = Output & j
Next
subtracttext = Int(StrReverse(Output))
End Function
'-----------

please post back if you need help using this UDF.

Regards
JulieD
 
D

Dana DeLouis

Here's a shorter version of something I use:
= Subtract("22000000002500025200", "22000000002500025000")

returns 200.

Function Subtract(n1 As String, n2 As String)
'// n1-n2
Subtract = CDec(0) + n1 - n2
End Function

HTH
 
J

JulieD

Hi Dana

thought someone would neaten up my code - didn't expect it to quite so
dramatic :) still it was fun creating it.

Thanks
JulieD
 
O

Opinicus

Dana DeLouis said:
Here's a shorter version of something I use:
= Subtract("22000000002500025200", "22000000002500025000")
returns 200.
Function Subtract(n1 As String, n2 As String)
'// n1-n2
Subtract = CDec(0) + n1 - n2
End Function
Click to expand...

Cool.

After entering the function into the worksheet I set A3 to
=Subtract (A2, A1). When I enter numbers as text strings
(precede the number with ') in A1 and B1, the function will
return the difference in A3. The strings can be any length
and don't have to be the same length. However the function
still runs into the 14 digits of precision
limitation--unfortunately... ;-(
 
E

EXCEL$B!!(BNEWS

dear Dana

it is so nice.

but i cannot understand the meaning of CDec(0)

why two texts can be Subtracted like n1 - n2
 
T

Tushar Mehta

Look up CDec in XL VBA help.

CDec(0) ensures that the entire arithmetic operation will use the
decimal data type.

n1 and n2 are automatically coerced by VBA to the decimal data type.
This implicit data type conversion can sometimes do unexpected things.

In any case, I am not sure why Dana coded the function as he did. It
can be reduced to:

Function Subtract(a, b)
Subtract = CDec(a) - CDec(b)
End Function

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
D

David McRitchie

in that case Dana might have just as easily have used:

Function Subtract(a, b)
' Result with CDEC must not exceed 14 significant digits
Subtract = CDec(a) - b
End Function
 
Top