+ Reply to Thread
Results 1 to 21 of 21

IF, AND, OR formula confusing

  1. #1
    Registered User
    Join Date
    11-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Question IF, AND, OR formula confusing

    Hi,

    Is it possible to have a formula with all this in?

    If A1=P AND B1=X then C1+2
    If A1=P AND B1=Y then C1+3
    If A1=P AND B1=Z then C1+5

    If A1=Q AND B1=X then C1+1
    If A1=Q AND B1=Y then C1+ 2
    If A1=Q AND B1=Z then C1+3

    If A1="" then "" (this is meant to be if A1 is empty then my cell should be empty too)

    I have tried all sorts of combinations but it just gets too long and confusing and I come up with an error.

  2. #2
    Forum Contributor dogberry's Avatar
    Join Date
    07-15-2012
    Location
    Wales, UK
    MS-Off Ver
    Excel 2010
    Posts
    624

    Re: IF, AND, OR formula confusing

    Hi

    You will get a much better reponse if you post a worksheet with what you have and what you would like to achieve.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.



    Chris
    Click my star if I helped Thanks

  3. #3
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: IF, AND, OR formula confusing

    try this I tested it and worked!!

    =IF(C1<>"",IF(AND(A1="P",B1="X"),2,IF(AND(A1="P",B1="Y"),3,IF(AND(A1="P",B1="Z"),5,IF(AND(A1="Q",B1="X"),1,IF(AND(A1="Q",B1="Y"),2,IF(AND(A1="Q",B1="Z"),3,0)))))),"")

  4. #4
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: IF, AND, OR formula confusing

    A different method

    =IF(AND(OR(A1={"P","Q"}),OR(B1={"X","Y","Z"})),C1+HLOOKUP(B1,{"X","Y","Z";2,3,5;1,2,3},2+(A1="Q"),0),"")

  5. #5
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: IF, AND, OR formula confusing

    How come some of you always want a file!

    Quote Originally Posted by dogberry View Post
    Hi

    You will get a much better reponse if you post a worksheet with what you have and what you would like to achieve.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.



    Chris

  6. #6
    Registered User
    Join Date
    11-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: IF, AND, OR formula confusing

    dogberry - Hi I will do this shortly thanks
    Last edited by vox_vix; 12-06-2012 at 05:53 AM.

  7. #7
    Registered User
    Join Date
    11-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: IF, AND, OR formula confusing

    rcm - Thank you
    This worked when I put everything in and instead of the numbers I put in C1 plus number
    Then I changed the C1 at the start to the correct square and it stopped working altogether
    I shall have another go

  8. #8
    Registered User
    Join Date
    11-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: IF, AND, OR formula confusing

    Jason.b75 - thank you but this came up with #value when I put it in

  9. #9
    Registered User
    Join Date
    11-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Post file attached

    formula working.xls

    Hopefully this attachement will show what I am trying to achieve.
    All the formulas I have made have failed so I haven't included them as it shows how dumb I am!

  10. #10
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: IF, AND, OR formula confusing

    Try :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hope this helps

    Edit-
    Typed the formula before i saw the upload

    Edit 2
    BTW, this formula stolen from SUMPRODUCT AND/OR usages...lol
    Last edited by dredwolf; 12-06-2012 at 06:36 AM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: file attached

    Its better to mention your criteria's in separate location instead of evaluating it inside the formula. Because stepping into the formula in case of any addition or modification of conditions when the conditions are evaluated inside the formula will require too much of work.

    So go through the attachment file for details.
    Attached Files Attached Files


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  12. #12
    Registered User
    Join Date
    11-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: IF, AND, OR formula confusing

    Hi Dredwolf,
    Thank you but this isn't working, this may be because X, Y and Z are not numerical values

  13. #13
    Registered User
    Join Date
    11-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: file attached

    Sixthsense, thank you for your solution, I think using a look uptable will work.

    You use this formula
    =IF($C5="","",VLOOKUP($A5&LEFT($B5),$H$2:$I$7,2,FALSE)+$C5)

    Please can you explain why this part is required "&LEFT" also why does ($B5) have to be inside brackets?
    Thank you

  14. #14
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: IF, AND, OR formula confusing

    In lookup table i just mentioned the column a values and x, y.z.
    But in your column b data you are differentiating each set by x/xx for each set of data ( as new beginning of sets). But after the beginning of new set the data is marked wit just x instead of x/xx. so using left function to get 1 character from each cell of column b to make the data unique. The left function should be used as left(b5,1) but using the left(b5) will also get the same result so just ignored the second argument.
    sent from mobile device so accept the apologies in case of any typo errors.

  15. #15
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: IF, AND, OR formula confusing

    This formula doesn't required a lookup table

    =IF(C2="","",LOOKUP(LEFT(B2),{"X","Y","Z"},IF(LEFT(A2)="Q",{1,2,3},{2,3,5}))+C2)

  16. #16
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: IF, AND, OR formula confusing

    Hi vox_vix

    The VLOOKUP up is the way to go, easy to maintain, as it is not hard coded in.

  17. #17
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: IF, AND, OR formula confusing

    This update of my original formula works with the sample you uploade, but it is definitely getting cumbersome...
    In e2, copy down :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the lookup solutions offered are probably much more efficient though, I just offered this to show it would work

  18. #18
    Registered User
    Join Date
    11-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: IF, AND, OR formula confusing

    Thank you teethless mama

  19. #19
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: IF, AND, OR formula confusing

    You're Welcome!

  20. #20
    Registered User
    Join Date
    11-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: IF, AND, OR formula confusing

    Thanks for the update dredwolf!

  21. #21
    Registered User
    Join Date
    11-05-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: IF, AND, OR formula confusing

    Hi Kevin UK,
    I have gone with the Vlookup because it allows me to change the numbers if I need to without getting back into the formula.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1