IF? (so close yet so far)

Deirdre · Mar 14th, 2002, 11:54 AM

Here is the formula my scatterbrained melon came up with:
=IF(((V17=0)*(N17>=1)),SUM(V17/SUM(N17-P17)),"No P2s")

It's not working, of course.

I would like for Y17 to check the value of V17 and N17. *IF* V = 0 AND N17 >=1, then I wish for v17/sum(N17-P17). If V17 = 0 AND N17< 1 then I would like it to return the text of "No P2s".

:: chomps on pencil ::

Your guidance is appreciated.
~Deirdre

ceelypa · Mar 14th, 2002, 12:05 PM

Here's what you need, although all this will return is either the value 0 (as zero divided by anything is zero) or "No P2s".

=IF(AND(V17=0,N17>=1),V17/SUM(N17-P17),"No P2s")

A simpler formula would be:
=IF(V17=0,0,"No P2s")

Hope this helps!

Al Chara · Mar 14th, 2002, 12:06 PM

Try the code below:
=IF(V17=0,IF(N17>=1,V17/SUM(N17-P17),"No P2s"),"")

You did not specify what you want if v17 does equal 0. You can put any text inside the "" that you want displayed.

I hope this helps.
Kind regards, Al

Dave Patton · Mar 14th, 2002, 12:12 PM

try

=IF(AND(V17=0,N17>=1),V17/(N17-P17),"No P2s")

but this will yield 0 since V17=0

so
=IF(AND(V17=0,N17>=1),0,"No P2s")

Did I read something incorrectly or is
some information missing?

Aladin Akyurek · Mar 14th, 2002, 12:13 PM

Quote:

On 2002-03-14 10:54, Deirdre wrote:
Here is the formula my scatterbrained melon came up with:
=IF(((V17=0)*(N17>=1)),SUM(V17/SUM(N17-P17)),"No P2s")

It's not working, of course.

I would like for Y17 to check the value of V17 and N17. *IF* V = 0 AND N17 >=1, then I wish for v17/sum(N17-P17). If V17 = 0 AND N17< 1 then I would like it to return the text of "No P2s".

:: chomps on pencil ::

Your guidance is appreciated.
~Deirdre

Almost there...

=IF((V17=0)*(N17>=1),V17/(N17-P17),"No P2s")

This will return "No P2s" when V17 is not zero and N17 is less than 1.

Aladin

Dave Patton · Mar 14th, 2002, 12:17 PM

Deirdre · Mar 14th, 2002, 12:24 PM

Thank you everyone for your inout and time. I tried them all and still not getting to what I want. Let me try to illustrate, I could very well be doing this the difficult way...

In retrospect, I see I failed to fully explain my dilema. Please accept my apologies.

N17 = 7
V17 = 4
When this occurs, I would like v17/(N17-P17).

N17 = 7
V17 = 0
When this occurs, I would like for it to return "No P2s"

N17 = 0
V17 = 0
When this occurs, I would like for it to return blank or 0 (doesn't matter)

Steve Hartman · Mar 14th, 2002, 12:30 PM

Are these the only possible values for V17 and N17?

Deirdre · Mar 14th, 2002, 12:31 PM

Quote:

Are these the only possible values for V17 and N17?

No

Steve Hartman · Mar 14th, 2002, 12:35 PM

Then are these the only values you are interested in looking at? what do want when N17 is greater than 7? When N17 is between 7 and 0? When n17 is less than 0? You need to provide more info please if you want a workable solution.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 11:54 AM	#1
Deirdre Board Regular Join Date: Feb 2002 Location: New England, USA Posts: 115	Here is the formula my scatterbrained melon came up with: =IF(((V17=0)(N17>=1)),SUM(V17/SUM(N17-P17)),"No P2s") It's not working, of course. I would like for Y17 to check the value of V17 and N17. IF* V = 0 AND N17 >=1, then I wish for v17/sum(N17-P17). If V17 = 0 AND N17< 1 then I would like it to return the text of "No P2s". :: chomps on pencil :: Your guidance is appreciated. ~Deirdre

Mar 14th, 2002, 12:05 PM	#2
ceelypa New Member Join Date: Mar 2002 Posts: 6	Here's what you need, although all this will return is either the value 0 (as zero divided by anything is zero) or "No P2s". =IF(AND(V17=0,N17>=1),V17/SUM(N17-P17),"No P2s") A simpler formula would be: =IF(V17=0,0,"No P2s") Hope this helps!

Mar 14th, 2002, 12:06 PM	#3
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Try the code below: =IF(V17=0,IF(N17>=1,V17/SUM(N17-P17),"No P2s"),"") You did not specify what you want if v17 does equal 0. You can put any text inside the "" that you want displayed. I hope this helps. Kind regards, Al

Mar 14th, 2002, 12:12 PM	#4
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	try =IF(AND(V17=0,N17>=1),V17/(N17-P17),"No P2s") but this will yield 0 since V17=0 so =IF(AND(V17=0,N17>=1),0,"No P2s") Did I read something incorrectly or is some information missing?

Mar 14th, 2002, 12:17 PM	#6
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065

Mar 14th, 2002, 12:24 PM	#7
Deirdre Board Regular Join Date: Feb 2002 Location: New England, USA Posts: 115	Thank you everyone for your inout and time. I tried them all and still not getting to what I want. Let me try to illustrate, I could very well be doing this the difficult way... In retrospect, I see I failed to fully explain my dilema. Please accept my apologies. N17 = 7 V17 = 4 When this occurs, I would like v17/(N17-P17). N17 = 7 V17 = 0 When this occurs, I would like for it to return "No P2s" N17 = 0 V17 = 0 When this occurs, I would like for it to return blank or 0 (doesn't matter)

Mar 14th, 2002, 12:30 PM	#8
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	Are these the only possible values for V17 and N17?

Mar 14th, 2002, 12:35 PM	#10
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	Then are these the only values you are interested in looking at? what do want when N17 is greater than 7? When N17 is between 7 and 0? When n17 is less than 0? You need to provide more info please if you want a workable solution.