Bespoke Rounding

**Nick_B123** · 04-26-2010, 05:59 AM

Hi guys ,

I'm not a fan of one time posting , but this looks a particularly effective forum!

i am trying to figure out on excel whether it is possible to round numbers up or down in a more bespoke way for example i would like it to read:-

Total sale price
£8.55 £8.55
£7.31 £7.35
£2.74 £2.75

however i normally get with most rounding functions:-

Total sale price
£8.55 £8.55
£7.31 £7.30 (this one always rounds down)
£2.74 £2.75

so basically i would like a rounding function that would round up anything to a five that is greater than 0 but not round up anything greater than 0.5 - i.e. wouldn't change the £8.55 to £9.00.

I hope this inst to much of a waffle , but would much appreciate some help ,

Nick

**DonkeyOte** · 04-26-2010, 06:01 AM

See CEILING, eg:

B1: =CEILING(A2,0.05)
where A2 holds original value

note however that were A2 = 8.57 then B2 would round to 8.60 - not clear if this is desired or not.

**Nick_B123** · 04-26-2010, 06:03 AM

Thanks ! I think that would work , will have a go now !

its a looong spreadsheet and want to make as easy as possible to avoid repetitive formula writing and to make it as trouble free for those using the spreadsheet in future.

**Nick_B123** · 04-26-2010, 06:06 AM

Ok , thats pretty good but anything that has a total price of anything ending in a .5 will round up i.e. my 8.55 goes to 8.60

its maybe something i cant do in excel

Cheers !
Nick

**DonkeyOte** · 04-26-2010, 06:08 AM

An exact value of 8.55 would not round to 8.60.

Presumably you have additional significance which is not visible by virtue of formatting (ie alter cell format to 10 decimals and see what you have in the original value column)

I would suggest then you ROUND also, eg:

=CEILING(ROUND(A2,2),0.05)

**Nick_B123** · 04-26-2010, 06:11 AM

Okay , That is the business!

thank you very much for your help - VERY much appreciated ! and certainly the promptness of the reply too!

hopefully I'll be able to participate on the forum too.

cheers

N