Writing IF statement for ODD numbers

1
votes

I am trying to figure out how to write an IF statement such as this 

 IF (value in column A is ODD, then do this)
 IF (value in column A is EVEN, then do this)

With the cells having both letters and numbers in them (i.e. cells would have the following format in a column: A1, A2, A3, etc.). This is my current formula:

=IF(EVEN(A13)=A13, I13, L13),

However, I can't somehow manipulate it such that it omits the letter in the cell and just looks at the number. Any suggestions would be gladly appreciated!

3
excelexcel-formula
Will it always be one letter? Just substitue A13 with RIGHT(A13, LEN(A13)-1) – Robin Gertenbach
Not always, a cell could look like (AB24 or ABC24). I just want to look at the numerical value and write a formula incorporating this into an IF statement looking to see whether the numerical value is even or odd. – tellap
So the number is always at the very end of the string? – XOR LX
The VBA function IIF() (inline if) works much the same way as Excel's IF formula. – jsheeran
The letters and numbers are in mixup (AB12F8) or always letters at the start, numbers at the end? – Blenikos

3 Answers

1
votes

To test for evenness of a number, it is sufficient to perform the test on the last digit only of that number.

As such, and if the numbers only appear at the end of the string, then:

=ISEVEN(RIGHT(A1))

Regards

0
votes

Here's an array formula that will work but I think there is a simpler way. You'll need to press ctrl+shft+enter:

=IF(ISEVEN(VALUE(MID(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),"")),
LEN(A1)-MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),""))+1))),I1,L1)

Here is the non-array version. Just press enter:

=IF(ISEVEN(VALUE(MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&
{0;1;2;3;4;5;6;7;8;9})),LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9},
A1&{0;1;2;3;4;5;6;7;8;9}))+1))),I1,L1)
0
votes

Here is a vba function that I use to extract numbers from a string:

Public Function numer(val As Variant) As Double
Dim collector As String
Dim i As Long

For i = 1 To Len(val)
    If IsNumeric(Mid(val, i, 1)) Or Mid(val, i, 1) = "," Then: _
    collector = collector + Mid(val, i, 1)
Next i

numer = CDbl(collector)

End Function

You can add that on a new module and then use it inside your formulas as you wish (like a normal excel function, see below in the screenshot).

Result

For more info on how to do this (use a vba function in your formulas) please take a look here: https://support.office.com/en-us/article/Create-Custom-Functions-in-Excel-2007-2f06c10b-3622-40d6-a1b2-b6748ae8231f

If you need to have a coma for decimal places inside your number, you have to change a little bit the if statement in that function to accept commas.

EDIT: I've improved the function to accept decimal numbers (with comma) and also I have created a more complex exampel for you that shows how to use the function with the result (In column B on my screenshot) and how to use it inside a formula with IF.