Round to the nearest Nickle (.80 or .85 or .90) 1

[wooddsw]

I'm trying to develop a function that will calculate a percentage increase on a cost price but need it to be to the nearest nickle. For example, 9.95 * 1.40 = 13.93. I want to be able to store that value as 13.95. So basically, I'm looking to divide the result up this way:

Pennies .00 to .02 should round to the lower .00 result
ie. 13.92 would be stored as 13.90

Pennies .03 to .07 should be rounded to .05 result
ie 13.93 would be stored as 13.95
ie 13.97 would be stored as 13.95

Pennies .08 and .09 should be rounded up to the next .00 result
ie. 13.98 would be stored as 14.00

I've attempted used conditional logic to first test the calculation (9.95 * 1.40) incorporating the RIGHT function to evaluation the units position of the pennies. Then based on the result I was trying to use flavors of the CEILING and FLOOR functions to get the rounding correct.
The function looked something on this order but wouldn't give the desired result:


=IF(AND(RIGHT(C2,1)>=3,RIGHT(C2,1)<=7),"Use the 05 mid value",IF(RIGHT(C2,1)<3,FLOOR(C2,0.05)"Lower 00",CEILING(C2,0.05)+0.05)"Higher 00")

This is my first exposure to CEILING/FLOOR. Anyone that can provide a suggest on my function or even suggest a completely new approach would be appreciate.

 

[macropod]

Hi wooddsw,

Try:
=ROUND(C1*20,0)/20

Cheers

 

[Davidprince]

Unfortunately =Round(c1*20,0)/20 doesn't work ( try .93 ).
However, it's on the right track. You need to check against the midpoint ie if you are rounding to .05 (5 cents) then check for .025. I set out my solution although its rough:
=If(mod(a1,.05)<.025,a1-mod(a1,.05),if(.025<mod(a1,.05)<.05,a1+.05-mod(a1,.05),if(.05<1-int(a1)<.075,a1-mod(a1,.05),if(.075<1-int(a1),a1+.05-mod(a1,.05),"Error"))))

I think I put in all the brackets.
regards
David

 

[macropod]

Hi David,

Works for me:

ColC Returns
13.90 13.90
13.91 13.90
13.92 13.90
13.93 13.95
13.94 13.95
13.95 13.95
13.96 13.95
13.97 13.95
13.98 14.00
13.99 14.00
14.00 14.00

Specified requirements were:

Pennies .00 to .02 should round to the lower .00 result
ie. 13.92 would be stored as 13.90

Pennies .03 to .07 should be rounded to .05 result
ie 13.93 would be stored as 13.95
ie 13.97 would be stored as 13.95

Pennies .08 and .09 should be rounded up to the next .00 result
ie. 13.98 would be stored as 14.00


Cheers

 

[wooddsw]

Worked like a champ Macropod....Thanks