[Closed] Help. .xlsx gurus. Round up to nearest £0.05?

Round *up* or round to nearest?

Floor (a1+0.04,0.05) or similar

EDITED

=(C5*100) + (5 - MOD(C5*100, 5)) / 100

Works except £0.20 gives £0.25...

=round( value * 100 / 5,0) /100 * 5

or simplified

=round( value * 20,0) / 20

edit: soz, doesn't always round up, didn't read that 🙂

CEILING (again in Libre Office)

= CEILING (C5, 0.05)

Couldn't resist a crack myself though.

Allthepies, you can simply yours further with MROUND

=MROUND([b]value[/b], 0.05) is to the nearest multiple of 0.05

=if(MROUND(value,0.05)<value,MROUND(value+0.05,0.05),MROUND(value,0.05))

Maybe?? Quite tired....

=INT(A1)+(0.05*IF(((A1-INT(A1))*20)-INT(((A1-INT(A1))*20))>0,INT(((A1-INT(A1))*20))+1,INT(((A1-INT(A1))*20))))

It's messy, and I may have gone OTT on the parentheses, but it gets the job done.

[b]CEILING FUNCTION[/b]

https://support.office.com/en-us/article/CEILING-function-0a5cd7c8-0720-4f0a-bd2c-c943e510899f

Does what you need, no maybes or complexity.

I'd assumed CEILING was Libre Office only. It's very unlike MS to have the exact function one requires 🙂

If the spreadsheet is for someone else though I'd go with The Flying Ox, a maintenance job for life there

=INT(A1/0.05+0.999)*0.05

I [s]use[/s] used to use =ROUNDUP(C2*2,1)/2 but I'm liking dmorts' and molgrips' solutions.

=INT(A1/0.05+0.999)*0.05

Surely this is the simple, no obscure function, use anywhere answer?

The int function from tillydog works but you need another decimal space in the 0.05 so it's 0.005 for both.

My initial option was to do an if using the round and roundup functions. If they are the same answer you keep, if different you use round then add 0.005. Not as elegant as tillydog's though but simpler than TFOs.

=if(MROUND(value,0.05)<value,MROUND(value+0.05,0.05),MROUND(value,0.05))

and

=CEILING(value,0.05)

Give the same results for the test cases I've just tried.... I knew it would 😀

The int function from tillydog works but you need another decimal space in the 0.05 so it's 0.005 for both.

Only if you want to round to £0.005, not £0.05 😉

CEILING is nice and neat but won't work if someone is still using an old version of Excel.

So I'd probably go for ROUNDUP(C2*20,1)/20 to be safe.

(replace 20 with 1/x if you want to round up to £x instead of 5p)

CEILING is nice and neat but won't work if someone is still using an old version of Excel.

Help. [b].xlsx[/b] gurus.

Seeing as he's asking this question, he's probably not aware of the difference, or the lack of backwards compatibility.

Well, you certainly learn many new things every day.

Thanks to those who directly contributed to my question.