Formula for rounding up to nearest 0.09 pence

Hi there

I am running currency conversions for a new international retail store and want to make the pricing look nicer.

I want to take the converted figure, eg £3.84 to be rounded UP to £3.89, or £22.36 to go to £22.39 etc etc.

I can only seem to find solutions to get everything to end in X.99, which could be too much of a jump for some low-value items.

Can anyone offer any help on this one?

Thanks in advance!

One way would be to round up to 1 decimal place then subtract 0.01.
So if your value was in A1 you'd use =ROUNDUP(A1,1)-0.01

You can replace A1 in that formula with the calculation you use to get your original price.

BSB

Sorry, spoke to soon. Doesn't work in all instances. GRRR!
I shall have a think on it.

BSB

Maybe this?

=IF(ROUNDUP(A1,1)-0.01<A1,A1+0.09,ROUNDUP(A1,1)-0.01)

BSB

Another way:

Formula:

Please Login or Register  to view this content.

FLOOR! That's the one I was trying to remember but my weary old brain wouldn't let me!

Thanks for the reminder

BSB

Originally Posted by BadlySpelledBuoy

Thanks for the reminder

Thanks for the rep