This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.
it can be said, that Excel doesn’t really calculate correctlyThat is a false and misleading statement. Excel calculates according to the rules of floating point arithmetic.
it's not really calculating correctlyNumbers are stored as binary values. Just because you are typing them as base10 values, doesn't mean they are stored in a base10 representation.
x=cdec("-1.23")
x=x + 1.12 + .11
?x
0
x=cdec("12345678901234567890123456789")
?x
12345678901234567890123456789
?len(x)
29
Option Explicit
Public Function Dec2Cur(ByVal parmRngOrValue) As Currency
Static rngArea As Range
Static rng As Range
Static decThing As Currency
If TypeName(parmRngOrValue) = "Range" Then
decThing = CCur(0)
For Each rngArea In parmRngOrValue.Areas
For Each rng In rngArea.Cells
decThing = decThing + Val(rng.Value)
Next
Next
Dec2Cur = decThing
Else
Dec2Cur = CCur(parmRngOrValue)
End If
End Function
Public Function Dec2Dec(ByVal parmRngOrValue) As Variant
Static rngArea As Range
Static rng As Range
Static decThing As Variant
If TypeName(parmRngOrValue) = "Range" Then
decThing = CDec("0")
For Each rngArea In parmRngOrValue.Areas
For Each rng In rngArea.Cells
decThing = decThing + Val(rng.Value)
Next
Next
Dec2Dec = decThing
Else
Dec2Dec = CDec(parmRngOrValue)
End If
End Function
Note: If you pass an area (non-contiguous cells or multiple ranges), you will need surround your formula within another pair of parentheses.=Dec2Dec((B1:B3,C1:C3,F4,I7,F14:G17))