Rounding up to 0.005
Posted by Chris Jones on August 23, 2001 8:44 AM
Hey everyone, I need some help big time.
I want a cell to take a value of another cell and round it up to 0.005.
e.g. 1.100 becomes 1.105
e.g. 2.115 becomes 2.120
It must always round up.
I've tried the roundup function
e.g. =ROUNDUP(AA19,.005)
but it does not work.
Does anyone have any ideas, it would be a great help.
Posted by Mark W. on August 23, 2001 8:51 AM
This doesn't make any sense to me. What do you
mean by "It must always round up"? Do you just
want to add .005 to any number?
Posted by Chris Jones on August 23, 2001 8:57 AM
No, I can't just add .005. I'll try to make this clearer. Any number can either be .005, or .000.
e.g. (.003 becomes .005, it does not become .008)
e.g. (.006 becomes .010, it does not become .011)
The last digit must be a 5 or 0.
Does this make more sense?
Posted by Mark W. on August 23, 2001 9:15 AM
Okay, but look at your examples...
e.g. 1.100 becomes 1.105
e.g. 2.115 becomes 2.120
Why does 1.100 become 1.105? -- it's last digit (1/1000) is 0!
Why does 2.115 become 2.120? -- it's last digit (1/1000) is 5!
Posted by Mark W. on August 23, 2001 9:19 AM
=CEILING(0.003,0.005) produces 0.005
=CEILING(0.006,0.005) produces 0.010 No, I can't just add .005. I'll try to make this clearer. Any number can either be .005, or .000.
Posted by IML on August 23, 2001 11:06 AM
Just because I'm bored, you would get the expected results of your examples by using:
=IF(CEILING(A1,0.005)=A1,A1+0.005,CEILING(A1,0.005))
If negatives are involved, try
=IF(CEILING(A1,0.005*A1/ABS(A1))=A1,A1+0.005*A1/ABS(A1),CEILING(A1,0.005*A1/ABS(A1)))
