none
Football Pool with Excel RRS feed

  • Question

  • Hello all,

    Super novice here. I have been reading about VBA and the benefits it provides using it to code certain aspect with Macros inside excel 2010. With all that's out there I do not know where to start. I'm looking to simplify my spreadsheet to auto format cells when certain criteria are met. Any information or examples would be greatly appreciated. Atleast a good place to start and I can trial and error. Heck I don't even know if VBA is the best option.

    We have roughly 7-10 players.

    The teams have a spread as set forth my vegas odds

    The players guess which team will either cover the spread or not. Ex. GB @ DAL 5    Dallas is favored by 5. They need to beat GB by 6 to cover.

    If the player chooses DAL AND Dallas covers the spread the DAL cell needs to be highlighted in yellow AND the players who selected the GB, the GB cell needs a diagonal line thru it.

    Of coarse this is visa versa. So if GB wins or loses by 5 or less, then GB's cell highlights yellow and DAL cell is crossed thru.

    I hope that makes sense. Then I need the cells to add the number of yellow (win) cells and total at bottom. But that is waay later LOL.

    Thanks in advance!

    Wednesday, January 11, 2017 6:22 PM

Answers

  • Sorry - CF is Conditional Formatting.  Here is a page with instructions for using formulas with CF:

    https://exceljet.net/conditional-formatting-with-formulas

    • Marked as answer by Martsman Thursday, January 12, 2017 3:18 PM
    Wednesday, January 11, 2017 9:56 PM
  • Select J4:Q12, and use CF for yellow with the formula

    =OR(AND(J4=$D4,$C4>$G4+$B4),AND(J4=$F4,$C4<=$G4+$B4))

    And then use CF for the cross out with the formula

    =OR(AND(J4=$D4,$C4<=$G4+$B4),AND(J4=$F4,$C4>$G4+$B4))

    You may need to play with the <= and > because I'm not sure of your logic for when the spread creates a tie.


    Thursday, January 12, 2017 7:14 PM
  • I think this should do it:

    =OR(AND(J4=$D4,OR(AND(ISNUMBER($B4),$C4>$G4+$B4),AND(ISNUMBER($H4),$C4>=$G4-$H4))),AND(J4=$F4,OR(AND(ISNUMBER($H4),$G4>$C4+$H4),AND(ISNUMBER($B4),$G4>=$C4-$B4))))

    and for the reverse

    =OR(AND(J4<>$D4,OR(AND(ISNUMBER($B4),$C4>$G4+$B4),AND(ISNUMBER($H4),$C4>=$G4-$H4))),AND(J4<>$F4,OR(AND(ISNUMBER($H4),$G4>$C4+$H4),AND(ISNUMBER($B4),$G4>=$C4-$B4))))


    Tuesday, January 17, 2017 8:15 PM

