which round function to use

[lezawang]

Hi
Which round I should use in this case:

• Amounts ending in 1 cent and 2 cents are rounded down to the nearest 10 cents;
• Amounts ending in 3 cents and 4 cents are rounded up to the nearest 5 cents;
• Amounts ending in 6 cents and 7 cents are rounded down to the nearest 5 cents;
• Amounts ending in 8 cents and 9 cents are rounded up to the nearest 10 cents;
• Amounts ending in 0 cent and 5 cents remain unchanged.

https://www.canada.ca/en/revenue-agency/programs/about-canada-revenue-agency-cra/phasing-penny/_jcr_content/par/img_0_26_1/image.img.jpg/1494473009602.jpg

Thank you very much.

 

[Rick Rothstein]

I think this will do what you want...

=MROUND(A1,0.05)

 

[lezawang]

Thank you so much. It works perfectly. My question if you do not mind, how you came with this idea? the 0.05 i mean. Thanks again

 

[Rick Rothstein]

It is instructs the function to round to the nearest multiple of 0.05 (which is the 5 cent increment you wanted to round to).