Answered by:
Football Pool with Excel
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 710 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!
Answers


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.
 Edited by Bernie Deitrick, Excel MVP 20002010 Thursday, January 12, 2017 7:14 PM
 Marked as answer by Martsman Thursday, January 12, 2017 7:30 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))))
 Edited by Bernie Deitrick, Excel MVP 20002010 Tuesday, January 17, 2017 11:26 PM
 Marked as answer by Martsman Wednesday, January 18, 2017 10:00 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.
 Proposed as answer by Chenchen LiModerator Thursday, January 12, 2017 2:39 AM



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 J4Q4 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.

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.
 Edited by Bernie Deitrick, Excel MVP 20002010 Thursday, January 12, 2017 7:14 PM
 Marked as answer by Martsman Thursday, January 12, 2017 7:30 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?

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.

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

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?

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.
 Edited by Bernie Deitrick, Excel MVP 20002010 Friday, January 13, 2017 8:21 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 BH wouldn't that create a negative? So the previous example of BAL by 4 = BAL by 0?

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?


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?


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

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.

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))))
 Edited by Bernie Deitrick, Excel MVP 20002010 Tuesday, January 17, 2017 11:26 PM
 Marked as answer by Martsman Wednesday, January 18, 2017 10:00 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.

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. ;)
 Edited by Bernie Deitrick, Excel MVP 20002010 Thursday, January 19, 2017 1:20 AM


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 $
 Edited by Bernie Deitrick, Excel MVP 20002010 Tuesday, January 24, 2017 8:47 PM