Is there a formula that will do this

Say I have Cell A1 that has a figure of 37.8 & Cell A2 shows 11.1

I would like Cell C1 to change from the default 0 to show the figure from A1 rounded to the nearest whole number (i.e 38 in this case ) if that is nearer to a whole number then the figure in A2

So in this case the figure in A2 (11.1) is nearer to a whole number so I would like cell C2 to change from 0 to 11 and Cell C1 to stay at 0

I hope you understand what I mean

Cheers

Hi

Can you give more details. For example, do the values in A1 and A2 always have decimal places? And what happens when they are both equally nearer?


Jean-Guy

At the moment my spreadsheet changes a figure given say £56, and calculates unused allowances in weeks & months.

So one cell might say this relates to approx 12.2 weeks & another will work it out in Months say 3.1 months.

They then have to decide which is more likely to be correct and place that figure into another cell.

So for instance if they pick the week figure that would go into say Cell C1
If they pick the month figure that would go in Cell C2. Whichever cell isn't used needs to show Zero.

I would like to see if the other cell can automatically be changed to show the figure that.

The cells will contain numbers to one decimal point

In C1

=IF(ABS(A1-ROUND(A1,0))<ABS(A2-ROUND(A2,0)),ROUND(A1,0),0)

and in C2

=IF(C1=0,ROUND(A2,0),0)

Originally Posted by Json

=IF(ROUND(MOD($A$1,1),1)>ROUND(MOD($A$2,1),1),ROUND(A1,0),0)

hello Jason, did you test this? In have a go's example A1 contains 37.8 and A2 contains 11.1 - the required result is for C1 to show 0 and C2 11.

Your suggestion will give 38 in C1 and 0 in C2

Hi All

Excuse the intrusion but all your formulas seems to have a flaw, example 11.1 is favored over 37.9 even though they are both .1 away from a whole number.

Regards
Jean-Guy

Originally Posted by Json

=IF(ROUND(MOD($A$1,1),1)<ROUND(MOD($A$2,1),1),ROUN D(A1,0),0)

This still won't work

It won't be sufficient to look at the rounded result of the MOD function

If A1=32.9 and A2=37.8 then ROUND(MOD(A1,1),1)=0.9 and ROUND(MOD(A2,1),1)=0.8 - the first is greater so C1 should show 33.

But If A1=32.2 and A2=37.1 then ROUND(MOD(A1,1),1)=0.2 and ROUND(MOD(A2,1),1)=0.1 - the first is greater again but this time is further from an integer.

You need to use ABS, i.e.

ABS(A1-ROUND(A1,0))

or something similar.

Hello pinmaster

Of course A1 is returned if both values are equidistant from an integer, my assumption is that one or other value needs to be returned so in the absence of any other advice I choose A1, of course you could always adjust the formula to return A2 rounded if both are the same distance from an integer, or return the greatest or smallest.....

Originally Posted by daddylonglegs

This still won't work

It won't be sufficient to look at the rounded result of the MOD function

If A1=32.9 and A2=37.8 then ROUND(MOD(A1,1),1)=0.9 and ROUND(MOD(A2,1),1)=0.8 - the first is greater so C1 should show 33.

But If A1=32.2 and A2=37.1 then ROUND(MOD(A1,1),1)=0.2 and ROUND(MOD(A2,1),1)=0.1 - the first is greater again but this time is further from an integer.

You need to use ABS, i.e.

ABS(A1-ROUND(A1,0))

or something similar.

Hello pinmaster

Of course A1 is returned if both values are equidistant from an integer, my assumption is that one or other value needs to be returned so in the absence of any other advice I choose A1, of course you could always adjust the formula to return A2 rounded if both are the same distance from an integer, or return the greatest or smallest.....

Hi daddylonglegs

The problem seems to be with the subtractions, on the surface they seem to give .1 but if you extend the decimal point to 16 then one gives 0.0999999999999996 while the other gives 0.1000000000000010, I'm not a math whiz so I can't explain it so how about this instead?

=IF(ROUND(ABS(A1-ROUND(A1,0)),1)<ROUND(ABS(A2-ROUND(A2,0)),1),ROUND(A1,0),0)

Regards
Jean-Guy