Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Ignoring #DIV/0! in a formula
Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried
messing around with IF statements but I cant seem to figure it out |
#2
|
|||
|
|||
=IF(ISERROR(cellref),erroraction,noerroraction)
=IF(ISNUMBER(cellref),noerroraction,erroraction) You can also use it in range functions, such as =SUM(IF(ISNUMBER(range),range)) but you must array enter (Ctrl-Shift-Enter) Jerry DLZ217 wrote: Is it possible to ignore a cell in a formula if the data is #DIV/0 ive tried messing around with IF statements but I cant seem to figure it out |
#3
|
|||
|
|||
"Jerry W. Lewis" wrote...
=IF(ISERROR(cellref),erroraction,noerroraction) =IF(ISNUMBER(cellref),noerroraction,erroraction ) You can also use it in range functions, such as =SUM(IF(ISNUMBER(range),range)) but you must array enter (Ctrl-Shift-Enter) .... Masking all errors is usually not a good idea. It's not all that difficult to isolate just the #DIV/0! error. =IF(COUNT(1/(ERROR.TYPE(x)=2)),error_return,nonerror_return) =SUM(IF(ISERROR(1/(ERROR.TYPE(rng)=2)),rng)) Better still would be to trap the condition giving rise to the #DIV/0! error values, such as trapping zero denominators or stats calculated from ranges containing no numbers. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Named SUM Formula with relative refernce(s) | Excel Worksheet Functions | |||
Frequency formula | Excel Worksheet Functions | |||
I want a formula to ignore text eg 5mts * 5 ignoring the mts any . | Excel Discussion (Misc queries) | |||
Cell shows formula and not the result of the formula. | Excel Worksheet Functions |