Shorcuts or Macro for Creating If Else Statements

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.

0
excel8208 (9)
6/2/2007 5:38:11 AM
excel 39880 articles. 2 followers. Follow

2 Replies
420 Views

Similar Articles

[PageSpeed] 49

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


"excel wonk >" wrote:

> 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.
> 
> 
0
6/2/2007 4:56:02 PM
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
-- 
Cheers,
Shane Devenshire


"excel wonk >" wrote:

> 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.
> 
> 
0
6/2/2007 6:35:00 PM
Reply:

Similar Artilces:

MACRO #45
Can a macro be created in my excel and then saved to our server on an excel spreadsheet. When I create one and then save it, it does not appear when I open the spreadsheet on the server. Thanks Pam: When you create the macro, make sure you save it in "This Workbook" and not in your Personal Macro Storage. Please see Step 1, and "Store macro in". http://www.officearticles.com/excel/record_a_macro_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com "Pam Coleman" <PamColeman@discussions.microsoft.com> wrote in message news:D7A7...

Executing a macro
Hi there, I can't find the convenient way to run a macro directly from shortcu on my desktop. I'd like to be able to run a certain macro just b clicking on my visio document shortcut, then this would open m document with everything done! I put the right path, but is possible t add the name of the macro to run? (like for Word: path/M macroname) thanx a lot for your help Avalon2 -- avalon2 ----------------------------------------------------------------------- avalon27's Profile: http://www.officehelp.in/member.php?userid=18 View this thread: http://www.officehelp.in/showthread.p...

VB Macro
Hello,, When I wrote a macro in excel ( for ex. a button click), and saved i in html format, althogh this button works in excel ,it doesnt work i html file. Shold I change some settings in excel -- Message posted from http://www.ExcelForum.com Hi AFAIK no chance as HTML does not support these kind of macros! -- Regards Frank Kabel Frankfurt, Germany > Hello,, > > When I wrote a macro in excel ( for ex. a button click), and saved it > in html format, althogh this button works in excel ,it doesnt work in > html file. Shold I change some settings in excel? > > > ---...

Copy down formula macro
Hi I have this macro that doesn't work and I am not sure why: LastRow = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row Sheets("Sheet1").Range("P2:q2").Copy _ Destination:=Sheets("Sheet1").Range("P3:Q" & LastRow) Calculate Macro should check how many rows are in column D and then copy the formula form P2:Q2 down to the last row. -- Greatly appreciated Eva Hi, In what way doesn't it work because it looks fine to me? Mike "Eva" wrote: > Hi > I have this macro that...

Create new project in PWA with custom fields
Hello, I hope someone can point me to the right direction since I'm so new to PS 2007 and PSI. I need to create a custom page in PWA that allows users to create a new project. In my custom page, there are custom fields/dropdownlists with values. For example, I will need to populate a dropdownlist with division names (A, B, C, or D...), and the values of other dropdownlists on the page will be populated based on whatever division name I select from my dropdownlist. For this, I create a custom webpart page, and created custom field "Division" with lookup ta...

FRx If/THEN/ELSE statement
Hi, I am trying to use IF/THEN/ELSE statement in column design using FRx 6.5. It seems that no text character can be put after ELSE statement, i.e IF((B/C)-1)>-1 THEN (B-C)/C ELSE "n/c" Does this sound right? or anybody know how to create condition statement with text character after CALC type selected in the column detail. Any help would be appreciated. Pamela Zhou Were you ever able to find a solution to this? I am currently trying to figure this out myself. Thanks "Pam" wrote: > Hi, I am trying to use IF/THEN/ELSE statement in column design using FRx 6....

disable macro???
Hi all, when loading Excel I get the message disable Macros---enable macros. How do I bypass this window and have macros enabled by default. Regards Graham Graham, Depending on your release of Excel Tools - Options - General - Macro virus protection: unchecked Tools - Options - Security - Macro Security: Low This message normally comes up when you open a file containing macros, not when you open Excel. It's to warn you there are macros in the file you're opening. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "g...

