finding how much decimal places numbers have

dikken20

Board Regular
Joined
Feb 25, 2009
Messages
130
Office Version
  1. 2010
Platform
  1. Windows
Hey all,

in short, my purpose is to find the maximum decimal places among 6 prices.
meaning,
if I have the next prices (I'll list only 3 prices in the example, don't need more):
stock A: 100
stock B: 100.1
stock C: 100.11
then I would like to get the answer 2 as stock C has 2 decimal places which are more than 1 or 0 of stocks B and A.

so I wrote a function that does that, and for some reason when I play with the prices to check if it works it has some issues (in particular if all prices has no decimal and I add one decimal to a price it's ok, but as soon as I add another decimal to another stock [two stocks with one decimal each] then suddenly I get the answer 2 instead of 1)

can someone give me a hand on this please ? thanks

Function DecimalPlaces(ByVal bp1!, ByVal bp2!, ByVal bp3!, ByVal ap1!, ByVal ap2!, ByVal ap3!) As Byte 'bp = Bid Price
Dim nodp As Byte 'nodp = number of decimal places
nodp = 0
If (bp1 * 10 - CInt(bp1 * 10) <> 0) Or (bp2 * 10 - CInt(bp2 * 10) <> 0) Or (bp3 * 10 - CInt(bp3 * 10) <> 0) Or _
(ap1 * 10 - CInt(ap1 * 10) <> 0) Or (ap2 * 10 - CInt(ap2 * 10) <> 0) Or (ap3 * 10 - CInt(ap3 * 10) <> 0) Then
nodp = 2
ElseIf (bp1 - CInt(bp1) <> 0) Or (bp2 - CInt(bp2) <> 0) Or (bp3 - CInt(bp3) <> 0) Or _
(ap1 - CInt(ap1) <> 0) Or (ap2 - CInt(ap2) <> 0) Or (ap3 - CInt(ap3) <> 0) Then
nodp = 1
Else
nodp = 0

End If

DecimalPlaces = nodp
End Function
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">J6</th><td style="text-align:left">{=MAX(<font color="Blue">LEN(<font color="Red">MID(<font color="Green">I8:I10,1+SEARCH(<font color="Purple">".",I8:I10&"."</font>),255</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />


Excel 2013
IJ
65
7
8100.6666
9987.5575
10100.11
Sheet2
 
Upvote 0
Thanks VBA Geek, but I need this to be as a VBA function since I'm using it in some other functions and not partically in a range of cells..
 
Upvote 0
Code:
Function MaxDecimals(ParamArray Prices() As Variant) As Long
Dim cntr As Long, tempMax As Long, lDecimals As Long
For cntr = LBound(Prices) To UBound(Prices)
    lDecimals = Len(Mid$(Prices(cntr), InStr(1, Prices(cntr) & ".", ".") + 1))
    If lDecimals > tempMax Then tempMax = lDecimals
Next cntr
MaxDecimals = tempMax
End Function
 
Upvote 0
sweet, much appreciated!

by the way, why did you choose to use the array as Variant ? isn't that a waste of memory and might be problematic (in the meaning of miss interpertation of VBA to recognize the right type of variable) ?
 
Upvote 0
your can only user ParamArray as a variant

you were using variants as well in your function arguments:

Code:
[COLOR=#333333]Function DecimalPlaces(ByVal bp1!, ByVal bp2!, ByVal bp3!, ByVal ap1!, ByVal ap2!, ByVal ap3!) As Byte[/COLOR]


sweet, much appreciated!

by the way, why did you choose to use the array as Variant ? isn't that a waste of memory and might be problematic (in the meaning of miss interpertation of VBA to recognize the right type of variable) ?
 
Upvote 0
as far as I know ! refers to single, not as variant, correct me if I'm wrong..
 
Upvote 0
By the way,
I would be greatful if you could tell me what is wrong with my original code as I need to learn from my mistake, if you've got a spare moment please.

thanks :)
 
Upvote 0
you are omitting the "As Single" in your function arguments, so you're using variants as well since you do not specify any data type.

what i see wrong in your function is that it is not dynamic, meaning that you have to code in all the arguments of the function, whereas in mine you can pass as many as you like, second it won't catch numbers with more than 2 decimals the way it is constructed
 
Upvote 0
you are omitting the "As Single" in your function arguments, so you're using variants as well since you do not specify any data type.

That is incorrect. If you print TypeName(bp1) in dikken20's original implementation, you will see it is type Single.

That is because dikken20 wrote bp1! in the original implementation. Note the exclamation point; it causes the variable to be type Single.

(I prefer to write As Single explicitly.)
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top