+ Reply to Thread
Results 1 to 20 of 20

Multiple If, AND, OR statement

  1. #1
    Registered User
    Join Date
    10-28-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2010
    Posts
    14

    Multiple If, AND, OR statement

    Can someone put this into into an excel formula?

    If B5 = "Cpl" and I75 = 47 or B5 = "Sdt" and I75 = 47 then cell K75=0, if not then cell = I75 And

    If B5 not "Cpl" or "Sdt" And I75 = 59 then cell K75=0, if not then cell =I75

    Thanks:

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

    Re: Multiple If, AND, OR statement

    you can not enter values into other cells with formulas or functions, only the cell you are in, so are there typos in your OP, or are you looking for a Macro/VBA solution here?
    OR am I missing something ? (always possible )
    Last edited by dredwolf; 10-29-2013 at 12:02 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

  3. #3
    Registered User
    Join Date
    10-28-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Multiple If, AND, OR statement

    The cell I want the result in is K75. So i need the formula enterered in that cell ro produce a value based on looking up the info that is in cell B5 and I75. Would you like to see the sheet?

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

    Re: Multiple If, AND, OR statement

    I would always like to see the sheet

  5. #5
    Registered User
    Join Date
    10-28-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Multiple If, AND, OR statement

    Here you go.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-28-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Multiple If, AND, OR statement

    It is the cell in yellow that I would like the formula in and it is based on the selection chosen in B3.

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

    Re: Multiple If, AND, OR statement

    Ok I'd go something like this (k75):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    (I think I got it all right, but PLEASE say if I don't)

    Hope this helps
    EDIT- I think i got the results backwards, should be :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I believe..getting a bit tired, sorry
    Last edited by dredwolf; 10-29-2013 at 12:32 AM.

  8. #8
    Registered User
    Join Date
    10-28-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Multiple If, AND, OR statement

    Please Login or Register  to view this content.
    I made a small change in your code. Changed Std to Sdt. There is missing a constraint though. When it is Sdt and 47 the total should be 0 not 47.

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

    Re: Multiple If, AND, OR statement

    Ah...darn it....change the first And to an OR....my bad
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    been a long day on a jackhammer, so I am a little off, sorry

  10. #10
    Registered User
    Join Date
    10-28-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Multiple If, AND, OR statement

    No problem. You got me closer. I just got to figure out to set the value in I75 to 0 when the 2 conditions are met.

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

    Re: Multiple If, AND, OR statement

    Well, the last formula I gave you meets the conditions stated ( I believe), but if it's still giving the wrong answer, try separting the or, something like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by dredwolf; 10-29-2013 at 01:07 AM. Reason: messed up formula, fixed

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

    Re: Multiple If, AND, OR statement

    Well use the same style logic as for K75, if these conditions met, this else that
    The hardest part is the basic logic, after that, making sure it works!...lol

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

    Re: Multiple If, AND, OR statement

    Just a hint for EVERYONE, the more possibilities you can erase in the first logic test (or the second, etc..), the less nested IF's you need to accomplish your goal!
    (this doesn't mean nested if's are not needed, just that they can usually be pared down by a large number if you think about the results that produce certain answers.... )

  14. #14
    Registered User
    Join Date
    10-28-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Multiple If, AND, OR statement

    Quote Originally Posted by dredwolf View Post
    Ah...darn it....change the first And to an OR....my bad
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    been a long day on a jackhammer, so I am a little off, sorry
    This is really close to solving it except when the point total becomes exactly 59 under option a Cpl or Sdt, the score shows up as a 0when it should be 59.

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

    Re: Multiple If, AND, OR statement

    AAaarg...could you upload me a sheet with this ?, I've looked through the logic, and I cannot see the error...need some help...lol

  16. #16
    Registered User
    Join Date
    10-28-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Multiple If, AND, OR statement

    I've highlighted it in blue to show that the condition should not go to zero at that point.
    Attached Files Attached Files

  17. #17
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Multiple If, AND, OR statement

    =IF(AND(OR(B5="CPL",B5="SDT"),I75=47),I75,IF(I75=59,I75,0))

    Azumi
    Last edited by azumi; 10-29-2013 at 02:02 AM.

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

    Re: Multiple If, AND, OR statement

    I think I see whats wrong now, I took your written words and translated it literally, I should have read it closer (I hope this is it...truly ), so lets try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Please tell me this one is right....lol...
    Edit-
    the reason the or didn't work, is that if it did = one of them, it could not = the other, so the or returned the TRUE value, which means it gave the 0, insted of the I75 value (59)...,
    I am so sorry about this, my logic skills are usually so much better than this
    Last edited by dredwolf; 10-29-2013 at 02:12 AM.

  19. #19
    Registered User
    Join Date
    10-28-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Multiple If, AND, OR statement

    Yes! Thank you so much dredwolf!

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

    Re: Multiple If, AND, OR statement

    You are very Welcome !
    And Thank You, Thank You, Thank You !!! (for putting up with my denseness tonight)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] If statement that will look among multiple criteria and generate a statement
    By liz5818 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-01-2013, 04:12 PM
  2. multiple if/then statement
    By Mile029 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-06-2013, 03:07 PM
  3. multiple if statement
    By Blake 7 in forum Excel General
    Replies: 2
    Last Post: 09-14-2011, 08:50 AM
  4. IF statement using multiple variables to give multiple outcomes
    By stujordan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2009, 06:46 AM
  5. Help with a multiple OR statement.
    By Pank in forum Excel General
    Replies: 2
    Last Post: 07-11-2006, 01:35 PM

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