UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Problems Nesting Function Iferror, Office 2013    
 
   
bakersburg9
post Jul 23 2019, 12:13 PM
Post#1



Posts: 5,519
Joined: 2-November 04
From: Downey, CA


Previously, I had
CODE
strformulaGP = "=(B2-C2)/(B2)"


... but it was returning DIV/0! in some cases, which I didn't want to see
... so I changed it to:
CODE
strformulaGP = "=ISERROR((B2-C2)/(B2),"""
and it works to get rid of #DIV/0!

but I want to do the same with this line of code, and nothing I try works - I'm trying to fold in the IFERROR

CODE
strformulaBonusCalc = "=IF(A:A=1,IF(((B2-C2)/B2)>0.329,((B2-D2)*0.005)),IF(A:A=5,IF(((B2-C2)/B2)>0.329,((B2-D2)*0.005)),IF(A:A=4,IF(((B2-C2)/B2)>0.349,((B2-D2)*0.005)),IF(A:A=3,IF(((B2-C2)/B2)>0.349,((B2-D2)*0.005))))))"


also, I know I could google it, but what's the difference between IFERROR AND ISERROR ?

and... there's another problem I mentioned in another question I posted, that this works great to get rid of the #DIV/0, and just shows a blank cell, but I ALSO want to do that if there's a negative number - there is a percentage that's being calculated

EDIT: I just noticed that I intermingled "IFERROR" and "ISERROR" odd, because like I said, the macro worked - now I'm getting an error message - so strange - my code said ISERROR, and magically IFERROR was put in cell - and it worked, but now my code is not working
????

i'm getting
Run-time error '1004'
Application-defined or object-defined error

but my code should work ! Is this a 'buggy' thing ?

Thanks in advance,

Steve
This post has been edited by bakersburg9: Jul 23 2019, 12:25 PM
Go to the top of the page
 
strive4peace
post Jul 23 2019, 03:15 PM
Post#2


strive4peace
Posts: 20,447
Joined: 10-January 04



hi Steve,

I saw that thread ... or one of them?

Use IfError when you have a an expression that might result in an error, and, if so, supply another value.
=IFERROR( expression1, expression2)
The first parameter is the expression you want to return the value of. The second parameter is what you want to return if the first parameter has an error.

IsError tests to see if an error will happen and is True or False.
Wrap that with IF to test its return value -- if true, use expression1. if false, use expression2.
=IF( ISERROR( expression-to-test), expression1, expression2)

I like testing IsError because, it seems for me in the past, IfError didn't work in some cases -- could have been my expression wink.gif

> "want to do that if there's a negative number - there is a percentage that's being calculated"

turning the cell into text, not a number might impact other calculations. Might Zero (0) be better? A consideration anyway. I'm not sure what is better for array formulas.

~~~
what is the significance of 0.329? Rather than setting a value -- perhaps make it a reference? something like MyRate ...

~~~

anyhow, I think your formula might get errors if there aren't numbers in the cells unless you test for that or convert them. If so, put numbers in there to begin with ... like 0. And then, if 0 might be a problem so test for it in B2

or maybe in your formula, test for that, and don't do the the big equation if values aren't valid

"= if(B2=0 or B2="" , 0 , else your big equation)
or maybe
"= if( ISERROR( C2/B2 ) , expression1, expression2)
(what you test doesn't have to be what you calculate)

~~~
all that said ...
strformulaGP = "=ISERROR((B2-C2)/(B2),"""
looks wrong. There is no closing parenthesis, and inside a string delimited with double quotes, you need 2 each time you want to use one, and then you need an expression to evaluate if there isn't an error. Seems that it should be something like this:
strformulaGP = "=IF( ISERROR( (B2-C2)/B2 ),"""", (B2-C2)/B2)"

> "and it works to get rid of #DIV/0!"
does it calculate in other places?
~~~

make sure you set the FormulaArray property for the cell when you have an array formula

~~~
still have the scarf, thanks ~


This post has been edited by strive4peace: Jul 23 2019, 03:46 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
WildBird
post Jul 23 2019, 04:21 PM
Post#3


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


Anything with more than 1 or 2 IF statements, I would put into a function. Much easier to read and maintain.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
strive4peace
post Jul 23 2019, 06:02 PM
Post#4


strive4peace
Posts: 20,447
Joined: 10-January 04



true, Coop, but performance is often better if everything is in the cell equation ~

--------------------
have an awesome day,
crystal
Go to the top of the page
 
bakersburg9
post Jul 23 2019, 06:07 PM
Post#5



Posts: 5,519
Joined: 2-November 04
From: Downey, CA


Great to hear from you ! I'm heading out right now, but printed it off - I may send you a PM if that's ok - I'll tell my wife you have the scarf

Thanks again, and WELCOME BACK !!!!! I'm forever in your debt... cool.gif

Steve
Go to the top of the page
 
strive4peace
post Jul 25 2019, 03:02 PM
Post#6


strive4peace
Posts: 20,447
Joined: 10-January 04



hi Steve,

thanks, nice to see you smile.gif I hope it helps. ... and, as you know, I care about you getting the logic ... and speaking of that, what exactly are you trying to do with that long formula? Perhaps there is a better way.

Please post your questions here so that others can benefit too ~ thank you, Steve



--------------------
have an awesome day,
crystal
Go to the top of the page
 
WildBird
post Jul 25 2019, 08:33 PM
Post#7


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


QUOTE
true, Coop, but performance is often better if everything is in the cell equation ~


Agree, but often the performance is minimal, and it is usually much easier to maintain code where you can have descriptions and explain logic etc, as opposed to having a formula that is hard to maintain or modify etc.

I personally generally go for the function, unless performance is severely impacted (usually if there is a very large number of rows for example), then I would look at a formula.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th August 2019 - 06:47 AM