Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How To return 0 if error
I am using an array formula that looks like this: {=AVERAGE(IF('Drop'!$A$2:$A$65563='CE OHIO'!B3,'Drop'!$AC$2:$AC65536))} The problem is if there is no information for this formula to reference then the result is #DIV/0! This is rather ugly on a spreadsheet. Does anyone know how to return 0 if there is an error like this. Thank you in advance for any help -- rmeister ------------------------------------------------------------------------ rmeister's Profile: http://www.excelforum.com/member.php...o&userid=30163 View this thread: http://www.excelforum.com/showthread...hreadid=505321 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How To return 0 if error
{=IF(ISERROR(AVERAGE(IF('Drop'!$A$2:$A$65563='CE OHIO'!B3,'Drop'!$AC$2:$AC65536))),0,AVERAGE(IF('Dr op'!$A$2:$A$65563='CE OHIO'!B3,'Drop'!$AC$2:$AC65536)))} Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=505321 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How To return 0 if error
Does
anyone know how to return 0 if there is an error like this. "yourformula" gives an error. use a structure like this to prevent any error: =if(iserror(yourformula),"your error value",yourformula) if you want to check only special errors use instead of ISERROR eg. ISNV for lookups, there are also other IS-functions, see online help for details. There's no way around this - this is the workaround. arno |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How To return 0 if error
You can make the formula length a bit more manageable by testing the
condition that will cause the error instead of repeating the whole formula. like: =if(sum("data range"=0,"your error value",yourformula) where "data range" is the data being averaged. I find this helps with long formulas using e.g. nested ifs lookups and the like that end up using loads of brackets. Giz "arno" wrote: Does anyone know how to return 0 if there is an error like this. "yourformula" gives an error. use a structure like this to prevent any error: =if(iserror(yourformula),"your error value",yourformula) if you want to check only special errors use instead of ISERROR eg. ISNV for lookups, there are also other IS-functions, see online help for details. There's no way around this - this is the workaround. arno |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How To return 0 if error
=if(sum("data range"=0,"your error value",yourformula)
?? pls. explain with =if(iserror(hlookup(a1,myrange,2,false)),0,hlookup (a1,myrange,2,false)) or =if(iserror(a1/b1),0,a1/b1) where b1 can be 0, #N/V, #Div0 or #Name arno |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How To return 0 if error
If you have a lot of these you may find it useful to create a VBA function to test and return the result eg Function myClean(ByRef rng As Range) As Variant If IsError(rng.Value) Then myClean = 0 Else myClean = rng.Value End If End Function It has the advantage that you never have two copies of the formula with potential for typos in one and makes it easier to read. -- tony h ------------------------------------------------------------------------ tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074 View this thread: http://www.excelforum.com/showthread...hreadid=505321 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How To return 0 if error
Fair point Arno, should probably have been clearer and a missing bracket
doesn't help :( =if(sum("data range")=0,"your error value",yourformula) In this case the error is caused by having no data in the "data range" so by just checking for the existence of data to average it is possible to reduce the length of the formula. My suggestion is to check for the cause of the error at the basest level and deal with it there. I have inherited some horrendous formulas that cover 4-5 rows on the screen and 60% of it can be removed by a simple error check like the one above. It will be different cases as with your lookup example. In your second example I could go back to the formula being used in B1 and deal with the cause of the error there. But the example is so short it probably wouldn't be worth the effort. But faced with (simplified): =IF(ISERROR(IF((IF(($B$1=CM$7),VLOOKUP($A27,'[Flows Data Sheet.xls]1105 Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41)))<0,0,(IF(($B$1=CM$7),VLOOKUP($A2 7,'[Flows Data Sheet.xls]1105 Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41))))),0,(IF((IF(($B$1=CM$7),VLOOKUP ($A27,'[Flows Data Sheet.xls]1105 Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41)))<0,0,(IF(($B$1=CM$7),VLOOKUP($A2 7,'[Flows Data Sheet.xls]1105 Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41)))))) Which can be reduced to: =IF(ISNA(VLOOKUP($A27,'[Flows Data Sheet.xls]1105 Inv'!$A$4:$HH$1000,'TS'!CM$8,0)),0,(IF((IF(($B$1= CM$7),VLOOKUP($A27,'[Flows Data Sheet.xls]1105 Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41)))<0,0,(IF(($B$1=CM$7),VLOOKUP($A2 7,'[Flows Data Sheet.xls]1105 Inv'!$A$4:$HH$1000,'TS'!CM$8,0),(CM36-CM31+CM40+CM41))))) by simply testing the root of an error, you can why I suggest that approach. Giz "arno" wrote: =if(sum("data range"=0,"your error value",yourformula) ?? pls. explain with =if(iserror(hlookup(a1,myrange,2,false)),0,hlookup (a1,myrange,2,false)) or =if(iserror(a1/b1),0,a1/b1) where b1 can be 0, #N/V, #Div0 or #Name arno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handler Not Working | Excel Discussion (Misc queries) | |||
Huge problem with "if" formula's | Excel Discussion (Misc queries) | |||
Excell - How do I "force" cell to be filled or return error mge. | Excel Worksheet Functions | |||
IF formulas--please help!! | Excel Worksheet Functions | |||
#REF error | Excel Worksheet Functions |