Results 1 to 15 of 16

20070620, 10:40 #1
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
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
BraddyIf you are a fool at forty, you will always be a fool

20070620, 11:10 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: IF(AND) formula ? (Excel 2002/2003)
And what if NSI is "Yes" and Channel4 is "No" or the other way round?

20070620, 11:14 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.

20070620, 12:25 #4
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
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
BraddyIf you are a fool at forty, you will always be a fool

20070620, 13:36 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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.)

20070620, 15:25 #6
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
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
BraddyIf you are a fool at forty, you will always be a fool

20070620, 15:53 #7
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
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
BraddyIf you are a fool at forty, you will always be a fool

20070620, 16:20 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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?

20070620, 17:42 #9
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
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
BraddyIf you are a fool at forty, you will always be a fool

20070620, 17:54 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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>

20070621, 06:12 #11
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
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
BraddyIf you are a fool at forty, you will always be a fool

20070621, 07:12 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
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?

20070621, 07:15 #13
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
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'sIf you are a fool at forty, you will always be a fool

20070621, 07:17 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: IF(AND) formula ? (Excel 2002/2003)
That is impossible. Could you attach a sample workbook?

20070621, 07:27 #15
 Join Date
 Dec 2000
 Location
 Northampton, Northamptonshire, England
 Posts
 1,951
 Thanks
 2
 Thanked 1 Time in 1 Post
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
BraddyIf you are a fool at forty, you will always be a fool