Results 1 to 6 of 6

2 Criteria IF Statement

This is a discussion on 2 Criteria IF Statement within the Excel Questions forums, part of the Question Forums category; simply want to say if Home Team is A, and Home Score is greater than Away Score, use AHW (A ...

  1. #1
    New Member
    Join Date
    Apr 2008
    Posts
    15

    Default 2 Criteria IF Statement

    simply want to say if Home Team is A, and Home Score is greater than Away Score, use AHW (A Home Win)


    Only bothered about teams A & B so 8 possible combinations.

    Hope someone can help me..

    Thanks

    A B C D E
    1 Home Team HomeScore Away Score Away Team Result
    2 A 1 2 C AHL
    3 A 3 0 D AHW
    4 B 4 1 E BHW
    5 B 2 3 F BHL
    6 G 3 2 A AAL
    7 H 1 2 A AAW
    8 D 5 1 B BAL
    9 C 1 3 B BAW

  2. #2
    Board Regular proficient's Avatar
    Join Date
    Apr 2012
    Location
    Karachi, Pakistan.
    Posts
    468

    Default Re: 2 Criteria IF Statement

    Quote Originally Posted by markhib View Post
    simply want to say if Home Team is A, and Home Score is greater than Away Score, use AHW (A Home Win)


    Only bothered about teams A & B so 8 possible combinations.

    Hope someone can help me..

    Thanks

    A B C D E
    1 Home Team HomeScore Away Score Away Team Result
    2 A 1 2 C AHL
    3 A 3 0 D AHW
    4 B 4 1 E BHW
    5 B 2 3 F BHL
    6 G 3 2 A AAL
    7 H 1 2 A AAW
    8 D 5 1 B BAL
    9 C 1 3 B BAW
    May be this...

    Sheet9

     ABCDE
    1Home TeamHome ScoreAway TeamAway ScoreResult
    2A3P2AHW
    3B2O1BHW
    4C6L4CHW
    5D5T3DHW
    6E4E5EHL
    7F2Q1FHW

    Spreadsheet Formulas
    CellFormula
    E2=IF(B2>D2,A2&"HW",A2&"HL")
    E3=IF(B3>D3,A3&"HW",A3&"HL")
    E4=IF(B4>D4,A4&"HW",A4&"HL")
    E5=IF(B5>D5,A5&"HW",A5&"HL")
    E6=IF(B6>D6,A6&"HW",A6&"HL")
    E7=IF(B7>D7,A7&"HW",A7&"HL")


    Excel tables to the web >> Excel Jeanie HTML 4
    Regards,

    Kamran Mumtaz
    PAKISTAN.

    --------------------------------------------------------------------------------------------------------------------------------------------------------
    Playing with excel -
    2007

    Want to post a small screen shot? Try this Excel jeanie,

    Try searching for your answer first, see how

  3. #3
    Board Regular
    Join Date
    Oct 2005
    Posts
    161

    Default Re: 2 Criteria IF Statement

    Use the following formula in Cell F2 and copy down:

    =IF(B2>C2,A2&"HW","")

    Hope this helps.

  4. #4
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,246

    Default Re: 2 Criteria IF Statement

    =if(or(a2={"a","b"}),a2&"h"&if(b2>c2,"w","l"),if(or(d2={"a","b"}),d2&"a"&if(b2>c2,"l","w")))
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  5. #5
    Board Regular proficient's Avatar
    Join Date
    Apr 2012
    Location
    Karachi, Pakistan.
    Posts
    468

    Default Re: 2 Criteria IF Statement

    Quote Originally Posted by Robert Mika View Post
    =if(or(a2={"a","b"}),a2&"h"&if(b2>c2,"w","l"),if(or(d2={"a","b"}),d2&"a"&if(b2>c2,"l","w")))
    Are you sure the formula is working...?
    Sheet9

     ABCDE
    1Home TeamHome ScoreAway TeamAway ScoreResult
    2A3P2Ahl

    Spreadsheet Formulas
    CellFormula
    E2=IF(OR(A2={"a","b"}),A2&"h"&IF(B2>C2,"w","l"),IF(OR(D2={"a","b"}),D2&"a"&IF(B2>C2,"l","w")))


    Excel tables to the web >> Excel Jeanie HTML 4
    Regards,

    Kamran Mumtaz
    PAKISTAN.

    --------------------------------------------------------------------------------------------------------------------------------------------------------
    Playing with excel -
    2007

    Want to post a small screen shot? Try this Excel jeanie,

    Try searching for your answer first, see how

  6. #6
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,246

    Default Re: 2 Criteria IF Statement

    Quote Originally Posted by proficient View Post
    Are you sure the formula is working...?
    Sheet9

    A B C D E
    1 Home Team Home Score Away Team Away Score Result
    2 A 3 P 2 Ahl

    Spreadsheet Formulas
    Cell Formula
    E2 =IF(OR(A2={"a","b"}),A2&"h"&IF(B2>C2,"w","l"),IF(OR(D2={"a","b"}),D2&"a"&IF(B2>C2,"l","w")))


    Excel tables to the web >> Excel Jeanie HTML 4
    works here:
    Sheet1

     ABCDEF
    1Home TeamHomeScoreAway ScoreAway TeamResult 
    2A12CAHLAhl
    3A30DAHWAhw
    4B41EBHWBhw
    5B23FBHLBhl
    6G32AAALAal
    7H12AAAWAaw
    8D51BBALBal
    9C13BBAWBaw
    10A32P Ahw

    Spreadsheet Formulas
    CellFormula
    F2=IF(OR(A2={"a","b"}),A2&"h"&IF(B2>C2,"w","l"),IF(OR(D2={"a","b"}),D2&"a"&IF(B2>C2,"l","w")))
    F3=IF(OR(A3={"a","b"}),A3&"h"&IF(B3>C3,"w","l"),IF(OR(D3={"a","b"}),D3&"a"&IF(B3>C3,"l","w")))
    F4=IF(OR(A4={"a","b"}),A4&"h"&IF(B4>C4,"w","l"),IF(OR(D4={"a","b"}),D4&"a"&IF(B4>C4,"l","w")))
    F5=IF(OR(A5={"a","b"}),A5&"h"&IF(B5>C5,"w","l"),IF(OR(D5={"a","b"}),D5&"a"&IF(B5>C5,"l","w")))
    F6=IF(OR(A6={"a","b"}),A6&"h"&IF(B6>C6,"w","l"),IF(OR(D6={"a","b"}),D6&"a"&IF(B6>C6,"l","w")))
    F7=IF(OR(A7={"a","b"}),A7&"h"&IF(B7>C7,"w","l"),IF(OR(D7={"a","b"}),D7&"a"&IF(B7>C7,"l","w")))
    F8=IF(OR(A8={"a","b"}),A8&"h"&IF(B8>C8,"w","l"),IF(OR(D8={"a","b"}),D8&"a"&IF(B8>C8,"l","w")))
    F9=IF(OR(A9={"a","b"}),A9&"h"&IF(B9>C9,"w","l"),IF(OR(D9={"a","b"}),D9&"a"&IF(B9>C9,"l","w")))
    F10=IF(OR(A10={"a","b"}),A10&"h"&IF(B10>C10,"w","l"),IF(OR(D10={"a","b"}),D10&"a"&IF(B10>C10,"l","w")))


    Excel tables to the web >> Excel Jeanie HTML 4
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com