What happened to my macros?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel I had macros set up in version 2004 so I did not have to repeatedly type my 3 doctor's names, titles, etc. at the end of each letter. Now the Macros are something entirely different and worthless. How do I set up my macros again? Hi Edra, Well, Macros written in VBA will not work in Office/Word 2008. However, I'm not sure that what you had were really macros--both AutoCorrect and AutoText can be used to do what you describe, without a real macro, although WordPerfect used to call text shortcuts like that mac...

macro shuts excel down
attempting to run a previously working macro causes excel 2003 to shut down Open XL with macros disabled, then analyze your code. There is several ways using macros to shut XL down upon a workbook opening, you may need to fix the code. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "bobmayers" wrote: > attempting to run a previously working macro causes excel 2003 to shut down ...

Exchange 2000
A couple of days ago, we noticed that our server was getting bogged down and the hard drive was running constantly. Upon looking into this further, I found that relaying was enabled, and that there were hundreds of thousands of messages in the BadMail folder. If I stop the SMTP virtual server, the disk activity stops. I go in and delete all of the files in the BadMail folder. As soon as I start the SMTP virtual server again, the folder starts filling up with messages again. I cannot figure out where these messages are coming from! HELP! The messages are in the internal processing queues of th...

including solver in a Macro
I am working on creating a macro that will identify cells to be used in the solver as the "target cell" and the "by changing cell" boxes. I also need it to pick up a figure in the "equal to value" box from a specific cell in the spreadsheet. Once these are defined, it needs to begin the solving process. When it is done, go on with the rest of the macro. I hope this makes sense. Thanks, Scot Scot, A good starting point is often to record a macro doing the solver with your parameters, and look at the resulting code. Earl Kiosterud mvpearl omitthisword at ...

Shorcut to my computer
In my previous version of Outlook (2000) I had a shorcut to My Computer in the left pane that, when selected, opened a folder list in the rigth pane. This was quite useful, because it permitted me to have Outlook acting like a file manager and, particularly because it was the only way I had to print a list of the content of any directory. In Outlook 2003, however, it opens a new window, that does not have the same functionality. Is there a workaround to this or I am doing something wrong? Any help would be appreciated Thanks, Luis No, this has been dropped to deliver more security and funct...

OL 2003: How to print the Created field in tasks?
When I print an Outlook 2003 task, the following fields get printed: Subject Status Percent Complete Total Work Actual Work Owner If a task has a Start Date, then it also gets printed. Interestingly, I created a custom field, which I call "Actor" and it also gets printed. However, I would like to print the Created field (which has the date the task was created). Advise please. ...

How do you create a mailbox for a security group in exchange 2000?
I am having trouble to how to create a mailbox for a security group in exchange 2000. It creates the mail address and alias but not the mail box. Groups don't have mailboxes. You might look into setting up a resource mailbox or a public folder depending on the use needed. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Chuco...

how do I create a chart using nonadjacent ranges?
I am trying to create a chart using nonadjacent ranges in Excel 2007. The directions say to select the first range, then hold down CTRL and select the second. When I hold down control it will only let me select one cell. What am I doing wrong? Thanks. The directions are correct, when you select the second column just drag the mouse down the column best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "floridamurph" <floridamurph@discussions.microsoft.com> wrote in message news:49C44302-91C9-4E7A-A25B-EBDBE8C317A1@...

Transfer Macro to another user
Ive created a macro and I would like to send it to another user in order for him to run it on his proper excel files. Ive sent him the file with the macro attached, how can he register it to have it available each time he'll open an Excel File? Thanks Poolpa -- Poolpa ------------------------------------------------------------------------ Poolpa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32894 View this thread: http://www.excelforum.com/showthread.php?threadid=527099 Hi Poolpa.......... If he wants the macro to be available to run on any Excel fi...

Translation to International Excel 4 Macro
I have a Excel 4 Macro sheet that looks like this: Auto_Open =ERROR(2;Recover) =WORKBOOK.SELECT("Sheet 1") =WINDOW.RESTORE() =WINDOW.SIZE(82.5;42.75) =WORKBOOK.HIDE("Macro1";TRUE) =EDIT.COLOR(1;0;0;0) =EDIT.COLOR(3;255;0;0) =EDIT.COLOR(4;212;212;212) =EDIT.COLOR(5;247;247;231) =EDIT.COLOR(2;255;255;255) =RUN("Macro5") =DELETE.NAME("Auto_Open") =WORKBOOK.SELECT("Sheet 1") =SELECT("R1:R65536") =COLUMN.WIDTH(;;;3) =ROW.HEIGHT(;;;3) =SELECT("R1C1") =WINDOW.MAXIMIZE() =RETURN() Macro5 =WORKBOOK.SELECT("Sheet 1") =SELE...

