Seeking help in personalising the process of rounding off an amount in excel

[baidya91]

I would like to have a formula to round off an amount in a personalised way. To illustrate, if an amount comes to 830.70, then the amount will be rounded off to 830.00, but if the amount is 831.40 then it will be rounded off to 840.00. Which excel formula shall I apply to get desired result?

 

[Micron]

I think you are out of luck as far as a 'one fits all' formula. Several rounding functions are listed here https://support.microsoft.com/en-us/kb/196652
but none seem to provide the degree of rounding you're asking for. I'd say you need a function that takes your value and converts it to an integer (830). If the last digit is zero, it's done. If not, find the difference between 10 and the last digit (831: 10-1=9) and add 9 to the integer.

 

[baidya91]

Then how shall I work out the issue? Is there no way at all to get the desired result? Any combination of formula?

 

[Micron]

Then how shall I work out the issue?

I know of no formula or expression, or combination of, that will do what you want because you're not really rounding. That's not to say that there isn't one. Others may be able to create one for you. But I think you did not understand my reply as I gave you an answer - a function (vba) that does what I described, which was based on the limited information given.

 

[MarcelBeug]

Your personal rules are not clear.
You might even mean: =IF(A1<831,830,840)
I suspect you want to round to a multiple of 10, <1 down and >= 1 up.
That would be =ROUND(A1+4,-1)

 

[baidya91]

I want to use excel in place of VBA code. Besides, I don't mean only two figures. In fact, there are many more such figures. They are just examples. I think you have got it.

 

[MarcelBeug]

...I think you have got it.

That means I gave the right suggestion?

 

[Micron]

Thanks MarcelBeug; I learned something. What I find interesting is that ROUND(A1+3,-1), which makes 831.7 into 834.7, doesn't 'round' to 835, which doesn't round to 840.
Maybe I'd better keep my nose in the Access part of the forum where I know a thing or two...

 

[baidya91]

I think the formula should be =ROUNDUP(TRUNC(A1,0),-1). It calculates what I want. After a great effort, I have been able to do this. By the by, Micron is absolutely right in his observation. The formula given by MarcelBeug is not foolproof. Thank you both for the effort you have made so far.

 

[MarcelBeug]

The formula given by MarcelBeug is not foolproof.

Excuse me???
The formula is absolutely foolproof.
It may not do what you want, but it was never properly explained what the formula should do.