IF

[Connie Martin]

I have this formula in one cell, dragged down through more than 800 rows and
it's working fine: =IF(G7<=15,"2 WKS",""). But, for the life of me, I can't
come up with the next one I want, which is IF G7 is more than 14 but less
than 22, return "3 WKS". I have checked online help and there's lots of help
there, but can't find this particular one. Connie

 

[Jacob Skaria]

Try the below..

=IF(G7<=15,"2 WKS",IF(G7>15,"3 WKS",""))

'Handle blank entries in G7...
=IF(G7,IF(G7<=15,"2 WKS",IF(G7>15,"3 WKS","")),"")


If there are more comditions there are better ways to handle this..

 

[Glenn]

I have this formula in one cell, dragged down through more than 800 rows and
it's working fine: =IF(G7<=15,"2 WKS",""). But, for the life of me, I can't
come up with the next one I want, which is IF G7 is more than 14 but less
than 22, return "3 WKS". I have checked online help and there's lots of help
there, but can't find this particular one. Connie

Your ranges don't exactly work...what do you want if G7=15? In your first
statement, G7<=15 would make the result "2 WKS", but in your second request,
G7>14 would make the result "3 WKS".

I would suggest that something like this would probably work, if you clarified
exactly what you want:

=MAX(INT((G7-1)/7)+1,2)&" WKS"

 

[Connie Martin]

Thank you, Jacob, but what about incorporating "less than 22"?
IF(G7>15...this will capture every number above 15, which will mean, if I
understand correctly, it will include 37, for example. Also, >15....would
that exclude 15, which I do want to include? In a nutshell, for the "3 WKS",
I want to include only numbers from and including 15-21, therefore more than
14 but less than 22. Connie

 

[Tom Hutchins]

Try

=IF(G7,IF(G7<15,"2 WKS",IF(AND(G7>=15,G7<22),"3 WKS","")),"")

It wasn't clear from your original post whether 15 should return 2 WKS or 3
WKS (it is <=15 but also >14).

Hope this helps,

Hutch

 

[Eduardo]

Hi,
=if(or(G7>14,G7<=22),"3Wks",if(G7<=14,"2Wks",""))

 

[Connie Martin]

I found what I'm looking for, which is: =IF(AND(G14>14,G14<22),"3 WKS","").
I found it here: http://www.techonthenet.com/excel/formulas/if.php, third
question down is very similar. Just needed a little tweaking. Connie

 

[David Biddulph]

=IF(G7<15,"2 WKS",IF(G7<22,"3 WKS","")) ?

 

[David Biddulph]

Why have you got (AND(G7>=15, when you've already tested for G7<15, Tom ?

Wouldn't
=IF(G7,IF(G7<15,"2 WKS",IF(AND(G7>=15,G7<22),"3 WKS","")),"")
be the same as
=IF(G7,IF(G7<15,"2 WKS",IF(G7<22,"3 WKS","")),"") ?

 

[David Biddulph]

Three questions, Eduardo:

If you've tested for G7>14, why do you need G7<=14 subsequently?
You have a test for OR(G7>14,G7<=22); for what value of G7 would that be
anything other than TRUE?
Did you mean AND rather than OR?

 

[Connie Martin]

Thank you for responding, Tom. I'm sorry...I guess everyone found my
question "unclear" and I've re-read it and it seems clear to me. Anyway, I
did get the answer I needed. Thank you so much. Will try to be clearer the
next time. Connie

 

[Connie Martin]

This gives me 3 WKS for every single row. I guess, by the sounds of what
others wrote, I wasn't clear enough in what I wanted. See my other post in
the string. I did find what I was looking for finally. Thank you for
responding. Connie

 

[Connie Martin]

That's exactly it! Thank you. I was actually putting this in two separate
columns....that is, the 2 WKS formula in one and the 3 WKS formula in
another, but combined or separate, this works. Thank you very much. Connie

 

[Rick Rothstein]

The confusion comes from your two conditions. Your first says G7<=15 (less
than or EQUAL TO) and your second says "G7 is more than 14"... both of these
conditions include the number 15 in them so it is unclear if you want 15 to
return "2 WKS" or "3 WKS".

 

[Connie Martin]

I'm sorry if I wasn't too clear. I always try to be clear and precise when I
post here and I thought I was. Anyway, your formula works, except for it
adds "4 WKS" to numbers over 22, but how were you to know exactly what I
wanted?!! Sorry for the confusion. Perhaps I was a little too skimpy on my
info. Thanks again. Connie

 

[David Biddulph]

Glad it helped.
--
David Biddulph

That's exactly it! Thank you. I was actually putting this in two
separate
columns....that is, the 2 WKS formula in one and the 3 WKS formula in
another, but combined or separate, this works. Thank you very much.
Connie