=IF AND or OR

S

sumif and

I need some help with a formula. Here's my data:

Field F7 could be 50 or 60 (value)
Field F8 could be either Y or N (meaning yes or no)

If F7=50 and F8=y, then I need it to read "NA" for not applicable
If F7=50 and F8=n, then I need it to read " " blank
If F7=60 and F8=Y, then I need it to perform calculation of F5*F4
If F7=60 and F8=n, then I need it to read " " blank

So, only when F7 is 60 and F8 is Y do I want the formula to calculate. How
do I do this? I've tried =ifand statements and =ifor statements, but they
all keep coming up "false". Please help!
 
L

Lars-Åke Aspelin

=IF(F8="n","",IF(F7=50,"NA",F5*F4))


If you want to accept either "n" or "N" as no, you can modify this to

=IF(LOWER(F8)="n","",IF(F7=50,"NA",F5*F4))
or
=IF(LOWER(F8)="n"," ",IF(F7=50,"NA",F5*F4))
if you really want " " and not "" as the "blank" result.

Hope this helps / Lars-Åke
 
L

Luke M

BTW, XL does not actually distinguish between upper and lower case when using
IF formulas.
 
L

Lars-Åke Aspelin

BTW, XL does not actually distinguish between upper and lower case when using
IF formulas.

You are right. I should have checked that first.
Buy how would one do if one really wanted to distinguish between upper
an lower case? CODE() could be used for single letter strings, but
what about longer strings?

/ Lars-Åke
 
J

JE McGimpsey

Check out the EXACT() function.

Lars-Åke Aspelin said:
You are right. I should have checked that first.
Buy how would one do if one really wanted to distinguish between upper
an lower case? CODE() could be used for single letter strings, but
what about longer strings?

/ Lars-Åke
 
L

Lars-Åke Aspelin

You are right. I should have checked that first.
Buy how would one do if one really wanted to distinguish between upper
an lower case? CODE() could be used for single letter strings, but
what about longer strings?

/ Lars-Åke

I found it. EXACT() is the think to use in that case.

/ Lars-Åke
 
G

Glenn

Lars-Åke Aspelin said:
You are right. I should have checked that first.
Buy how would one do if one really wanted to distinguish between upper
an lower case? CODE() could be used for single letter strings, but
what about longer strings?

/ Lars-Åke

You would use FIND() or EXACT().
 
J

JBeaucaire

If you really have three status options, why complicate it by trying t
use a y/Y/n? Why not just use a DIFFERENT 3rd flag

y/n/na or y/n/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top