Baseball and Conditional Formatting

CrimsnTide

New Member
Joined
Oct 25, 2017
Messages
5
Hopefully I can explain this correctly.

A2:A15 - Players last name

B1:B15 - Dates of games

The number of hits are entered for each player, on the date played. (actually are populated from a different page/tab in workbook)



I am HOPING to change color of the TWO or more cells when the adjacent cell has at least one hit (Showing a hitting streak for minimum of TWO games).



If a cell has a ZERO (No hits), the streak is broken and thus will not change color.



3/1 3/2 3/4 3/6

Ruth 1 2 3 1

Cobb 0 1 3 0

Jackson 1 2 0 1





I guess I'm having problems with Conditional Formatting since the formatting is based on the adjacent cell, in which I would want formatted also, since it was the BEGINNING of the hitting streak, A player could have a hit on one date, but zero hits prior and zero hits after.



Thanks!!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Hi, welcome to the board.

It's difficult to give a precise answer because it's not clear from your post, how your data is laid out exactly.

But you could try using the OR function.

For example, let's say you have either a 1 or a zero in each cell in column A.

A formula like this will identify whether any cell is part of a string of two continuous 1s, and return TRUE if it is

=or(and(A1=1,A2=1),and(A2=1,A3=1))

This specific example identifies whether A2 is part of a streak of 1s.

A formula like this can be used in Conditional Formating.
 

CrimsnTide

New Member
Joined
Oct 25, 2017
Messages
5
Hi, welcome to the board.

It's difficult to give a precise answer because it's not clear from your post, how your data is laid out exactly.

But you could try using the OR function.

For example, let's say you have either a 1 or a zero in each cell in column A.

A formula like this will identify whether any cell is part of a string of two continuous 1s, and return TRUE if it is

=or(and(A1=1,A2=1),and(A2=1,A3=1))

This specific example identifies whether A2 is part of a streak of 1s.

A formula like this can be used in Conditional Formating.


Thanks for prompt reply! So, this is probably more than what I can understand... The formula I can understand (well, almost), but if there were MULTIPLE hits on any given date, would it change the formula to a ">="? I can't see anyone getting above 4 hits in our league, however, a multi hit game is probable.. I know it's probably not relevant, but on multi-hit games, I do have it changing to a larger font, different color and bold using Conditional Formatting.

3/1
3/2
3/4
3/5
3/7
3/8
Ruth
1
1
2
1
McGuire
2
1
0-
1
Cobb
1
3
2
2
Aaron
2
2
1
2

<tbody>
</tbody>
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
I don't think there's a need to quote all of my post :)

What do you consider determines whether a streak continues or stops ?
If any score of 1 or higher is enough to maintain the streak, and any score of 0 breaks it, try this . . .

=or(and(A1>=0,A2>0),and(A2>0,A3>0))
 

CrimsnTide

New Member
Joined
Oct 25, 2017
Messages
5
Thank you again for the response...

As usual, my ultimate goal has some defining variables that it just may be too much for this novice to achieve.
As you say, "If any score of 1 or higher is enough to maintain the streak, and any score of 0 breaks it" is true but is only true if the player has a official "at bat". If a player does NOT play the next day after getting a hit, it does not register as a "0" - the cell is just blank. A "0" is entered only if a player has a official at bat (by rules) and does not get a hit (see below for official rules). I appreciate our response and will definitely try it out.


The OFFICIAL rule reads:

CONSECUTIVE-GAME HITTING STREAKS. A consecutive-game hitting streak shall not be terminated if all of a batter’s plate appearances (one or more) in a game result in a base on balls, hit batsman, defensive interference or obstruction or a sacrifice bunt. The streak shall terminate if the player has a sacrifice fly and no hit.
A player’s individual consecutive-game hitting streak shall be determined by the consecutive games in which such player appears and is not determined by his club’s games.



I just keep adding to my template trying to learn a little bit more each time. Conditional Formatting is a whole new "ball game" for me...

Thanks for response again, I will try it out and see what I can learn from it!!!
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
OK, perhaps like this . . .

=or(and(or(A1>0,A1=""),or(A2>0,A2="")),and(or(A2>0,A2=""),or(A3>0,A3="")))

There was a slight mistake in my post #4 , should have been just > and not >=, sorry !
 

Watch MrExcel Video

Forum statistics

Threads
1,109,011
Messages
5,526,263
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top