0

# Round Up 4.1 to 5

Feb 09 at 09:09 PM

76

I need to round up all my sales:

example

4.2 need to see 5

1.1 need to see 2

3.8 need to see 4

2.3 need to see 3

I tried with the Round (variable, 0) but eh formula don't do it.

AMIT KUMAR
Feb 15 at 12:28 PM
0

use this formula.

=Round(If(ToNumber(Right(FormatNumber([Number];"#.#");1))>5) Then [Number] Else [Number]+1;0)

Show 7 Share

The formula doesn't work. it giving me the normal round 5.1= 5 , 207.66=207 and also it's the number doesn't have decimal is summing 1.

5=6; 4=5

Thanks

AILI CUTIE

why you want to display 5 as 6? is it not wrong?

generally round() function will do the round to nearest integer.

if this is really required then you need to do more if-else condition to handle all these type of conditions.

try this,

=Round(If(ToNumber(Right(FormatNumber([Number];"0.0");1))>5) Then [Number] Else [Number]+1;0)

The formula almost works, but for some reason, when the number doesn't have decimal is summing 1.

Example:

5=6;

4=5.

I don't want that, I want to maintain the number. if the number is 5, it has to be 5, not 6, but the formula is rounding to 6.

the formula works perfect with numbers with decimals, but I have numbers with no decimals.

Thanks

Try this.

=Round(If(Tonumber(Right(FormatNumber([Number];"0.0");1))>5) or (toNumber(Right(FormatNumber([Number];"0.0");1))=0)) Then [Number] Else [Number]+1;0)

it not working correct.

thanks
capture1.jpg (130.9 kB)
AILI CUTIE

which value is incorrect? please higlight

the column without name has your formula

line 6, column Goal says 58.5, your formula put 60

line 8 column Goal =34.5, your formula put 36

AMIT KUMAR
Feb 23 at 10:41 AM
0

use this formula.i have changed the condition where +1 will be added if decimal part is above 4. earleir it was 5.

=Round(If(Tonumber(Right(FormatNumber([Number];"0.0");1))>4) or (toNumber(Right(FormatNumber([Number];"0.0");1))=0)) Then [Number] Else [Number]+1;0)

Share