Rounding in calculated cells

[Former Member]

Hello,

in one of my queries I calculate a variance between two key figures.

In this querie i need integers only. Therefore I set the "number of decimal places" to 0.

I defined exceptions to color the cell in green if the value is bigger or equal to 0 and to color it red in case it is less than 0.

The problem is that, even though not on display the exceptions react to the decimal places, e.g. -0.35 is colored red (shoud be green) and displayed as "-0".

I can partly fix it by changing the exception to react on "greater or equal to 0.5" and "less than -0.5). This of cours doesn't make the "-0" go away and all in all does not feel like the "real deal" solution.

I'd prefer to round the variance values in the calculation using the "round half up" method. Is there a simple way to do that? e.g.

round(<calculation of variance>)

Thanks in advance,

Ben

Edited by: Benjamin Linder on Apr 14, 2010 3:40 PM

Edited by: Benjamin Linder on Apr 14, 2010 3:41 PM

[Former Member]

Answered! Thanks again!

[former_member189638]

You can use the CEIL and FLOOR Functions for this

Suppose you have a Key Figure K.F . hide this key figure and create a new formula Key Figure and write a formula as shown below.

( FRAC ( 'K.F' ) >= 0.5 ) * CEIL ( 'K.F' ) + ( FRAC ( 'K.F' ) < 0.5 ) * FLOOR ('K.F' )

CEIL, FLOOR and FRAC will be available under Mathematical Functions of Formula Key Figure

[Former Member]

Hi,

Thanks a lot! That pretty much solved my problem.

I made some minor adjustments as in this case I'm dealing with negative integers also.

I'm now using:

SIGN( 'K.F.' ) * (( ABS( FRAC( 'K.F.' )) >= 0.5 ) * CEIL( ABS( 'K.F.' )) + (ABS ( FRAC( 'K.F.' )) < 0.5 * FLOOR( ABS( 'K.F.' )))

Best Regards,

Ben