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.
use this formula.
=Round(If(ToNumber(Right(FormatNumber([Number];"#.#");1))>5) Then [Number] Else [Number]+1;0)
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
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.
thankswhich 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
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)