Dividing by zero
Marcelo had a typo in his formula.
=if(and(i19=0,k19=0),0,if(i19=0,-0,15,MAX(MIN(((I19-K19)/I19),0.15),-0.15)))
should have been
=if(and(i19=0,k19=0),0,if(i19=0,-0.15,MAX(MIN(((I19-K19)/I19),0.15),-0.15)))
An alternative way of expressing Marcelo's formula is
=IF(AND(I19=0,K19=0),0,IF(I19=0,-0.15,MEDIAN(-0.15,(I19-K19)/I19,0.15)))
Another alternative is
=IF(I19=0,IF(K19=0,0,-0.15),MEDIAN(-0.15,(I19-K19)/I19,0.15))
--
David Biddulph
"jvega" wrote in message
...
Marcelo,
It says "You've entered too many arguments for this function." I checked
and
it's not formatted as text, what do you think it could be?
Thank you
"Marcelo" wrote:
I tested here and it works, take a look if the cell is formated as text.
hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo
"jvega" escreveu:
Marcelo,
I couldn't get it to work, I copied and pasted your formula, and when I
pressed enter, it just stayed the same, as if it was just text, and yes
I did
include the "=" without I7.
Thanks
"Marcelo" wrote:
sorry..
I7=if(and(i19=0,k19=0),0,if(i19=0,-0,15,MAX(MIN(((I19-K19)/I19),0.15),-0.15)))
Now correct.
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo
"jvega" escreveu:
Hello,
I have this formula: I7=MAX(MIN(((I19-K19)/I19),0.15),-0.15), where
I want
the range to be from -0.15 to 0.15.
However, when I19=0, I obviously get the message that I can't
divide by zero.
So, how would I change I7 so that when I19=0, I7 will equal -.15,
BUT, when I19=0, AND K19=0, I7will equal 0.
Thanks for you help, it is much appreciated,
Regards,
JV
|