Hi,

Our Company has a load of Filling Stations they supply, and they wanted to use an auto roundup or down in a spreadsheet to change daily fluctuations in pricing to the sites.

ie if todays price was 98.25 it should round down to 98.00 if it was 98.67 it would round up to 98.90

Anyone any idea how to work that out? I've been reading about Round Max and Ceiling but they dont round after the decimal point or from what my limited knowledge in coding can fathom out.

Any ideas anyone?

Thanks

You need to add 0 as the second argument:

ROUND(68.25,0)=68
ROUND(68.67,0)=69

What he means is to add '1' as the second argument (i.e. the number of digits after the decimal point that you want to keep)

EDIT: Actually, your first post makes no sense. How can you round from 0.67 to 0.9???

My life is hard, I suffer lots

So we're working with .9p.

Surely the middle value should be at .4p?

94.9<95.4<95.9

If <95.4 then it'll be 94.9
If ≥95.4 then it'll be 95.9

That looks about right.

Can I embarass myself and ask for a sample excel sheet ?!?

I cant obviously work with the values above but need sheet values to work it out in my head.

I know I know but im in no way a programmer and dont understand some of these functions!

Many thanks Ant

I'd help if I could, but I'm fairly shit with Excel.

I know what you want, but I don't exactly know how to implement it. But since we've cleared up what you're looking for then maybe someone else will be able guide you!

Unless I'm misunderstanding things:

=IF(MOD(x,FLOOR(x,1))>=0.51,FLOOR(x,1)+0.9,FLOOR(x,1)-0.1)

I'm no good at writing Excel macros, but you can put that directly in the cell you want to update and change x to the cell number, i.e. A1.

Thanks all, especially Ant and Matt. I'll give them a go tomorrow and see how I get on.

I'll let you know, thanks again though.

Matt your a feckin genius!

It works perfect, thank you very much. I would honestly never in a million years worked that out, I've honestly been reading shitloads about Roundup/Ceilings/Floors/etc etc.

Didn't have a clue what to do next.

Thanks a lot, really appreciated.

PS. Attached a test Spreadsheet to show the calculation for the future, if anyone is needing it (unlikely I know)