Skip to Content
0

Round Up 4.1 to 5

Feb 09 at 09:09 PM

76

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

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
10 |10000 characters needed characters left characters exceeded

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

0

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)

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

0

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)

0

it not working correct.

thanks
capture1.jpg (130.9 kB)
0

which value is incorrect? please higlight

0

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

0
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
10 |10000 characters needed characters left characters exceeded