How to round off the last integer nearest to 5. say 12342 and 12348. Here the last one is 2 and 8.

Question

It is a question under MSExecl. Suppose 12or 18 or 16 or14. The 12 should be worked out to 10, 16 to 15, 14 to 15, 18 to 20

Answer 1

LOL youve answered your own question but that would only work if u were rounding to 5 =)

Answer 2

This is quite simple, with the MOD function. MOD stands for Modulo. The usage is

MOD(n,d).

This returns the reminder when number n is divided by number d.

For example, MOD(11,5)=1, MOD(23,7)=2.

Apply this principle to find out the reminder when your cell contents are divided by 5. If the reminder is 1 or 2, then you need to subtract the reminder from the original number. If it is 3 or 4, you may need to subtract the reminder from the original number, and then add 5, to round it up to the higher multiple of 5.

Use the IF statement to choose whether to round up, or down.

Here is the formula, for your immediate use:

IF(MOD(A2,5)<=2,

A2-MOD(A2,5),

A2-MOD(A2,5)+5)

This should be in a single line. I've split this into three lines for your understanding. Here, A2 is the cell site containing the number to be rounded up or down.

Try this, and let me know if this works.