Round a monetary value to nearest even .2 amount

[jack109]

Hi

I've tried playing with ROUNDUP and EVEN to try and round a monetary value to nearest even .2 amount but no avail.

For example

£5.23 would become £5.24
£1.09 would become £1.10
£6.46 would remain as £6.46

Is there a solution to this?

Thanks in advance

 

[SanjayGMusafir]

Trying to think of a better solution. Till then try this -

Book1
	B	C
3	5.23	5.24
4	1.09	1.10
5	6.46	6.46
Sheet1

Cell Formulas
Range	Formula
C3:C5	C3	=IF(ISODD(B3*100),B3+0.01,B3)

 

[jack109]

thank you

 

[SanjayGMusafir]

Here is another thread on this forum itself -

Round to the nearest even decimal point

Hi, I am looking to round a value to the nearest even decimal point.....the obvious combination of ROUND() and EVEN() makes it the nearest even integer, where as I need it to 2 decimal places e.g. 291.97 --> 291.98 Any simple way to do this? Thanks Lewis

www.mrexcel.com

 

[SanjayGMusafir]

Based on the forum I quoted above - CEILING seems the short & workable formula

Actually I was trying to remember it but couldn't recall initially.

Book1
	B	C	D	E
3	5.23	5.24	5.24	5.24
4	1.09	1.10	1.10	1.10
5	6.46	6.46	6.46	6.46
Sheet1

Cell Formulas
Range	Formula
C3:C5	C3	=IF(ISODD(B3*100),B3+0.01,B3)
D3:D5	D3	=FLOOR(B3+0.01, 0.02)
E3:E5	E3	=CEILING(B3,0.02)

 

[jack109]

thank you very much

 

[SanjayGMusafir]

Glad to help you