Baseball and Conditional Formatting

[CrimsnTide]

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!!

 

[Gerald Higgins]

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]

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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]3/1
[/TD]
[TD]3/2
[/TD]
[TD]3/4
[/TD]
[TD]3/5
[/TD]
[TD]3/7
[/TD]
[TD]3/8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ruth
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]McGuire
[/TD]
[TD][/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]0-
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cobb
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aaron
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

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]

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]

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 !