Floor and Rounding Function Question  [SOLVED]

Questions about MultiCharts and user contributed studies.
trader101
Posts: 15
Joined: 30 Nov 2012
Has thanked: 8 times

Floor and Rounding Function Question

Postby trader101 » 17 Jan 2013

Hello,

I am trying to understand if its possible to use the FLOOR or CEILING functions to round numbers to a specific multiple of significance ? Like in Microsoft Excel...

Rounds number down, toward zero, to the nearest multiple of significance.

Syntax

FLOOR(number,significance)

Number is the numeric value you want to round.

Significance is the multiple to which you want to round.

Remarks

If either argument is non numeric, FLOOR returns the #VALUE! error value.
If number and significance have different signs, FLOOR returns the #NUM! error value.
Regardless of the sign of number, a value is rounded down when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.

Example :

Formula Description (Result)
=FLOOR(2.5, 1) Rounds 2.5 down to nearest multiple of 1 (2)
=FLOOR(-2.5, -2) Rounds -2.5 down to nearest multiple of -2 (-2)
=FLOOR(-2.5, 2) Returns an error, because -2.5 and 2 have different signs (#NUM!)
=FLOOR(1.5, 0.1) Rounds 1.5 down to the nearest multiple of 0.1 (1.5)
=FLOOR(0.234, 0.01) Rounds 0.234 down to the nearest multiple of 0.01 (0.23)

source : http://office.microsoft.com/en-ca/excel ... 09094.aspx


Can this be achieved or I will have to code a formula myself ?

Thank you.

User avatar
TJ
Posts: 6583
Joined: 29 Aug 2006
Location: Global Citizen
Has thanked: 970 times
Been thanked: 1907 times

Re: Floor and Rounding Function Question

Postby TJ » 17 Jan 2013

trader101 wrote:Hello,

I am trying to understand if its possible to use the FLOOR or CEILING functions to round numbers to a specific multiple of significance ? Like in Microsoft Excel...

Rounds number down, toward zero, to the nearest multiple of significance.

Syntax

FLOOR(number,significance)

Number is the numeric value you want to round.

Significance is the multiple to which you want to round.

Remarks

If either argument is non numeric, FLOOR returns the #VALUE! error value.
If number and significance have different signs, FLOOR returns the #NUM! error value.
Regardless of the sign of number, a value is rounded down when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.

Example :

Formula Description (Result)
=FLOOR(2.5, 1) Rounds 2.5 down to nearest multiple of 1 (2)
=FLOOR(-2.5, -2) Rounds -2.5 down to nearest multiple of -2 (-2)
=FLOOR(-2.5, 2) Returns an error, because -2.5 and 2 have different signs (#NUM!)
=FLOOR(1.5, 0.1) Rounds 1.5 down to the nearest multiple of 0.1 (1.5)
=FLOOR(0.234, 0.01) Rounds 0.234 down to the nearest multiple of 0.01 (0.23)

source : http://office.microsoft.com/en-ca/excel ... 09094.aspx


Can this be achieved or I will have to code a formula myself ?

Thank you.


Have you tried the Wiki?
Press [F1] at MultiCharts, or
https://www.multicharts.com/trading-soft ... /Main_Page

trader101
Posts: 15
Joined: 30 Nov 2012
Has thanked: 8 times

Re: Floor and Rounding Function Question

Postby trader101 » 17 Jan 2013

Yes, I did thanks for the suggestion.

None of the Math functions will work for what I want to do...

I looked at :

FLOOR, CEILING, ROUND...

Maybe there is something I don't see or understand...do you have any idea ?

Thanks.

escamillo
Posts: 197
Joined: 25 Mar 2011
Has thanked: 22 times
Been thanked: 55 times

Re: Floor and Rounding Function Question

Postby escamillo » 17 Jan 2013

Within the PowerLanguage Editor there is a key word reference that you can reference from "Navigator". In it about Floor it says:

Floor
Returns the greatest integer less than or equal to the specified numerical expression.
Usage
Floor(Value)
Where: Value - a numerical expression
Example
Floor(9.1) will return a value of 9
Floor(-2.85) will return a value of -3

Otherwise, TJ recently posted some useful math on Rounding and rounding to the number of decimals that you want.

evdl
Posts: 399
Joined: 19 Jan 2011
Location: Netherlands
Has thanked: 84 times
Been thanked: 123 times

Re: Floor and Rounding Function Question

Postby evdl » 17 Jan 2013


trader101
Posts: 15
Joined: 30 Nov 2012
Has thanked: 8 times

Re: Floor and Rounding Function Question  [SOLVED]

Postby trader101 » 18 Jan 2013

Thanks for all your inputs, I think I found the solution. I wanted to implement a Quantization function on some ES calculations.

http://en.wikipedia.org/wiki/Quantizati ... cessing%29

Basically when I got the result of a calculation for the ES I wanted to either floor or Ceil the value to the next price increment (0.25).

For example : Price calculated : 1000.49 Floor it to 1000.25 or Ceil it to 1000.50 depending of some other criteria...

Here is the solution I decided to use and I am sharing it with the community as a thank you for all your help...

Price is the output of some other calculation and significance is the price increment of the ES (0.25)

This would be the Floor function :

Price2 = Price / significance

Price3 = IntPortion(Price2)

Price4 = Price3 * significance

For example :

Price = 1000.49

Price2 = 1000.49 / 0.25 = 4001.96

Price3 = IntPortion(4001.96) = 4001

Price4 = 4001 * 0.25 = 1000.25

This would be the Ceiling function :

Price2 = Price / significance

Price3 = IntPortion(Price2)

Price4 = (Price3 * significance) + significance

For example :

Price = 1000.49

Price2 = 1000.49 / 0.25 = 4001.96

Price3 = IntPortion(4001.96) = 4001

Price4 = (4001 * 0.25) +0.25 = 1000.50

significance could be replaced by the MinMove / PriceScale to make this more universal and usable on other assets...

I hope this help.

Please feel free to critique my code to make it better...thanks.


Return to “MultiCharts”