All replies

  • Let's say that you have the teams, spreads, and winners starting in cell A1, like so:

    Spread Visitor   Home Spread Winner
      GB @ DAL 5 GB
    4 PB @ KC   KC

    And you have columns of picks - say, starting in H1, like:

    Tom Tim Fred Joe
    GB DAL GB DAL
    KC KC PB PB

    Select H2 through the end of the picks, and use CF, with the formula option, using the formula

    =$F2=H2

    and select the yellow fill that you want.

    Then add another level, with the diagonal line, using the formula option and 

    =$F2<>H2

    Then, to count the number correct, use formulas like

    =SUMPRODUCT(($F$2:$F$5=H2:H5)*1)

    for each column of picks.

    Wednesday, January 11, 2017 8:05 PM
  • Thank for the quick response! I look forward to applying those formulas and tinkering them a bit. Back to reference what is CF with formula option?

    Wednesday, January 11, 2017 9:23 PM
  • Sorry - CF is Conditional Formatting.  Here is a page with instructions for using formulas with CF:

    https://exceljet.net/conditional-formatting-with-formulas

    • Marked as answer by Martsman Thursday, January 12, 2017 3:18 PM
    Wednesday, January 11, 2017 9:56 PM
  • The sheet I have has the spread points are on column B4 and H4 respectively, the score is column C and G home teams are labeled on column F.  Team Selections start on J4 through Q12.

    So for the CF of the picks, the formula should be and I'm thinking it would be based on each game and not as 1 formula:

    Example game:

    1 10 BAL @ CIN 27. 1 is the spread, 10 is BAL's score, 27 is CIN score

    The formula for picks J4-Q4 should be:

    IF C4 (Bal's score) is > than G4 by 2 (since they have to beat CIN by 2 points to cover spread) then background color with char of BAL (representing BAL) should be yellow AND cells with CIN char should be crossed out with diagonal.

    If G4 is > than C4 or G4 is = to C4 or G4 is < C4 by 1 then cells with Char BAL are crossed out with diagnol mark AND CIN char cells are highlighted yellow.

    Wow that was tough explaining. Now I think CF would be a good fit, I'm trying to find the right words or commands to use in the formulas.

    Thursday, January 12, 2017 5:48 PM
  • Select J4:Q12, and use CF for yellow with the formula

    =OR(AND(J4=$D4,$C4>$G4+$B4),AND(J4=$F4,$C4<=$G4+$B4))

    And then use CF for the cross out with the formula

    =OR(AND(J4=$D4,$C4<=$G4+$B4),AND(J4=$F4,$C4>$G4+$B4))

    You may need to play with the <= and > because I'm not sure of your logic for when the spread creates a tie.


    Thursday, January 12, 2017 7:14 PM
  • This is great! Thank you again for all this! I have a starting point! So, just to be clear, I've put the code into CF. I changed the value of G4 to another number that should switch the highlights and cross outs, but nothing happened. I opened up CF to make sure the two rules were there and they are.

    For clarity:

    $D4 = The actual cell of D4, whatever is in it.

    So (J4=$D4,$C4<=$G4+$B4) Why is there no $ for J4?

    I did run the rules highlighting the cells J4 thru Q4 since that formula represents that specific game.

    But it does not seem to be changing according to what the score is. It did immediately cross out all the BAL cells since I set the score to so BAL would be crossed out.

    Wouldn't J4 be with K4,L4,M4 etc to Q4?

    Thursday, January 12, 2017 9:18 PM
  • The code is correct. I had entered it in wrong. That is great! Thank you for the start. Now I need to change it for each game. The hard part is doing it for each game every week and customizing the code to match.

    Again for clarity the =OR(AND( part of the formula what does that stand for? Just looking how to explain this im layman's terms.

    Friday, January 13, 2017 3:15 PM
  • You don't need to change the formula for each row - you need to select all the cells and apply the CF once, or copy the first row and paste formats on the others.  The formula was written in such a way that it updates properly for the other rows.

    The reason it does that is that the cell references are relative (no $s) for the cell J4 - that updates to K4.... Q4, but also J5... Q5 and so on. The other cell references are all like $G4 - which means when it is copied across, it stays G4 (so that the correct information is referenced) but when it is copied down, it updates the rows properly, to $G5 and so on to $G12.

    In laymen's terms: the formula used in CF needs to return TRUE or FALSE - when the formula is TRUE, the CF is applied.  OR is a function that returns TRUE when either of the two (or more) formulas are true, and AND returns TRUE when both (or all) of its formulas are true.  So if you say

    IF GB wins by more than the spread, and the player picked them...

    That becomes  

    AND(Player picked GB,GBScore>DALScore+spread)

    AND(J4=$D4,$C4<=$G4+$B4)

    and so on

    Friday, January 13, 2017 4:03 PM
  • Since the spread changes between Columns H and B because home team is always in Column F. For the spreads that are in Column H do I need to swap B for H?

    So example:

    =OR(AND(J4=$D4,$C4>$G4+$B4),AND(J4=$F4,$C4<=$G4+$B4)) is for spreads in column B

    =OR(AND(J4=$D4,$C4>$G4+$H4),AND(J4=$F4,$C4<=$G4+$H4)) is for spreads in column H

    The laymans terms is great! I am so much farther ahead from where I was. I can now play with it and customize it more.

    When I selected all the cells and applied the CF it seems to highlight almost everything and doesn't change much. Could this be because of the spread being in different columns?

    Friday, January 13, 2017 5:47 PM
  • I think - and I have to emphasize the think part of that, since I don't really understand how spreads are actually used in gambling - that a spread in H would be equivalent to a negative spread in B.  So, try changing each $B4 to  $B4-$H4, and get rid of the <= requirement by evaluating which spread is used like this:

    =OR(AND(J4=$D4,$C4>$G4+$B4-$H4+($H4>0)*1),AND(J4=$F4,$C4<$G4+$B4-$H4+($B4>0)*1))

    If that doesn't work, post example schedules/scores/spreads that show where it doesn't work.





    Friday, January 13, 2017 8:15 PM
  • How the spread works is a little confusing. If a team is favored by 3. The spread would be 3. The way we play with spreads is that the team that is favored by 3 needs to win by 4, covering the spread completely.

    Example:    BAL @ CIN       BAL is favored by 3. BAL needs their total score to be more than CIN's score by 4 to be considered a WIN (highlight in yellow). BAL loses (gets crossed out) if their total score is 3 ( the spread) points or less than CIN's total score.

    BAL score = 13 Cin score = 9  outcome is that BAL wins. They are 4 points above CIN.

    BAL score = 10 Cin Score = 9. CIN wins because BAL score is not greater than CIN's score by 4.

    I hope that kind of clears up how the spread works.

    As for the negative code is that replacing  once of the two rules or both?

    There is no spread for BOTH column B and H at the same time. It is either B or H depending on who is favored. Is there a string for it to check to see if there is a number inside either column B or H BEFORE it runs the other rules? Because if it subtracts the spread B-H wouldn't that create a negative? So the previous example of BAL by 4 = BAL by 0?  

    Friday, January 13, 2017 11:24 PM
  • applying the code it seems to work for the spreads. It doesn't highlight the loser cell and cross the loser cell out. I just need to add the other rule for the cross out as well. Would I switch the - to plus since the spread would be considered positive being in column H or is the minus column H still apply?
    Saturday, January 14, 2017 3:09 PM
  • The formula for the crossout should just be that the wrong team is picked - so we change = to <> for the team name part.

    =OR(AND(J4<>$D4,$C4>$G4+$B4-$H4+($H4>0)*1),AND(J4<>$F4,$C4<$G4+$B4-$H4+($B4>0)*1))

    Saturday, January 14, 2017 8:08 PM
  • Its working! And I got the code so far anyway to add up the total correct!

    How ever there looks to be an issue when the spread is a decimal. Yay another variable!

    So if a spread is 4.5 a team needs to win by 5 to be highlight. Sometimes it doesn't highlight the correct team and sometimes it highlights everything.

    Example:

    JAX @ IND 4.5  With the score of 20(JAX) to 24(IND) The highlight should go to JAX since IND needs 5 over to win. But it doesn't highlight any of them with that score. Other scores work fine.

    Opposite is true for  NE @ MIA.  NE favored by 9.5 needs to win by 10.  With the score 35(NE) to 25(MIA) It highlight's all the cells.

    Would this because the decimal is skewing the formula?

    Saturday, January 14, 2017 9:39 PM
  • Playing some scores and it seems to also be affecting the non-decimal spreads as well. Can't quite put my finger on what the pattern is.
    Saturday, January 14, 2017 10:00 PM
  • I clearly don't understand spreads. Who wins if you have

    20 JAX @ IND 24 4

    What is the difference between

    20 JAX @ IND 24 4.5

    and

    20 JAX @ IND 24 5

    If you could just show a list of scores and spreads, and who actually wins in each case, like

    20 JAX @ IND 24 5     JAX wins

    20 JAX @ IND 24 4.5   JAX wins

    20 JAX @ IND 24 4     ??? wins

    Saturday, January 14, 2017 11:20 PM
  • We pull our spreads from 3 different locations and sometimes it averages out to have the half point.

    20 JAX @ IND 24 4 = JAX wins because IND needs to BEAT the spread.

    20 JAX @ IND 24 4.5 = JAX wins because IND need to BEAT the spread.

    20 JAX @ IND 24 5= JAX wins because IND needs to beat the spread.

    20 JAX @ IND 25 4 = IND because IND has BEATEN the spread.

    I hope that kind of clarifies it a little. I'll be able to work on the formula a little more today since I had to work the weekend.

    Monday, January 16, 2017 3:08 PM
  • I think this should do it:

    =OR(AND(J4=$D4,OR(AND(ISNUMBER($B4),$C4>$G4+$B4),AND(ISNUMBER($H4),$C4>=$G4-$H4))),AND(J4=$F4,OR(AND(ISNUMBER($H4),$G4>$C4+$H4),AND(ISNUMBER($B4),$G4>=$C4-$B4))))

    and for the reverse

    =OR(AND(J4<>$D4,OR(AND(ISNUMBER($B4),$C4>$G4+$B4),AND(ISNUMBER($H4),$C4>=$G4-$H4))),AND(J4<>$F4,OR(AND(ISNUMBER($H4),$G4>$C4+$H4),AND(ISNUMBER($B4),$G4>=$C4-$B4))))


    Tuesday, January 17, 2017 8:15 PM
  • That is it! IT works flawlessly! You are a master. Now can you explain the ISNUMBER inside the function for me so I can break this down? How does excel see this formula.

    Since CF was used for the outcomes to add up the winning cells, in AutoSum it has and If statement. Would I try that or another rule for CF to be created since the cells don't deal with numbers.

    Wednesday, January 18, 2017 10:00 PM
  • ISNUMBER just makes sure that the cell has a number - it returns TRUE is the cell has a number (the spread) or FALSE if it is blank.

    To count the number or correct picks for column J, use

    =SUMPRODUCT((J4:J14=$D4:$D14)*(ISNUMBER($B4:$B14)*($C4:$C14>($G4:$G14+$B4:$B14))+(ISNUMBER($H4:$H14)*($C4:$C14>=($G4:$G14-$H4:$H14)))))+SUMPRODUCT((J4:J14=$F4:$F14)*((ISNUMBER($H4:$H14)*($G4:$G14>$C4:$C14+$H4:$H14)+(ISNUMBER($B4:$B14)*($G4:$G14>=$C4:$C14-$B4:$B14)))))

    and then copy to the right.

    Of course, this all could have been avoided if we simply used another column with the name of the team that won (the "Winner" column from my first post) with the spread taken into account.  That would not have been as much fun, though.  ;-)


    Thursday, January 19, 2017 1:15 AM
  • Copy right? Is that another CF or under autosum formula?
    Tuesday, January 24, 2017 8:17 PM
  • No - "copy to the right" means select the cell with the formula

    =SUMPRODUCT((J4:J14=$D4:$D14)*(ISNUMBER($B4:$B14)*($C4:$C14>($G4:$G14+$B4:$B14))+(ISNUMBER($H4:$H14)*($C4:$C14>=($G4:$G14-$H4:$H14)))))+SUMPRODUCT((J4:J14=$F4:$F14)*((ISNUMBER($H4:$H14)*($G4:$G14>$C4:$C14+$H4:$H14)+(ISNUMBER($B4:$B14)*($G4:$G14>=$C4:$C14-$B4:$B14)))))

    copy it, and paste it into the cells to the right.  The way that you had described your layout, the picks were in adjacent columns starting in column J, so if your list of picks is in columns J to Z, then copy that formula from J all the way across to Z. Excel will update the J reference to K, L... Z without changing the column addresses preceeded by $



    Tuesday, January 24, 2017 8:46 PM