Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
Hello,
could you please show me how I can get rid of #DIV/0 with iserror my formula is =IF(L12=0,"",(G12/K12)/L12) much appreciated Ditchy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
Hi,
Am Thu, 10 Oct 2013 22:59:57 -0700 (PDT) schrieb : could you please show me how I can get rid of #DIV/0 with iserror my formula is =IF(L12=0,"",(G12/K12)/L12) try: =IF(OR(L12=0,K12=0),"",G12/K12/L12) or: =IFERROR(IF(L12=0,"",G12/K12/L12),"") Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
=IF(ISERROR((G12/K12)/L12,"",(G12/K12)/L12)
wrote in message ... Hello, could you please show me how I can get rid of #DIV/0 with iserror my formula is =IF(L12=0,"",(G12/K12)/L12) much appreciated Ditchy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
wrote:
could you please show me how I can get rid of #DIV/0 with iserror my formula is =IF(L12=0,"",(G12/K12)/L12) If you just want to avoid the #DIV/0 error: =IF(K12*L12=0,"",G12/K12/L12) Or if you do not require Excel 2003 compatibility: =IFERROR(G12/K12/L12,"") But if you require Excel 2003 compatibility or your assignment requires the use of ISERROR: =IF(ISERROR(G12/K12/L12),"",G12/K12/L12) The exercise demonstrates the issue with ISERROR and why IFERROR was born: ISERROR calculates the expression twice if there is no error. Not a big deal for your expression. But the cause of many performance issues when the expression involves long look-ups, for example. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
On Friday, October 11, 2013 5:09:44 PM UTC+11, Claus Busch wrote:
Hi, Am Thu, 10 Oct 2013 22:59:57 -0700 (PDT) schrieb could you please show me how I can get rid of #DIV/0 with iserror my formula is =IF(L12=0,"",(G12/K12)/L12) try: =IF(OR(L12=0,K12=0),"",G12/K12/L12) or: =IFERROR(IF(L12=0,"",G12/K12/L12),"") Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Thank You Claus much appreciated, all fixed |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
On Friday, October 11, 2013 5:20:46 PM UTC+11, Charlotte E. wrote:
=IF(ISERROR((G12/K12)/L12,"",(G12/K12)/L12) Thank You Charlotte much appreciated, all fixed Hello, could you please show me how I can get rid of #DIV/0 with iserror my formula is =IF(L12=0,"",(G12/K12)/L12) much appreciated Ditchy |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
On Friday, October 11, 2013 5:36:32 PM UTC+11, joeu2004 wrote:
Thank You joeu2004 much appreciated, all fixed could you please show me how I can get rid of #DIV/0 with iserror my formula is =IF(L12=0,"",(G12/K12)/L12) If you just want to avoid the #DIV/0 error: =IF(K12*L12=0,"",G12/K12/L12) Or if you do not require Excel 2003 compatibility: =IFERROR(G12/K12/L12,"") But if you require Excel 2003 compatibility or your assignment requires the use of ISERROR: =IF(ISERROR(G12/K12/L12),"",G12/K12/L12) The exercise demonstrates the issue with ISERROR and why IFERROR was born: ISERROR calculates the expression twice if there is no error. Not a big deal for your expression. But the cause of many performance issues when the expression involves long look-ups, for example. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
Hi,
Am Fri, 11 Oct 2013 01:43:52 -0700 (PDT) schrieb : much appreciated, all fixed have a look for Joe's suggestion. That is the better way to do it. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
On Friday, October 11, 2013 7:47:12 PM UTC+11, Claus Busch wrote:
Hi, Thanks Claus, I have another small problem with this array coming up with this #DIV/0 {=AVERAGE(IF(L9:L23<0,L9:L23))} would you have any suggestions how it can be fixed? thank you Ditchy Am Fri, 11 Oct 2013 01:43:52 -0700 (PDT) schrieb much appreciated, all fixed have a look for Joe's suggestion. That is the better way to do it. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
wrote:
I have another small problem with this array coming up with this #DIV/0 {=AVERAGE(IF(L9:L23<0,L9:L23))} Did you ever whether you are using Excel 2003/earlier or Excel 2007/later? Or if you require Excel 2003/earlier compatibility, nevertheless? It might save us a lot of time and space if you would tell us that. Sorry if I overlooked it. If you do not require Excel 2003/eariler compatibility, the simplest solution is the following normally-entered formula (just press Enter): =IFERROR(AVERAGEIF(L9:L23,"<0"),"") If you require Excel 2003/earlier compatiblity, array-enter the following (press ctrl+shift+Enter instead of just Enter): =IF(COUNTIF(L9:L23,"<0")=0,"",AVERAGE(IF(L9:L23< 0,L9:L23))) |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror problem
On Saturday, October 12, 2013 12:34:05 PM UTC+11, joeu2004 wrote:
Hi Joeu2004 thank you for solving my array problem, without people like you that help out novices like me, we could be fumbling around for days trying to solve these issues. You an everyone else that helps on these sites does make Excel easier and more rewarding to learn. Much appreciated Ditchy Ballarat Australia I have another small problem with this array coming up with this #DIV/0 {=AVERAGE(IF(L9:L23<0,L9:L23))} Did you ever whether you are using Excel 2003/earlier or Excel 2007/later? Or if you require Excel 2003/earlier compatibility, nevertheless? It might save us a lot of time and space if you would tell us that. Sorry if I overlooked it. If you do not require Excel 2003/eariler compatibility, the simplest solution is the following normally-entered formula (just press Enter): =IFERROR(AVERAGEIF(L9:L23,"<0"),"") If you require Excel 2003/earlier compatiblity, array-enter the following (press ctrl+shift+Enter instead of just Enter): =IF(COUNTIF(L9:L23,"<0")=0,"",AVERAGE(IF(L9:L23< 0,L9:L23))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If IsError Help | Excel Discussion (Misc queries) | |||
IF(ISERROR problem | Excel Discussion (Misc queries) | |||
if iserror then value = zero problem | Excel Programming | |||
Help with ISERROR | Excel Worksheet Functions | |||
iserror problem | Excel Discussion (Misc queries) |