• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding a variable to Multiconditional Formula

J

JWeber

New Member
I would be very grateful if someone could take a look at this, Thanks in advance! So i'm attaching a book that has two sheets, CHC and Standings. on the CHC sheet, the Chicago Cubs' matchups with each team is pulled in by a web query. In the cells between queries, I have a a repeating group of formulas that need help.

In this group of cells, there is a cell that shows which of the two team has the highest "SRS" rating (checked on a table using Vlookup on the Standings sheet.) I'm wondering if this cell could be further leveraged to reference a different cell (U39) on the CHC sheet to determine if the Cubs are the home team? ....part of what I can't figure out is cell U39 only contains the name of the ball park. So on this CHC sheet, for example, I'm looking to check if U39 contains the string "Wrigley" and if so, I'm then looking to include some variables: If the CHC are the home team (U39 contains "Wrigley") AND they are the higher rated team, B137 says "CHC", then the difference for B138 to display, "Yes" needs to be >.25, likewise, If CHC is not the higher rated team, AND U39 does not contain "Wrigley" the difference needed to display "Yes" needs to be >.25.

Then inversely, If CHC are higher rated but U39 does not match "Wrigley" or CHC is not higher rated but U39 does contain "Wrigley" then the difference needs to be >.5. I hope this makes sense! It seems a lot to ask, but I wanted to try. I appreciate any consideration!
 

Attachments

Hi ,

Please clarify the following :

1. The output in cell B138 should be YES or NO , based on some logic.

2. You mention :
to determine if the Cubs are the home team? ....part of what I can't figure out is cell U39 only contains the name of the ball park.
Click to expand...

U39 does not contain the name of a ball park , but your doubts are in order , since given the name of a ball park , how do we decide it is a home game or an away game ?

Anyway , if we assume that all that is needed is a SEARCH :

=ISNUMBER(SEARCH("Wrigley",U35))

will return TRUE if the cell U35 contains the text Wrigley anywhere in it , and FALSE otherwise.

3. You want 2 conditions to be combined :

a. Wrigley is present in some pre-defined cell

b. CHC is the higher-rated team

These 2 conditions can be combined to result in 4 combinations :

a b Difference
---------------------------------
FALSE FALSE 0.25
FALSE TRUE 0.50
TRUE FALSE 0.50
TRUE TRUE 0.25

Can you confirm ?

See the attached file for a preliminary formula.

Narayan
 

Attachments

Hello, Narayank991! Thanks so much for your reply! Here are your clarifications:

1. B138 should be Yes if:
  • CHC are higher rated team, game is at wrigley (U35 contains "wrigley"), and CHC are rated higher by >.25
  • CHC are higher rated team, game is not at wrigley, CHC are rated higher by >.5
  • CHC opponent is higher rated, game is not at wrigley, CHC opponent rated higher by >.25
  • CHC opponent is higher rated, game is at wrigley, CHC opponent rated higher by >.5
  • If one of the 4 combinations above happen but the rating differential isn't the prescirbed .25 or .5, then B138 should say "No"
2. I just noticed is that my web query seems to be changing the location of the home ballpark cell by a cell or two, shifted up or down. Would it just be possible instead to do another VLookup of CHC! in column U for the cell that contains the exact match "Venue:" and then always just check the cell directly below it to see if it contains, "Wrigley"? If it does, follow directives in point 1 of this post, if it contains the name of a different ballpark (not wrigley) follow directives in point 1 of this post. That way, this reference isn't tied to a single cell and relying on the query to put it exactly there....

3. You are exactly correct. These are the combinations. And if ANY of those 4 are true, and the difference between ratings meets the criteria, then cell B138 is "YES." The following combinations should have B138 display "Yes":
FALSE FALSE >0.25
FALSE TRUE >0.50
TRUE FALSE >0.50
TRUE TRUE >0.25

If one of those same combinations doesn't meet the required rating difference then it would say "No." The following four combinations should have B138 display "No":
FALSE FALSE <0.25
FALSE TRUE <0.50
TRUE FALSE <0.50
TRUE TRUE <0.25
 
Last edited:
You must log in or register to reply here.
Back
Top