IF? (so close yet so far)

Deirdre

Board Regular
Joined
Feb 28, 2002
Messages
137
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top