Rounding Formulae

[Shadowboxer]

I need to create some automated rounding rules as follows:

One

Note before - all cells will show numbers to one decimal place only.

Baseline figures in cell D2 need to be rounded and displayed in E2 according to the following rules:

Decimals .1 + .2 to be rounded down to nearest whole number, except when rounding down to zero, when decimals should instead be rounded up to 0.5.

Decimals of .3 + .4 to be rounded up to the nearest .5

Decimals of .6 + .7 to be rounded down to the nearest .5

Decimals of .8 + .9 to be rounded up to the nearest whole number.


Two

Note before - as previously all cells will show data to one decimal place - but this time the rounding rules are different:

Decimals prior to rounding to appear in D2. Cell E2 is required to restate the content of cell D2 having applied the following rounding rules:

Decimals of .1 to be rounded down to the nearest whole number, with the exception of anything rounded to zero, which should be rounded up instead to 0.5.

Decimals of .2 + .3 + .4 to be rounded up to the next .5

Decimals of .6 to be rounded down to the nearest .5

Decimals of .7 + .8 + .9 to be rounded to the nearest whole number.


All help with this gratefully received.

 

[Andrew Fergus]

Hi

Here is a formula for the first method :

=MAX(0.5,ROUND(D2-0.3,0)+0.5*(ABS(MOD(D2,1)-0.5)<=0.2))

and a slightly different formula for the second :

=MAX(0.5,INT(D2)+IF(MOD(D2,1)>=0.7,1,IF(MOD(D2,1)>=0.2,0.5)))

HTH, Andrew

 

[barry houdini]

I'm assuming you only have positive values.....

for one

=ROUND(MAX(D2,0.3)*2,0)/2

and for two

=ROUND(MAX(D2+0.1,0.3)*2,0)/2

 

[Shadowboxer]

Thanks for your help folks. Something remaining for me to achieve that didn't cross my mind when I was planning this ... for both formulae I need nil or blank fields to be interpreted as zero, as opposed to 0.5.

The help received so far has worked beautifully thanks folks.

 

[barry houdini]

You can amend my suggestions as follows:

=(D2<>"")*ROUND(MAX(D2,0.3)*2,0)/2

and

=(D2<>"")*ROUND(MAX(D2+0.1,0.3)*2,0)/2

 

[Shadowboxer]

A little confused as zero/blank fields are still being rounded to 0.5 (he says hoping he's not done anything really dumb)

 

[barry houdini]

Sorry, to cope with both blank and zero

=(D2<>0)*ROUND(MAX(D2,0.3)*2,0)/2

and

=(D2<>0)*ROUND(MAX(D2+0.1,0.3)*2,0)/2

 

[Shadowboxer]

Thanks for your help, really appreciated.