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.
Floor and Rounding Function Question [SOLVED]
- TJ
- Posts: 7740
- Joined: 29 Aug 2006
- Location: Global Citizen
- Has thanked: 1033 times
- Been thanked: 2221 times
Re: Floor and Rounding Function Question
Have you tried the Wiki?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.
Press [F1] at MultiCharts, or
https://www.multicharts.com/trading-sof ... /Main_Page
Re: Floor and Rounding Function Question
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.
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.
Re: Floor and Rounding Function Question
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.
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.
-
- Posts: 401
- Joined: 19 Jan 2011
- Location: Netherlands
- Has thanked: 85 times
- Been thanked: 124 times
Re: Floor and Rounding Function Question
Maybe this is what you are looking for:
http://www.multicharts.com/discussion/v ... est#p54523
post 7.
http://www.multicharts.com/discussion/v ... est#p54523
post 7.
Re: Floor and Rounding Function Question [SOLVED]
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.
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.