Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to simplify a L O N G functional argument
I have the following function (this is a "simple" example):
=IF(ISERROR(AVERAGE(D12:G12)),"No Data",AVERAGE(D12:G12)) I don't want to retype, the "AVERAGE (D12:G12)" argument, can I use a "define name" to replace this functional argument? (here in a very simple format) THANKS! Gene:) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to simplify a L O N G functional argument
Yes... click Insert/Name/Define, type in your name (say AVG) and put the
value in as a formula (even though it will be in the middle of your own formulas... =AVERAGE(Sheet6!D12:G12) You may want to use absolute cell references depending on if you will be copying it and if you want or need to absolute fix either the row and/or column reference. Rick "Gene" wrote in message ... I have the following function (this is a "simple" example): =IF(ISERROR(AVERAGE(D12:G12)),"No Data",AVERAGE(D12:G12)) I don't want to retype, the "AVERAGE (D12:G12)" argument, can I use a "define name" to replace this functional argument? (here in a very simple format) THANKS! Gene:) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to simplify a L O N G functional argument
Go to Menu, Insert--Name--Define
Names in workbook: TheData ---or some other name that you would use Refers to: =AVERAGE(Sheet1!$D12:$G12) ---use appropriate sheet name of course Then, for example, in cell H12, I would type the followingw: =IF(ISERROR(TheData),"No Data",TheData) -- John C "Gene" wrote: I have the following function (this is a "simple" example): =IF(ISERROR(AVERAGE(D12:G12)),"No Data",AVERAGE(D12:G12)) I don't want to retype, the "AVERAGE (D12:G12)" argument, can I use a "define name" to replace this functional argument? (here in a very simple format) THANKS! Gene:) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to simplify a L O N G functional argument
In Excel 2003 (all earlier): Insert | Name | Define
Give a name ( I used MYAVG) Enter in refers to: =AVERAGE(Sheet1!$D$12:$G:12) best wishes-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Gene" wrote in message ... I have the following function (this is a "simple" example): =IF(ISERROR(AVERAGE(D12:G12)),"No Data",AVERAGE(D12:G12)) I don't want to retype, the "AVERAGE (D12:G12)" argument, can I use a "define name" to replace this functional argument? (here in a very simple format) THANKS! Gene:) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to simplify a L O N G functional argument
Yes... click Insert/Name/Define, type in your name (say AVG) and put the
value in as a formula (even though it will be in the middle of your own formulas... =AVERAGE(Sheet6!D12:G12) I left out this part... so you can now write your formula like this... =IF(ISERROR(AVG),"No Data",AVG) Rick |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to simplify a L O N G functional argument
If you're using Excel 2007:
=IFERROR(AVERAGE(D12:G12),"No Data") -- Biff Microsoft Excel MVP "Gene" wrote in message ... I have the following function (this is a "simple" example): =IF(ISERROR(AVERAGE(D12:G12)),"No Data",AVERAGE(D12:G12)) I don't want to retype, the "AVERAGE (D12:G12)" argument, can I use a "define name" to replace this functional argument? (here in a very simple format) THANKS! Gene:) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to simplify a L O N G functional argument
AND(IF(Excel_Version=2007,use IFERROR(),0))
"Gene" wrote: I have the following function (this is a "simple" example): =IF(ISERROR(AVERAGE(D12:G12)),"No Data",AVERAGE(D12:G12)) I don't want to retype, the "AVERAGE (D12:G12)" argument, can I use a "define name" to replace this functional argument? (here in a very simple format) THANKS! Gene:) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to simplify a L O N G functional argument
One other method
Write your original formula as just =AVERAGE(D12:G12) You can add the error trap to all selected cells with this macro. Sub ErrorTrapAdd() Dim myStr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=IF(ISERROR*" Then myStr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=IF(ISERROR(" & myStr & "),"No Data"," & myStr & ")" End If End If Next End Sub Gord Dibben MS Excel MVP On Wed, 16 Jul 2008 12:41:01 -0700, Gene wrote: I have the following function (this is a "simple" example): =IF(ISERROR(AVERAGE(D12:G12)),"No Data",AVERAGE(D12:G12)) I don't want to retype, the "AVERAGE (D12:G12)" argument, can I use a "define name" to replace this functional argument? (here in a very simple format) THANKS! Gene:) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using + and / as characters not as functional formatting | Excel Discussion (Misc queries) | |||
Excel 2007 Nearly Non Functional | Excel Discussion (Misc queries) | |||
Fully functional spreadsheets on web? | Excel Discussion (Misc queries) | |||
Conversion of Cell Contents into a Functional Worksheet name ? | Excel Worksheet Functions | |||
Non-functional Popup Menu Boxes | Charts and Charting in Excel |