round to one of two numbers

[Kirk]

I would like to be able to round the cents portion of a retail price to
either .95 or .45 whatever is the closest and the rounding can either
go up or down. For instance:

Current Price Modified Price
$7.05 $6.95
$7.99 $7.95
$7.50 $7.45
$7.19 $6.95
$7.75 $7.95

Is this possible? Any help is much appreciated.

Kirk

 

[CLR]

=MROUND(A1,0.5)-0.05

Vaya con Dios,
Chuck, CABGx3

 

[JE McGimpsey]

Or, if you don't want to use the Analysis Toolpak Add-in:

=ROUND(A1*2,0)/2-0.05

 

[Kirk]

I'm not getting the desired result if the number is 10.71 using either
of the 2 formula's. I would expect the result to be 10.95 not 10.75
some goes it the number is 10.21 I get 9.95 not 10.45. Thanks

 

[CLR]

As far as I can see, whether you use my MROUND, or JE's formula or CEILING,
or FLOOR, there will always be some specific values that will swing the wrong
direction. The only alternative I can see is to use a large (.0-.99) VLOOKUP
table on the decimals.

Vaya con Dios,
Chuck, CABGx3

 

[JE McGimpsey]

Quite right. Try:

=ROUND((A1+0.05)*2,0)/2-0.05