odd numbers

[Runes]

I have a large table in which I have three columns of numbers. I only use even numbers so I need a formula for my conditional formatting to look for any odd numbers in these columns and turn the cell red when it finds one. Can you suggest anything?

 

[barry houdini]

Hello Runes, welcome to MrExcel,

You can use MOD function, e.g. if your numbers are in columns A to C then just select those 3 columns and use conditional formatting with "formula is" option and this formula

=MOD(A1,2)

select red formatting > OK

 

[Runes]

That worked like a charm and was so easy. Thank you very much!

 

[ZVI]

=MOD(A1,2) works good, but fails within a big numbers, say if A1 is equal to 1000000001

There is the formula for the full range of Excel numbers:
=TRUNC(A1)/2<>TRUNC(TRUNC(A1)/2)

You cal also use the Analysis ToolPak Add-In's function IsEven() in this manner: =Not(IsEven(A1))
But such Add-In should be checked in the listbox of menu Tools > Add-Ins

 

[barry houdini]

If you have numbers that large then I think the simplest alternative would be:

=A1<>EVEN(A1)

Note that you can't use Analysis ToolPak functions like ISEVEN in conditional formatting

 

[phxsportz]

Long debate yesterday over Odd/Even numbers.. Shows the shortcomings of how excel handles Even and Odd numbers...

Sheet3

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 170px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BACKGROUND-COLOR: #ffcc99"># to Evaluate</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff99">12.5</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BACKGROUND-COLOR: #ffcc99">ISEVEN() says it's Even ?</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ccffcc">TRUE </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BACKGROUND-COLOR: #ffcc99">Even() say it's Even ?</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ff0000; COLOR: #ffffff; FONT-WEIGHT: bold">FALSE</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>K6</TD><TD>=ISEVEN(K5)</TD></TR><TR><TD>K7</TD><TD>=EVEN(K5)=K5</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

http://www.mrexcel.com/forum/showthread.php?t=404266&goto=newpost

 

[ZVI]

If you have numbers that large then I think the simplest alternative would be:

=A1<>EVEN(A1)

Note that you can't use Analysis ToolPak functions like ISEVEN in conditional formatting

Hi Barry,

Such formula returns the different result than =Not(ISEVEN(A1)) and than of mine, for example if A1 = 2.5 or A1 = -2.5 etc.

Taking into account that ISEVEN() now is integrated into the current version of Excel 2007 and it not requires the Analysis ToolPak Add-In, I think that the compatibility with ISEVEN() would be the de-facto rule.

Regards,
Vladimir

P.S. Evening Phxsportz, thanks for your inputs in both debates!

 

[barry houdini]

You could get into endless debates about this I think....

My assumption was that numbers would be integers. Once that doesn't hold I think you'd need to make the definition of odd or even very clear.

 

[phxsportz]

LOL... This is the same direction that yesterdays thread took... heh

 

[barry houdini]

OK, I'll take a stand......

If only integers can be odd then I suppose the best formula here would be

=A1=ODD(A1)