ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Dividing by zero (https://www.excelbanter.com/excel-worksheet-functions/190579-dividing-zero.html)

jvega
Dividing by zero
 
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

Rick Rothstein \(MVP - VB\)[_640_]
Dividing by zero
 
Try this formula instead of the one you posted...

=IF(I19=0,-0.15,MAX(MIN(((I19-K19)/I19),0.15),-0.15))

Rick


"jvega" wrote in message
...
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


Marcelo
Dividing by zero
 
I7=if(i19=0,-0,15,MAX(MIN(((I19-K19)/I19),0.15),-0.15))
--
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

Marcelo
Dividing by zero
 
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

jvega
Dividing by zero
 
Ok, I put your formula in, but when I19 and K19 are both 0, it equals -.15
still. How would I make it equal 0?

Thanks

"Rick Rothstein (MVP - VB)" wrote:

Try this formula instead of the one you posted...

=IF(I19=0,-0.15,MAX(MIN(((I19-K19)/I19),0.15),-0.15))

Rick


"jvega" wrote in message
...
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



jvega
Dividing by zero
 
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

Marcelo
Dividing by zero
 
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

Rick Rothstein \(MVP - VB\)[_641_]
Dividing by zero
 
Perhaps this does what you want...

=IF(I19=0,IF(K19=0,0,-0.15),MAX(MIN(((I19-K19)/I19),0.15),-0.15))

Rick


"jvega" wrote in message
...
Ok, I put your formula in, but when I19 and K19 are both 0, it equals -.15
still. How would I make it equal 0?

Thanks

"Rick Rothstein (MVP - VB)" wrote:

Try this formula instead of the one you posted...

=IF(I19=0,-0.15,MAX(MIN(((I19-K19)/I19),0.15),-0.15))

Rick


"jvega" wrote in message
...
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

Dave
Dividing by zero
 
Hi,
Try:
=IF(AND(I19=0,K19=0),0,IF(I19=0,0.15,MAX(MIN(((I19-K19)/I19),0.15),-0.15)))

Regards - Dave.

jvega
Dividing by zero
 
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

Dave
Dividing by zero
 
Sorry, that should be:
=IF(AND(I19=0,K19=0),0,IF(I19=0,-0.15,MAX(MIN(((I19-K19)/I19),0.15),-0.15)))

"Dave" wrote:

Hi,
Try:
=IF(AND(I19=0,K19=0),0,IF(I19=0,0.15,MAX(MIN(((I19-K19)/I19),0.15),-0.15)))

Regards - Dave.

David Biddulph[_2_]
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



jvega
Dividing by zero
 
Dave,
Awesome, thanks for your help, it works now

Regards,
Josh

"Dave" wrote:

Sorry, that should be:
=IF(AND(I19=0,K19=0),0,IF(I19=0,-0.15,MAX(MIN(((I19-K19)/I19),0.15),-0.15)))

"Dave" wrote:

Hi,
Try:
=IF(AND(I19=0,K19=0),0,IF(I19=0,0.15,MAX(MIN(((I19-K19)/I19),0.15),-0.15)))

Regards - Dave.

Dave
Dividing by zero
 
Glad to help. Thanks for the feedback.
Dave.


All times are GMT +1. The time now is 09:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com