PDA

View Full Version : Best way to capture digits to left of decimal place?



Jennifer
02-26-2017, 10:40 PM
The Int() and fix() functions look to be equally good at isolating the digits to the left of the decimal place. Is that correct?



R/C
C
D


4
x
function


5
1
int(abs(x)) = '1', fix(abs(x)) = '1'


6
-1
int(abs(x)) = '1', fix(abs(x)) = '1'


7
123.456
int(abs(x)) = '123', fix(abs(x)) = '123'


8
-123.456
int(abs(x)) = '123', fix(abs(x)) = '123'


9
123.99999
int(abs(x)) = '123', fix(abs(x)) = '123'


10
-123.99999
int(abs(x)) = '123', fix(abs(x)) = '123'


11
123.999999999999000
int(abs(x)) = '123', fix(abs(x)) = '123'


12
-123.999999999999000
int(abs(x)) = '123', fix(abs(x)) = '123'


13
12.999999999999900
int(abs(x)) = '12', fix(abs(x)) = '12'


14
1.999999999999990
int(abs(x)) = '1', fix(abs(x)) = '1'


15
0.999999999999990
int(abs(x)) = '0', fix(abs(x)) = '0'



Is there a better way?

mikerickson
02-27-2017, 12:02 AM
Excel does not have FIX function. But it does have the function FIXED, which rounds values.


FIXED(1.6,0) = 2
INT(1.6)= 1


VBA does have a Fix function, that rounds towards 0, as opposed to Int with returns the integer that is lower than the given value


Int(-77.2) = -78
Fix(-77.2) = -77

Jennifer
02-27-2017, 06:00 AM
Excel does not have FIX function. But it does have the function FIXED, which rounds values.

FIXED(1.6,0) = 2
INT(1.6)= 1

VBA does have a Fix function, that rounds towards 0, as opposed to Int with returns the integer that is lower than the given value

Int(-77.2) = -78
Fix(-77.2) = -77

I'm doing this in VBA. I suppose I should have said that, but since this is VBA Express, I guess I assumed it would be about VBA. ;-)

I also used the Abs() function, which, I think, makes Int() and Fix() work the same, no? I think int(abs(x)) = fix(x), correct?

But my question was whether this is the best way to capture just the digits to the left of the decimal place. Is there a better way or are there any problems with this way?

I know that an alternative is to use logs, but VBA stupidly only offers natural logs, so I have to either use worksheet functions or perform a log conversion and then there are different rounding errors.

What I really want to do is find out how many significant digits there are to the left of the decimal place.

PS: I find it a little ironic that in a VBA forum, the word "VBA" is flagged as a spelling error. ;-)

p45cal
02-27-2017, 08:05 PM
deleted, not relevant.

mikerickson
03-01-2017, 07:44 AM
Converting natural logs to base 10 is easy. Try this


Function CountLeftNumber(aNumber As Double) As Double
CountLeftNumber = 1 + Int(Log(Abs(aNumber)) / Log(10))
End Function

CountLeftNumber(-123.456) = 3

snb
03-01-2017, 07:57 AM
In VBA no better methods than Int or fix.


Sub M_snb()
MsgBox Int(99999.99)
MsgBox Fix(99999.09)
End Sub