Shorcuts or Macro for Creating If Else Statements

[excel wonk]

I create if else statements often. But I find it tedious to have to
type them and redefine them all the time for different workbooks.

For example, let's say you want this type of if else condition for
calculating price to earnings:

a1=price; b1=earnings

=if ( iserror( a1/b1),"n/a", if (or( a1/b1<0, a1/b1>100),"nmf",
a1/b1))

Is there a way of shortening this? What I mean is, of course I can
copy this down the column and calculate a bunch of P/Es for a bunch of
companies.

I'm trying to see if there is a macro or short cut that will help you
create if else statements.

 

[Guest]

Hi,


Here are a couple of ideas:

1. Create a custom VBA function, with your example that would reduce to
=MyFuntion(A1,B1)


2. You could change ISERROR to ISERR,
3. You could use NA() rather than "N/A"
4. You could define a named constant for the "mnf" portion
5. You could define the first cell of the range as a name, here Price would
be defined as =Sheet1!A1 and Eranings as =Sheet1!B1
6. You could combine those so in effect Price/Earnings named D and defined
as =Sheet1A1/Sheet1!B1.

The end result would be:
=IF(ISERR(D),NA(),IF(OR(D<0,D>100),F,D))
7. Of you could define the entire formula as a name if you are reusing it.
8. You could buy Excel 2007 and use the IFERROR function.

Thanks,
Shane Devenshire

 

[Guest]

Hi,

I had to go to breakfast, so now I 've got a minute to give you example of
VBA functions you could use:

Function Tr(P As Double, E As Double)
Dim R As Double
R = P / E
If IsError(R) Then
Tr = "N/A"
ElseIf R < 0 Or R > 100 Then
Tr = "nmf"
Else
Tr = R
End If
End Function

or somewhat more elegant

Function Tc(P As Double, E As Double)
Dim R As Double
R = P / E
Select Case R
Case IsError(R): Tc = "N/A"
Case Is < 0, Is > 100: Tc = "nmf"
Case Else: Tc = R
End Select
End Function