Duplicating irregular rounding

[Whang56]

I have use Excel to duplicate an irregular rounding issue that was programmed into our company's computer system years ago.

What this thing does is round to the nearest nickel, but it doesn't break exactly at 2.5 like normal. This thing rounds down to zero at .449 - so at 4 &1/2 cents, it rounds up, and anything less it rounds down. Then between 5 and 10 cents, it rounds up at 5.5 cents.

So basically, in a scale between zero and 10 cents, everything below 4.5 rounds down, everthing above 5.5 rounds up. Only the numbers between 4.5 and 5.5 round to 5.

I can duplicate some of this using Excel's floor and ceiling functions. The formulas:

Please Login or Register  to view this content.

will each either round up or down, but I need a formula that will round down anything under 4.5 cents to the nickel below and round up anything over 5.5 cents to the nickel above.

I hope that I made this understandable. Many thanks in advance for your help.

[shg]

Please take a few minutes to read the forum rules about cross-posting.