Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    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

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    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?

  3. #3
    Plutonium Lounger
    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.

  4. #4
    Silver Lounger
    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

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Plutonium Lounger
    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.)

  6. #6
    Silver Lounger
    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

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Silver Lounger
    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

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Plutonium Lounger
    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?

  9. #9
    Silver Lounger
    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
    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    Plutonium Lounger
    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>

  11. #11
    Silver Lounger
    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

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    Plutonium Lounger
    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?

  13. #13
    Silver Lounger
    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's
    If you are a fool at forty, you will always be a fool

  14. #14
    Plutonium Lounger
    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?

  15. #15
    Silver Lounger
    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
    Braddy
    If you are a fool at forty, you will always be a fool

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •