how do I split/grab num of the last two decimal places?

[QuantEdge]

hi, any idea how do I split/grab num from the right side of the string:

0.00022 = 22

0.00005 = 05

etc???

[YasserKhalil]

Try this

Please Login or Register  to view this content.

[Andy Pope]

You can use the RIGHT function

Please Login or Register  to view this content.

[MrShorty]

Are these text strings or numbers?

As others have noted, if they are text strings, then it could be as simple as the Right() function: https://msdn.microsoft.com/en-us/vba...right-function
If they are numbers, are those the exact values stored in the number, or are those rounded approximations to the actual value stored? I expect that the most reliable way to do this with numbers will be to use the Format() function (or similar number to string conversion function https://msdn.microsoft.com/en-us/vba...r-applications ) to convert the number to text with the correct number format, then use the Right() function. Assuming you want 5 digits after the decimal point =Right(Format(mynumber,"0.00000"),2)

[QuantEdge]

I have attached my sheet...

[MrShorty]

You posted this in the VBA forum, so I think we all assumed that you were looking for a VBA solution. Your sample file contains no VBA, so can we assume you want a regular formula solution?

It appears that these are numbers, and, due to floating point errors (see links discussing issue here: https://www.excelforum.com/groups/ma...nd-errors.html ), none of the values are exactly 5 digits long. In Excel, we can use the TEXT() function to convert a number to a text string in a specified number format, then nest that function inside of the RIGHT() function (similar to what I did using the Right(Format(...),2) function VBA function above). https://support.office.com/en-us/art...8-93d29371225c

[QuantEdge]

MrShorty I NOT looking for VBA solution.
I think the "format cell" option is effect the calculation? which one do I set it to?

[QuantEdge]

how do I use that TEXT() function? or convert to text?

[MrShorty]

Number formats should not have any effect on the underlying calculation, unless you have the "precision as displayed" option checked.

In addition to using text formulas, which result in text string instead of numbers, it might be better to do this as a numeric operation. 1) Multiply by 1E5 then 2) Round that result to the nearest integer. Something like =ROUND(B2*1E5,0) which will result in the number 1 instead of the text string "01".

It really depends on exactly what you are trying to do with this calculation and how this fits into your overall project. Personally I would be inclined towards a numeric operation rather than a text manipulation.

[QuantEdge]

thx MrShorty, that seems to work!!! as Im here could I ask another problem which needs solution?

[MrShorty]

You can ask. There could be some discussion among the moderators whether you should start a new thread or not. If it is similar to this same question, then proceed in this thread. If it is different, start a new thread with your next question.

[QuantEdge]

thx how can I Normalized this fx dataset (see sheet) to simple digits of say from 0-9
as so to be inputed into any excel functions (for algorithm trading system creation)?

PS I can't use the raw data feed numbers of 1.18253 it just to complex for my excel functions.
Is there so way to find the different and will represent the structure when graphed?

see new sheet:

[MrShorty]

thx how can I Normalized this fx dataset (see sheet) to simple digits of say from 0-9 as so to be inputed into any excel functions (for algorithm trading system creation)?

I don't do this kind of trading, so I'm not sure I understand the math behind your question. It might be preferable to ask someone who is more knowledgeable about these trading algorithms how they should work. Then, bring that information here and we can help you program that into Excel.

One "typical" normalization algorithm I might use involves the TREND() function. Column C should return a value between 0 and 99 (if it really is always 2 digits), so one might use something like =TREND({0,9},{0,99},C2), which will "normalize" the 0 to 99 values in C2 onto a 0-9 scale. I have no idea why that would be better, or how that fits in to the trading algorithm, but that is one strategy for building a "normalization" formula in Excel.