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!

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

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"sumif and" wrote:

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!

On Mon, 5 Jan 2009 12:17:01 -0800, Luke M
wrote:

=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

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

Luke M
*Remember to click "yes" if this post helped you!*


"Lars-Ã…ke Aspelin" wrote:

On Mon, 5 Jan 2009 12:17:01 -0800, Luke M
wrote:

=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

On Mon, 5 Jan 2009 12:44:03 -0800, Luke M
wrote:

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

Check out the EXACT() function.

In article ,
Lars-Åke Aspelin wrote:

On Mon, 5 Jan 2009 12:44:03 -0800, Luke M
wrote:

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

On Mon, 05 Jan 2009 20:58:36 GMT, Lars-Åke Aspelin
wrote:

On Mon, 5 Jan 2009 12:44:03 -0800, Luke M
wrote:

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

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

/ Lars-Åke

Lars-Åke Aspelin wrote:
On Mon, 5 Jan 2009 12:44:03 -0800, Luke M
wrote:

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

You would use FIND() or EXACT().

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

y/n/na or y/n/x


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46728