Rounding Even and Odd Numbers Differently

[DocShock]

I would like to know if there is a way round even and odd number
differently. For example, if I wanted odd numbers to round to eve
numbers, but wanted even numbers to remain even, how would I do that?

This way would mean that 0.145 would be 0.14 and 0.155 would roun
0.16.

So I guess what it comes down to is that I would like my even number
to round down and my odd numbers to round up. The result be an eve
number in every case.

Thanks for taking the time to read this question. Any suggestion
would be greatly appreciated.

Thanks

 

[Frank Kabel]

Hi
problem is that neither of both of your numbers is odd or
even (this applies only to integers). If you always have 3
significant decimals you could try the following):
=IF(ISODD(A1*1000),ROUNDUP(A1,2),ROUNDDOWN(A,2))

Note: the Analysis Tolpak Add-in has to be installed for
this

 

[Peter Atherton]

You will need to have added the Analysis Tool Pack for
these formulas to work

=IF(ISEVEN(MID(C10,LEN(C10)-1,1)),ROUNDDOWN(C10,2),ROUND
(C10,2))

Regards
Peter

 

[Norman Harker]

Hi DocShock!

One way:
=IF(ISODD(RIGHT(ROUNDUP(A1,2),1)),ROUNDDOWN(A1,2),ROUNDUP(A1,2))

ISODD is an Analysis ToolPak function.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/

 

[Earl Kiosterud]

Doc,

Your description seems a bit ambiguous. But the following works for your
examples:

=ROUND(A2/2,2)*2

 

[DocShock]

Sorry for being so vague guys. Let try to explain better. The numbe
in which I speak is the result of several formulas and could have
number of decimal places.

So if my number works out to be 1.23454, then it would obviously roun
to 1.2345. But if that number was to end in a 5, I would like to hav
it round down if the number before it is even and round up if th
number before it is odd.

So if that case I need I formula that would make 1.2345 round to 1.23
and 1.2355 round to 1.236.

I guess what it comes down to is this: if the second last digit of th
number is even and the number ends in 5, I want it to round down (ie
1.2345 would round down to 1.234). If the second last digit of th
number is odd and the number ends in 5, I want it to round up (ie
12355 would round up to 1.236).

The second last digit and the number ending in 5 are the keys to what
want to have happen.

Thanks so very much for the replies. Your help is greatly appreciated

 

[Jerry W. Lewis]

ASTM standard rounding (round 5 to even) is done by the VBA round
function that was introduced in Excel 2000. For reasons that are
unclear to me, MS calls this "banker's rounding".

For an easy way to use this VBA round function in a worksheet, see

http://groups.google.com/groups?selm=3E55A396.8080708@no_e-mail.com

Jerry

 

[DocShock]

Thanks for the reply. How exactly do I implement Visual Basic so as t
use the Banker's Rounding function? I am not all the familar with VBA
Do I have to install something first to get this functions to work i
a spreadsheet? If I have a simple spreadsheet will I still be able t
implement VBA and use the functions you suggested?

Thanks again.

Do

 

[Norman Harker]

Hi DocShock!

See the reply given in your separate post on Bankers Rounding.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)

It is imperative that the patches provided by Microsoft in its April
Security Release be applied to Systems as soon as possible. It is
believed that the likelihood of a worm being released SOON that
exploits one of the vulnerabilities addressed by these patches is VERY
HIGH.
See:
http://www.microsoft.com/security/protect/

 

[Jerry W. Lewis]

Alt+F11 (or Tools|Macro|Visaual Basic Editor)
Insert|Module
Paste the code from the the link I provided earlier.

In the workbook you can then use the function ASTMround() with the same
syntax as the worksheet ROUND() function.

Jerry