Round up to the nearest 0.5 percentage value

[quentin5000]

Hi all

I am having a bit of difficulty with a calculation.

Basically I need a rounding formula which will round up to the nearest 0.5% value.

I know this does not make a lot of sense so the example below will help demonstrate my intention.

If I have a percentage of 99.4%, this should roundup to 99.5%. If I have a value of 99.1% this should also round up to 99.5%. If I have a value of 98.8%, this should also round up to 99.5. However if I have a value of 98.4% then this should round to 98.5% and so on.

I have tried using MROUND and CEILING to no avail.

I have also attached an example spreadsheet with my desired results.

Any help would be much appreciated!

[XOR LX]

Hi,

=ROUNDUP(A2-0.005,2)+0.005

Regards

[bebo021999]

mMay be:

Please Login or Register  to view this content.

[quentin5000]

Hi

Apologies for the delay. I can confirm that both formulas work.

The only issue I had was if a percentage value was for instance 99.6% this was rounding to 100.05%. This however was an easy fix, I just simply used an IF statement requiring that any value above 99.5% equal a result of 100%.

Thanks again!

[bebo021999]

Nice to hear it works.

Besides your If, this is alternative:

=MIN(100%,CEILING(A2-0.5%,1%)+0.5%)