Runtime error for macro that works in workbook created in
Hi there, I created a macro to fill in fields and saved it in my personal workbook so it could be used for all my workbooks. It works in the original workbook I created it in, but when I try to open it in others, I get the 1004 runtime error! Is there something I'm doing wrong? All my fields are named, and when I run it, it fills in the first field but gives me the error immediatly after. Here's my code, hopefully someone can offer me help. Thanks! Crystal Sub Macro1() ' ' FormFill Macro ' Range("REQUESTORF").Select ActiveCell.FormulaR1C1 = &qu...

Update Chart Macro
Hi, I have a workbook that contains a summary sheet (based on a 3d formula) from which I create 2 charts. Can anyone suggest a macro so my user can press a button and a macro runs to detect any new worksheets that have been added, add this new sheet to the 3d formula in the summary sheet and the associated graphs are updated? Many thanks ...

macro ? #2
Used MS Office 97 since 97. Never tried to create and complicated macros. I found the one below in a public domain spreadsheet. The following error open when the macro runs: Ambiguous name detected: GetData. The second Sub GetData() is higlighted in blue. Any help appreciated. I can upload the ss to my website if need be. dlw Sub Module1() Sub GetData() Sub GetData() Dim QuerySheet As Worksheet Dim DataSheet As Worksheet Dim qurl As String Dim i As Integer Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCa...

How do I create a graph displaying two parameters for one item
My example: Distance verses time for the path that a car travels A1:A10- some time values B1:B10- some distance values select any cell in A1:B10 click on Chart Wizard and select XY chart click OK a few times now you need to do some formatting double click elements of chart and format as needed read a simple Excel book to learn more best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "lavalisa" <lavalisa@discussions.microsoft.com> wrote in message news:89B1537B-7737-4B14-BB07-057F37B466D6@microsoft.com... > My example: Distance verses tim...

Just upgraded to Office XP and macros from an older version are n.
all macros not working / some ??? have you changed your security setting to medium (tools / macro / security)? a few more details might help :) Cheers JulieD "The spaceman" <The spaceman@discussions.microsoft.com> wrote in message news:9EF1494F-CAC9-43A0-AE25-C1A3337F03B8@microsoft.com... > ...

Opening Files
Help Needed. I had developed a macro that was used by myself, however now the macro needs to be utilised by others. The marco opens files, that have been exported from the corporate database. The files are saved automatically to the users C Drive eg. C:\Documents and Settings\"username – officer logged on"\help.xls The only difference is the username in the file path. Is there anyway that I can change the file path to automatically go to the correct file, without having to create a different macro for each user. Any help would be greatly appreciated. You hav...

Problem creating mail account
Has anyone had problems creating new mail accounts? I can create a new domain account ok with the option to create a mail account selected but the mail account doesn't seem to exist. It's happened on different systems and I can't find anything which gives any clues! Any help would be much appreciated. Thanks, G. Hello Gaz Can you give us a little more information? What kind of domain is it? NT4, Win2K, Win2K3 What version of Exchange? 5.5, E2K, E2k3 Do you get any errors when selecting to create a mailbox? Do you see any events in the application log? -- Michael Barta [M...

Shorcut
Hi, Does anybody know the shortcut for renaming sheets? I mean to change the name of the tab at the bottom of the screen, (Sheet1, Sheet2, etc), without using the mouse. Thanks. John It's amazing what you can find in HELP when you look Work with worksheets SHIFT+F11 or ALT+SHIFT+F1 Insert a new worksheet. CTRL+PAGE DOWN Move to the next sheet in the workbook. CTRL+PAGE UP Move to the previous sheet in the workbook. SHIFT+CTRL+PAGE DOWN Select the current and next sheet. To cancel selection of multiple sheets, press CTRL+PAGE DOWN or, to select a different sheet, pres...