Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Sandwich existing formulas with "If iserror(formula),0,formula)"
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 43
Default Sandwich existing formulas with "If iserror(formula),0,formula)"
Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,856
Default Sandwich existing formulas with "If iserror(formula),0,formula)"
If you are getting a #DIV/0! error from your VLOOKUP, then the error
is in the workbook where you are getting the data from, rather than in
the VLOOKUP formula itself. Consequently, you should look at the
"sending" workbooks and eradicate any errors that are in there.

Hope this helps.

Pete

On Dec 23, 4:36*pm, ORLANDO VAZQUEZ
wrote:
Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Sandwich existing formulas with "If iserror(formula),0,formula)"
Hi,

Try this macro. Select the formula you want to change and run it. Try it in
a test environment first

Sub Sonic()
For Each c In Selection
If c.HasFormula Then
F = Mid(c.Formula, 2)
c.Formula = "=IF(ISERROR(" & F & "),""""," & F & ")"
End If
Next
End Sub

Mike

"ORLANDO VAZQUEZ" wrote:

Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 16
Default Sandwich existing formulas with "If iserror(formula),0,formula)"
You could use a find and replace to do this.

"ORLANDO VAZQUEZ" wrote:

Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6
Default Sandwich existing formulas with "If iserror(formula),0,formul
This worked perfectly.

Thank you so much Mike.


"Mike H" wrote:

Hi,

Try this macro. Select the formula you want to change and run it. Try it in
a test environment first

Sub Sonic()
For Each c In Selection
If c.HasFormula Then
F = Mid(c.Formula, 2)
c.Formula = "=IF(ISERROR(" & F & "),""""," & F & ")"
End If
Next
End Sub

Mike

"ORLANDO VAZQUEZ" wrote:

Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6
Default Sandwich existing formulas with "If iserror(formula),0,formula
Of course, but Mike gave the answer right to the point.

Thanks Pete.


"Pete_UK" wrote:

If you are getting a #DIV/0! error from your VLOOKUP, then the error
is in the workbook where you are getting the data from, rather than in
the VLOOKUP formula itself. Consequently, you should look at the
"sending" workbooks and eradicate any errors that are in there.

Hope this helps.

Pete

On Dec 23, 4:36 pm, ORLANDO VAZQUEZ
wrote:
Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando

.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Sandwich existing formulas with "If iserror(formula),0,formul
Glad i could help and thanks for the feedback

"ORLANDO VAZQUEZ" wrote:

This worked perfectly.

Thank you so much Mike.


"Mike H" wrote:

Hi,

Try this macro. Select the formula you want to change and run it. Try it in
a test environment first

Sub Sonic()
For Each c In Selection
If c.HasFormula Then
F = Mid(c.Formula, 2)
c.Formula = "=IF(ISERROR(" & F & "),""""," & F & ")"
End If
Next
End Sub

Mike

"ORLANDO VAZQUEZ" wrote:

Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Sandwich existing formulas with "If iserror(formula),0,formul
Just for info.

Mike's macro does not check to see if ISERROR already exists in the
formula(s)

It won't matter to the result but it does make formulas a lot longer and
look more complex if ISERROR gets doubled up.

=IF(ISERROR(IF(ISERROR(A2&A3),"",A2&A3)),"",IF(ISE RROR(A2&A3),"",A2&A3))

I prefer to check as I go through the selected range.

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 & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP


On Wed, 23 Dec 2009 09:50:01 -0800, ORLANDO VAZQUEZ <Orlando Vazquez wrote:

This worked perfectly.

Thank you so much Mike.


"Mike H" wrote:

Hi,

Try this macro. Select the formula you want to change and run it. Try it in
a test environment first

Sub Sonic()
For Each c In Selection
If c.HasFormula Then
F = Mid(c.Formula, 2)
c.Formula = "=IF(ISERROR(" & F & "),""""," & F & ")"
End If
Next
End Sub

Mike

"ORLANDO VAZQUEZ" wrote:

Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Sandwich existing formulas with "If iserror(formula),0,formul
Gord,

A good point, I never thought of that but for the formula I still prefer

F = Mid(c.Formula, 2)

instead of

myStr = Right(cel.Formula, Len(cel.Formula) - 1)

Mike

"Gord Dibben" wrote:

Just for info.

Mike's macro does not check to see if ISERROR already exists in the
formula(s)

It won't matter to the result but it does make formulas a lot longer and
look more complex if ISERROR gets doubled up.

=IF(ISERROR(IF(ISERROR(A2&A3),"",A2&A3)),"",IF(ISE RROR(A2&A3),"",A2&A3))

I prefer to check as I go through the selected range.

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 & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP


