# odd numbers

#### Runes

##### New Member
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?

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### barry houdini

##### MrExcel MVP
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

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

#### ZVI

##### MrExcel MVP
=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))

#### barry houdini

##### MrExcel MVP

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

##### Well-known Member
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>

#### ZVI

##### MrExcel MVP

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,

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

Last edited:

#### barry houdini

##### MrExcel MVP

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

##### Well-known Member
LOL... This is the same direction that yesterdays thread took... heh

#### barry houdini

##### MrExcel MVP
OK, I'll take a stand......

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

=A1=ODD(A1)

Replies
7
Views
197
Replies
8
Views
310
Replies
5
Views
189
Replies
3
Views
126
Replies
6
Views
131

1,132,670
Messages
5,654,653
Members
418,146
Latest member
Shnn028

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back