# Thread: IF(AND) formula ? (Excel 2002/2003)

1. ## IF(AND) formula ? (Excel 2002/2003)

Hi

I am struggling with this if and formula

=IF(AND(NSI="YES",Channel4="YES"),(AF10*\$AI\$5/0.5),IF(AND(NSI="No",Channel4="No",(AC10*\$AI\$5)))

What I am trying to say here is if NSI and Channel4 =Yes, do this - (AF10*\$AI\$5)/0.5, if NSI and Channel4 =No then do this - AC10*\$AI\$5)))

Regards

2. ## Re: IF(AND) formula ? (Excel 2002/2003)

And what if NSI is "Yes" and Channel4 is "No" or the other way round?

3. ## Re: IF(AND) formula ? (Excel 2002/2003)

BTW, this is the corrected version of your formula (added a missing parenthesis and removed some superfluous ones):

=IF(AND(NSI="YES",Channel4="YES"),AF10*\$AI\$5/0.5,IF(AND(NSI="No",Channel4="No"),AC10*\$AI\$5))

If NSI and Channel4 are not both Yes or both No, the formula will return FALSE.

4. ## Re: IF(AND) formula ? (Excel 2002/2003)

Hi Hans

Very good question,

=IF(AND(NSI="YES",Channel4="YES"),AF10*\$AI\$5/0.5,IF(AND(NSI="No",Channel4="No"),AC10*\$AI\$5))

if NSI and Channel4 both = YES then as per your formula.

If NSI = YES and Channel4 = NO the formula must be AF10*\$AI\$5/0.5

if NSI = No and Channel4 = either Yes or No the formula must be AC10*\$AI\$5

PS Don't bet any money on me getting this correct.

Regards

5. ## Re: IF(AND) formula ? (Excel 2002/2003)

According to your description, the value of Channel4 doesn't matter - the result should be the same whether Channel4 = Yes or Channel4 = No. So try this:
<code>
=\$AI\$5*IF(NSI="YES",2*AF10,AC10)
</code>
(Note: dividing by 0.5 is the same as multiplying with 2.)

6. ## Re: IF(AND) formula ? (Excel 2002/2003)

Hi Hans

Sorry it so long to reply, but I feel I haven't explained myself very well, I would be most obliged if I could come back to this after a bit more investigation.

Regards

7. ## Re: IF(AND) formula ? (Excel 2002/2003)

Hi Hans

I hope this is a bit clearer

IF NSI = Yes and Channel4 = No then AC10*\$AI\$5

IF NSI = Yes and Channel4 = Yes then AF10*\$AI\$5/0.5

If NSI = No and Channel4 = Yes then AC10*\$AI\$5

IF NSI = No and Channel4 =No then AC10*\$AI\$5

It looks like any combination is AC10*\$AI\$5 except if IF NSI = Yes and Channel4 = Yes

Brief explanation:

Channel4 are Market Sectors there are 15 in all, however only 4 of them are Food Sector, and it's only if a Food Sector is chosen aswell as NSI does the formula AF10*\$AI\$5/0.5 come into play.

So if NSI is Yes the only change will be if a Food Sector is chosen to go with it.

If they are both blank I need ""

Regards

8. ## Re: IF(AND) formula ? (Excel 2002/2003)

And if one of the two is blank but not the other?
And if at least one of the two has another value than "Yes", "No" or blank? Or is that impossible?

9. ## Re: IF(AND) formula ? (Excel 2002/2003)

Hi Hans

IF NSI is blank or contains anything other than YES then then AC10*\$AI\$5

Only IF NSI contains Yes and Channel4 = Yes would we use AF10*\$AI\$5/0.5

To Sum up:
Only IF NSI contains Yes and Channel4 = Yes would we use AF10*\$AI\$5/0.5 Otherwise all other combinations would be AC10*\$AI\$5

Regards

10. ## Re: IF(AND) formula ? (Excel 2002/2003)

Does this do what you want?
<code>
=\$AI\$5*IF(AND(NSI="Yes",Channel4="Yes"),2*AF10,AC1 0)</code>

11. ## Re: IF(AND) formula ? (Excel 2002/2003)

Hi Hans

Sorry I could not get back to you until today, I think you are almost there, except if channell4 = No and NSI is blank I get a #N/A instead of 2*AC10.

Every other combination should result in 2*AC10 other than IF NSI = Yes and Channel4 = Yes then is should be 2*AF10. Channel4 will always contain either yes or no

Regards

12. ## Re: IF(AND) formula ? (Excel 2002/2003)

The formula should only return #N/A if at least on of the cells AI5, AC10, AF10 contains #N/A. Does that always happen when Channel4 is "No" and NSI is blank?

13. ## Re: IF(AND) formula ? (Excel 2002/2003)

Hi Hans

Good Morning

It happens when Channel4 is "No" and NSI is blank or contains "No"

there are no other #N/A's

14. ## Re: IF(AND) formula ? (Excel 2002/2003)

That is impossible. Could you attach a sample workbook?

15. ## Re: IF(AND) formula ? (Excel 2002/2003)

Hi Hans

I fear I misled you again, I found the #N/A is was in the formula where it was decided if a Market Sector was Food or not I have now corrected this to show if No if there is an error.

I will let you know if it works to the satisfaction of the users.

Once again my apologies for wasting your time.

Regards