On Wed, 23 Dec 2009 09:50:01 -0800, ORLANDO VAZQUEZ <Orlando Vazquez wrote:

This worked perfectly.

Thank you so much Mike.


"Mike H" wrote:

Hi,

Try this macro. Select the formula you want to change and run it. Try it in
a test environment first

Sub Sonic()
For Each c In Selection
If c.HasFormula Then
F = Mid(c.Formula, 2)
c.Formula = "=IF(ISERROR(" & F & "),""""," & F & ")"
End If
Next
End Sub

Mike

"ORLANDO VAZQUEZ" wrote:

Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando

.

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 6
Default Sandwich existing formulas with "If iserror(formula),0,formul
Good idea Gord.

Thanks.


"Gord Dibben" wrote:

Just for info.

Mike's macro does not check to see if ISERROR already exists in the
formula(s)

It won't matter to the result but it does make formulas a lot longer and
look more complex if ISERROR gets doubled up.

=IF(ISERROR(IF(ISERROR(A2&A3),"",A2&A3)),"",IF(ISE RROR(A2&A3),"",A2&A3))

I prefer to check as I go through the selected range.

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 & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP


On Wed, 23 Dec 2009 09:50:01 -0800, ORLANDO VAZQUEZ <Orlando Vazquez wrote:

This worked perfectly.

Thank you so much Mike.


"Mike H" wrote:

Hi,

Try this macro. Select the formula you want to change and run it. Try it in
a test environment first

Sub Sonic()
For Each c In Selection
If c.HasFormula Then
F = Mid(c.Formula, 2)
c.Formula = "=IF(ISERROR(" & F & "),""""," & F & ")"
End If
Next
End Sub

Mike

"ORLANDO VAZQUEZ" wrote:

Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando

.

Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 22,906
Default Sandwich existing formulas with "If iserror(formula),0,formul
Neater looking and shorter.

Gord

On Wed, 23 Dec 2009 16:07:01 -0800, Mike H
wrote:

Gord,

A good point, I never thought of that but for the formula I still prefer

F = Mid(c.Formula, 2)

instead of

myStr = Right(cel.Formula, Len(cel.Formula) - 1)

Mike

"Gord Dibben" wrote:

Just for info.

Mike's macro does not check to see if ISERROR already exists in the
formula(s)

It won't matter to the result but it does make formulas a lot longer and
look more complex if ISERROR gets doubled up.

=IF(ISERROR(IF(ISERROR(A2&A3),"",A2&A3)),"",IF(ISE RROR(A2&A3),"",A2&A3))

I prefer to check as I go through the selected range.

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 & "),""""," & myStr & ")"
End If
End If
Next
End Sub


Gord Dibben MS Excel MVP


On Wed, 23 Dec 2009 09:50:01 -0800, ORLANDO VAZQUEZ <Orlando Vazquez wrote:

This worked perfectly.

Thank you so much Mike.


"Mike H" wrote:

Hi,

Try this macro. Select the formula you want to change and run it. Try it in
a test environment first

Sub Sonic()
For Each c In Selection
If c.HasFormula Then
F = Mid(c.Formula, 2)
c.Formula = "=IF(ISERROR(" & F & "),""""," & F & ")"
End If
Next
End Sub

Mike

"ORLANDO VAZQUEZ" wrote:

Hi,

I have several hundred different formulas on one spreadsheet like this one.

=VLOOKUP($C$1,'P:\Rate Sheets\client name\[file name.xls]manager
name'!$BV$67:$CL$131,3,FALSE)

The results sometimes return #DIV/0!, or other error messages which ruin my
attempts at summing the results as well as the visual appeal.

Is there a way to rewrite (en masse) these formulas to so that they first
check "If(iserror(myformula),[then] 0, [else] myformula) so that rather then
an error result I will see a zero (0).?

I can manually change them but that would take a day at least.

Please let me know.

Thank you,
Orlando

.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
=IF(ISERROR(SEARCH("insurance",A125,1)),"","*") cynichromantique Excel Worksheet Functions 9 September 25th 08 10:49 PM
Formula Result is "V6", need Excel to use cell "V6", not the resul Erik Excel Worksheet Functions 3 September 5th 08 04:10 PM
TRYING TO COMBINE FORMULAS INTO AN "OR" FORMULA William Excel Discussion (Misc queries) 3 July 25th 08 04:40 PM
embedding "ISERROR" function into an "IF" statement [email protected] Excel Worksheet Functions 8 January 4th 07 01:01 AM
Syntax to "OR" 3 "ISERROR" conditions Mike K Excel Worksheet Functions 6 July 22nd 06 05:18 PM


All times are GMT +1. The time now is 05:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement