Calculate and round retail priceing

[Guest]

I have an Excel spread sheet with my cost on it, and I would like to mark
this up and round the price to the next higher $xx9.99. The retail values in
the price list range from $10 to over $1000.

The basic formula that I use to calculate retail price is /.6. For example
if the product costs $160, I would calculate that to $266.66 with the basic
formula.

I would like Excel to kick out a price of $269.99. I have tried several
different ways to get this output. The best way I have found to get close is
doing a custom cell format of
_($* #,##0.99_);_($* (#,##0.99);_($* "-"??_);_(@_)
which will round the price to $266.99.

What else can I try to get the desired output?

 

[Peo Sjoblom]

=CEILING(160/0.6,10)-0.01

or

=CEILING(A1/0.6,10)-0.01

where you put the amount in A1


Regards,

Peo Sjoblom

 

[Trevor Shuttleworth]

Jonathan

maybe something like:

=ROUND(A2*(1+B2),-2)-0.01

where A2 has the original value and B2 has the "factor"

160 to 210 would round up to 299.99 assuming B2 contains 0.6
220 to 280 would round up to 399.99 assuming B2 contains 0.6
290 to 340 would round up to 299.99 assuming B2 contains 0.6

and so on. Is that what you're looking for ?

Regards

Trevor