IF Formula Help

[Justinian]

In cell A2 is the number 78.13. I am trying to write a formula that rounds A2 down if the last digits is between 0 and 5 and round up if the last is between 6 and 9. This formulas does round down if the number is less than 5 but it will not round up when the number is 5 or greater.

=IF(AND(RIGHT(A2)<5,RIGHT(A2)>0),ROUNDDOWN(A2,0),ROUNDUP(A2,0))

 

[bbott]

The RIGHT function is treating A2 as text instead of a number. Try;

=IF(AND(INT(RIGHT(A2))<5,INT(RIGHT(A2)>0)),ROUNDDOWN(A2,0),ROUNDUP(A2,0))

 

[bbott]

I am trying to write a formula that rounds A2 down if the last digits is between 0 and 5 and round up if the last is between 6 and 9.

Is there a reason you wouldn't just use ROUND?

 

[JoeMo]

or this:
=IF(RIGHT(ROUND(A2,2),1)+0<=5,ROUNDDOWN(A2,0),ROUNDUP(A2,0))

 

[Justinian]

Is there a reason you wouldn't just use ROUND?

ROUND rounds 78.13 to 78 but it does not round 78.50 to 79.

 

[Justinian]

or this:
=IF(RIGHT(ROUND(A2,2),1)+0<=5,ROUNDDOWN(A2,0),ROUNDUP(A2,0))

I get a #VALUE error on this one.

 

[bbott]

ROUND rounds 78.13 to 78 but it does not round 78.50 to 79.

When I use this:

=ROUND(A2,0)

It rounds 78.50 to 79.

 

[JoeMo]

I get a #VALUE error on this one.

What is in cell A2 as viewed in the formula bar? Is the content of A2 a number or text?

 

[Justinian]

Number is 78.13.

 

[Justinian]

OK, I am an idiot. I must have input the formula incorrectly because this is now working for me:

=IF(RIGHT(A2)<49,ROUND(A2,2)*100,ROUND(B3,2)*100)