Hi everybody,
I've been trying to rewrite
=IF(ISERROR((IF(AND(F45>T45*(1-$B$11);T45<>"");1;0)));"";(IF(AND(F45>T45*(1-$B$11);T45<>"");1;0)))
into something much more simpler
=IFERROR(AND(F45>T45*(1-$B$11);T45<>"")*1;"")
I seem to have an error because its prompting me to multiply it by 1 my "value"
Eseentially, the formula should be IF NO-ERROR + (F45>T45*(1-$B$11);T45<>"") are TRUE = 1, IF YES-ERROR =""
Could someone point out where I my formula is wrong??
Thx!
Hi,
It would be this:
=IFERROR((IF(AND(F45>T45*(1-$B$11);T45<>"");1;0));"")
Try this:
=IFERROR(IF(AND(F45>T45*(1-$B$11);T45<>"");1;0);"")
Excel is a constant learning process and it's great to help each other. If any of us have helped you today, a click on the "reputation" star on the left is appreciated.
Thx for the quick reply
Last edited by Exequiel3k; 06-28-2016 at 06:12 PM.
This is more of an inquiry:
I switched all my ISERROR to IFERROR and the data processing actually took 8% more time to complet (did it twice to be sure).
I assumed there was less re-calculations to be done via IFERROR since ISERROR wasn't processing the formula twice..
Is there something that makes it longer to process as it stands "=IFERROR(IF(AND(F45>T45*(1-$B$11);T45<>"");1;0);"")"???
Last edited by Exequiel3k; 06-29-2016 at 01:07 PM.
Does this not do the same so no IFERROR ????
=IF(T45="","",IF(F45>T45*(1-$B$11),1,0))
or
=IF(T45="",0,IF(F45>T45*(1-$B$11),1,0))
The error would only result if F45, T45 or B11 were not numeric. Is this likely ?
Hmmmm... have never really come across this issue before, but I did a bit of research and found this item (a bit old now, but maybe still relevant if you're using 2013 version)
http://www.mrexcel.com/forum/excel-q...fficiency.html
It suggests that IFERROR can be slow if used on large data sets, so if you have a large worksheet this could apply.
As a suggestion, could you evaluate what type of errors you would get (e.g. T45 containing something invalid) and evaluate those in your formula instead of error checking everything? I must admit that I tend to use IFERROR mostly only VLOOKUPS to check for missing values and avoid #N/A but try to avoid the errors on other things where possible.
John's alternative suggestion is the kind of thing I mean
I'll rewrite the columns and report back using =IF(XX="","",IF(XXX>XXX*(1-$B$11),1,0))
The only error I could get is #value; I could always try ISERR but John`s approach seems promising
John's formula seems the way to go, however in your specific formula the source of the #Value! error could be either T45 OR B11 being a text value ("")
Try
=IF(OR(T45="",$B$11=""),"",IF(F45>T45*(1-$B$11),1,0))
Having changed all the IFERROR TO "=IF(XX="","",IF(XXX>XXX*(1-$B$11),1,0))", it was 2.30% slower than ISERROR.
Essentially, ISERROR faster than "No-Error formula" faster than IFERROR.
Intresting outcome.. Any thoughts??
(btw I have a i7 3770 with 8GB ram).
Last edited by Exequiel3k; 06-30-2016 at 08:19 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks