Need to change rounding so $192.50 is $192, not $193. And, $192.00 would need to stay $192.

[lmbarnett]

Hi all,

I'm working on a spreadsheet of income figures for a particular program (like, a public support program, not a computer program). In this program, we've got certain rules about rounding numbers. To keep it short, I need a way to make a $192.50 into $192, not $193. And I need $192.00 to stay $192. I need to be able to do this for both a calculated result via formula and a number that's simply typed in.

I hope this is clear.
I'm using Windows 7 and Excel 2010.

Thank you, thank you! The forum has already helped me turn negative numbers into just 0's.

 

[Gerald Higgins]

Hi, welcome to the board.

First, are you SURE you want to do this ?
I believe that rounding 0.5 up to 1 is fairly standard practice.
But if you really do, here's one way (there are lots of other ways too, some probably better than this)

=IF(MOD(a1,1)=0.5,ROUNDDOWN(a1,0),ROUND(a1,0))

 

[Gerald Higgins]

Here's another way that's a bit shorter

=ROUND(a1,0)-(MOD(a1,1)=0.5)

 

[JoeMo]

Here's another way that simply removes an insignificant amount from the value prior to rounding:

=ROUND(A1-0.000001,0)
This returns 192 for 192.50 and 193 for 192.51.

 

[Armando Montes]

Try:
=INT(A1)

 

[Gerald Higgins]

Armando - =INT(A1) will convert 192.6 to 192.
Which MIGHT be what is wanted, but I interpreted the OP as just concerning values ending in exactly 0.5, and in that case INT() would not be correct.
But re-reading the OP, it's not really that clear, so your interpretation may well be correct.
Anyway, just thought I should clarify what INT() does.

 

[mk2madman]

Formating the number to 0 deicmal points will only display the number as you wish or you can use =round("cell ref", 0)

 

[Gerald Higgins]

mk2madman - that won't work as requested for values like 192.5

 

[lmbarnett]

Thank you all for your help.

I'm going to be away from my desk for a few days but will surely use your tips upon my return. I'll update everyone on how it works.

And, yep, I really do want to go with the non-standard rounding conventions as per the quirky program policy.

Best,
Lindsey

 

[lmbarnett]

Oh, and for what its worth, the number will always be $###.50 or $###.00, no other numbers will be in the mix.

Thanks! Sorry I didn't specify that in the initial request!