Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding #DIV/0! in further calculations
Hi,
I am doing some calculations in several steps. In some steps the result of some cells is #DIV/0! as a result the final result (STDEV) gives #REF! how to exclude the cells that contain #DIV/0! so I get the result in the last step, based on the cells that had a intermediate result? (without adapting ALL intermdiate steps with IF etc) so i am looking for a simple trick in the last step Thanks! tom |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding #DIV/0! in further calculations
Use an array function akin to
=STDEV(IF(NOT(ISERROR(A1:E3)),A1:E3)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "tom ossieur" wrote in message ... Hi, I am doing some calculations in several steps. In some steps the result of some cells is #DIV/0! as a result the final result (STDEV) gives #REF! how to exclude the cells that contain #DIV/0! so I get the result in the last step, based on the cells that had a intermediate result? (without adapting ALL intermdiate steps with IF etc) so i am looking for a simple trick in the last step Thanks! tom |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding #DIV/0! in further calculations
tom ossieur wrote...
I am doing some calculations in several steps. In some steps the result of some cells is #DIV/0! as a result the final result (STDEV) gives #REF! The only way you get #REF! is if the #DIV/0! error screws up a function that returns a range reference, e.g., INDEX, OFFSET or INDIRECT. Why not tell us which? how to exclude the cells that contain #DIV/0! so I get the result in the last step, based on the cells that had a intermediate result? (without adapting ALL intermdiate steps with IF etc) If the #DIV/0! is screwing up intermediate function calls that should be returning range references, there's no way to respond with anything useful without knowing more details. If I'm right about the nature of the intermediate functions, your choices are limited to returning nothing when there are errors, so the final formula would look like =IF(ISNUMBER(STDEV(whatever)),STDEV(whatever),"") or trapping the #DIV/0! errors in the intermediate function calls. Maybe there's a better way to handle this, but you'd need to provide more details. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding #DIV/0! in further calculations
When I see #DIV/0! errors in my spreadsheets, I trap those errors, to avoid
problems such as yours. Assume I have the formula =A1/A2 and A2 can equal 0. I would trap the #DIV/0! error by modifying the formula: =IF(ISERROR(A1/A2),"",A1/A2). Then any formula which refers to this cell will not return #REF errors, because the cell wouldn't contain the #DIV/0! error. What happens, then, is your spreadsheets are much cleaner and more reliable. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "tom ossieur" wrote: Hi, I am doing some calculations in several steps. In some steps the result of some cells is #DIV/0! as a result the final result (STDEV) gives #REF! how to exclude the cells that contain #DIV/0! so I get the result in the last step, based on the cells that had a intermediate result? (without adapting ALL intermdiate steps with IF etc) so i am looking for a simple trick in the last step Thanks! tom |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding #DIV/0! in further calculations
Dave F wrote...
When I see #DIV/0! errors in my spreadsheets, I trap those errors, to avoid problems such as yours. Assume I have the formula =A1/A2 and A2 can equal 0. I would trap the #DIV/0! error by modifying the formula: =IF(ISERROR(A1/A2),"",A1/A2) .... But that traps any error in either cell A1 or cell A2. The only ways A1/A2 returns #DIV/0! is if either A1 or A2 already evaluates to #DIV/ 0! or A2 = 0. Usually a good idea to propagate UNEXPECTED errors and only trap EXPECTED errors, so better to use =IF(A2=0,"",A1/A2) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excluding #DIV/0! in further calculations
Fair point.
-- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Harlan Grove" wrote: Dave F wrote... When I see #DIV/0! errors in my spreadsheets, I trap those errors, to avoid problems such as yours. Assume I have the formula =A1/A2 and A2 can equal 0. I would trap the #DIV/0! error by modifying the formula: =IF(ISERROR(A1/A2),"",A1/A2) .... But that traps any error in either cell A1 or cell A2. The only ways A1/A2 returns #DIV/0! is if either A1 or A2 already evaluates to #DIV/ 0! or A2 = 0. Usually a good idea to propagate UNEXPECTED errors and only trap EXPECTED errors, so better to use =IF(A2=0,"",A1/A2) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excluding cells | Excel Worksheet Functions | |||
Fill Down Excluding First Row | Excel Discussion (Misc queries) | |||
excluding #N/A | Excel Discussion (Misc queries) | |||
AVERAGE excluding #N/A | Excel Worksheet Functions | |||
MIN excluding 0s | Excel Worksheet Functions |