How to simplify a L O N G functional argument

[Gene]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[John C]

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)

 

[Bernard Liengme]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

If you're using Excel 2007:

=IFERROR(AVERAGE(D12:G12),"No Data")

 

[Hershey]

AND(IF(Excel_Version=2007,use IFERROR(),0))

 

[Gord Dibben]

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