"if" function ~ odd/even numbers

  • Thread starter Thread starter sokevin
  • Start date Start date
S

sokevin

i need to do this

if cell A1 is odd then i want to take the negative, if A1 is even the
i want the positive

=if(a1="odd", -a1, +a1)

how do i specify "odd"
 
Hi
you can try
=IF(MOD(A1,2),"odd","even")
or use the functions
ISEVEN and ISODD respectively. e.g.
=IF(ISODD(A1),"odd","even")

HTH
Frank
 
sokevin

=IF(ISODD(A1),(-A1),A1)

ISODD and ISEVEN are Analysis Toolpak functions.

Tools>Add-ins. Check ATP.

Gord Dibben Excel MVP
 
Hmmm - Now I'm confused as to what he wanted :-) I just read it as he wanted a
negative value regardless one way, and a positive value the other, but now I
read it your way as well - Too tired for this methinks (getting late over here)
:-)
 
=A1*(2*(MOD(A1,2)=0)-1)
The stuff inside the parentheses will return 1 if even, -1 if odd.
 
Ken Wright said:
Hmmm - Now I'm confused as to what he wanted :-) I just read it as he wanted a
negative value regardless one way, and a positive value the other, but now I
read it your way as well - Too tired for this methinks (getting late over here)
Click to expand...

Sure is. Next logical step would be "which real life problem could possibly need a thing
like this?" and there goes a good night's sleep :-)
 
All I looked at was OP's original attempt at a formula
=if(a1="odd", -a1, +a1)
Click to expand...

and translated that to

=IF(ISODD(A1),(-A1),A1)

Straightforward to me<g>

Gord
 
Gord Dibben said:
Harald
Even better. Don't need ATP.
Click to expand...

Hi Gord

Thanks Gord. No, I never install ATP. I'm in Norway, and since the ATP functions don't
translate between language versions, and since my employer don't install it for my users
by default, it's a totally useless thing to me.

Best wishes Harald
Followup to newsgroup only please.
 
Not sure, but another idea might be to switch the signs and drop the "=0"
part?

=A1*(-2*MOD(A1,2)+1)
=A1*(2*(MOD(A1,2)=0)-1)
Click to expand...
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Harald Staff said:
The stuff inside the parentheses will return 1 if even, -1 if odd.
Click to expand...
 
Back
Top