excel course - Can I sho

Hi Tom,

I've got a solution for you!

I hope you appreciate this, cause I've spent quite a lot of my own time on it, and normally this would be a consultancy job by us.

I had to write a VBA function for this to work. Once you've added the VBA function to your worksheet, the formula is then:
=addpip(B9-FLOOR(B9, 1), C9)+B9
This assumes column B is your original number and column C is the number of pips.

The file is attached at the bottom of this post. You'll need to ensure Macros are enabled (Tools -> Macros -> Security).

FYI, to write a new function in Excel you go Tools -> Macros -> Visual Basic Editor -> Insert -> Module. And type the code into the window.

Thanks for attending our Excel Advanced training course.

I really hope this helps you Tom. Let me know how you get on with it, I'd be interested to hear.

Regards, Rich

' pass in a number less than 1

' ie. should be 0.23895 or similar

' you will need to floor() the original number before passing it to this function

Function addpip(theOriginal As Single, pips As Integer) As Double



Dim theString As String

Dim lengthOfString As Integer

Dim thePosition As Integer

Dim numberOfZeros As Integer

Dim thousandsInt As Long

Dim finalOutput As Double



' convert the number to a string

theString = CStr(theOriginal)



' get length of that string

lengthOfString = Len(theString)



' find out at which position does the decimal point appear

' should always be 2 or 0, as the first two characters should be "0."

' or a number passed through without decimal places

thePosition = InStrRev(theString, ".")



' if the position is 0, then no decimal places were passed in

' so pass back just the number of pips

If thePosition = 0 Then



    addpip = pips



Else



    ' number of zeros required

    ' length of the whole number minus the position of the decimal point

    numberOfZeros = lengthOfString - thePosition

    

    ' start at 1, and times by 10 for however many zeros we require

    thousandsInt = 1

    For i = 1 To numberOfZeros

        thousandsInt = (thousandsInt * 10)

    Next i

    ' we'll now have a number to multiply by. eg. 10 or 10000 or 1

    

    ' the actual value we want to add to the original number is

    ' the number of pips divided by however many thousands

    finalOutput = pips / thousandsInt

    

    ' return the value

    addpip = finalOutput



End If



End Function