Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplfying DIV/0 Error...Can you?
I have a sheet with hudreds of cells with the similar formula as follows:
IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)) The formula works okay unless J4 is blank then I get DIV/O Error in several cells and it affects the last cell that totals numbers. I have modified the formula to read as follows: IF(ISERROR(IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)),0,IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)) This also works, but it is very tiresome to have to change hundreds of cells at this point. QUESTION: Is there an easier way to do this by formatting all the cells or the entire sheet in any way? THanks, Neil M |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplfying DIV/0 Error...Can you?
=IF(OR(I4=0,J4=""),0,IF(J4="EF Vert",F4/I4,0)+IF(J4="EW EF",F4/I4,0))
-- __________________________________ HTH Bob "Neil M" wrote in message ... I have a sheet with hudreds of cells with the similar formula as follows: IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)) The formula works okay unless J4 is blank then I get DIV/O Error in several cells and it affects the last cell that totals numbers. I have modified the formula to read as follows: IF(ISERROR(IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)),0,IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)) This also works, but it is very tiresome to have to change hundreds of cells at this point. QUESTION: Is there an easier way to do this by formatting all the cells or the entire sheet in any way? THanks, Neil M |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplfying DIV/0 Error...Can you?
On Jun 24, 2:11 pm, Neil M wrote:
I have a sheet with hudreds of cells with the similar formula as follows: IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)) The formula works okay unless J4 is blank then I get DIV/O Error in several cells and it affects the last cell that totals numbers. I have modified the formula to read as follows: IF(ISERROR(IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)),0,IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)) This also works, but it is very tiresome to have to change hundreds of cells at this point. QUESTION: Is there an easier way to do this by formatting all the cells or the entire sheet in any way? THanks, Neil M There should be a way to copy this quickly to all the cells at once after fixing just one cell by hand. Is it a column of this formula? And I'm guessing the formula changes in each different cell. How is it set up? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplfying DIV/0 Error...Can you?
You would be best off to re-build as Bob suggests but if you want to add the
ISERROR to existing formulas use 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 & "),""""," & myStr & ")" End If End If Next End Sub Note that ISERROR will mask all errors, not just the #DIV/0! Gord Dibben MS Excel MVP On Tue, 24 Jun 2008 12:11:17 -0700, Neil M wrote: I have a sheet with hudreds of cells with the similar formula as follows: IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)) The formula works okay unless J4 is blank then I get DIV/O Error in several cells and it affects the last cell that totals numbers. I have modified the formula to read as follows: IF(ISERROR(IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)),0,IF(J4="EF Vert",((F4/I4)),0)+IF(J4="EW EF",((F4/I4)),0)) This also works, but it is very tiresome to have to change hundreds of cells at this point. QUESTION: Is there an easier way to do this by formatting all the cells or the entire sheet in any way? THanks, Neil M |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplfying DIV/0 Error...Can you?
It would not be hard to copy and paste, just tiresome. This is more for future reference. I think I will give Bob Phillips formula a try and see how that works. As far as masking all errors, I am not worried about it for this sheet as the blank cell is the only thing that would cause the error. ONce that is filled everything else works. Normally if it were just for me, I would leave it alone knowing that a value in JF would eventually solve the problem. However this is for others in the office to use later and I don't need a hundred emails telling me that the sheet has an error before they fill in cells. Thanks for all the help!! Neil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error: "Excel encountered an error and had to remove some formatti | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |