Another baseball scenario in Excel

**cheddarthief** · 05-06-2011, 11:41 AM

Okay, was thinking about how to ask this one so I'll do my best.

I've been trying to find a pitching calculation generator for my community Little League website using Excel. Little League mandates that if a pitcher throws "X" amount of pitches in a day that he must be held out "X" amount of days before returning to pitch again. Here are the conditions:

20 or less Pitches = No Days Rest
21-35 Pitches = 1 Days Rest
36-50 Pitches = 2 Days Rest
51-65 Pitches = 3 Days Rest
66-75 Pitches = 4 Days Rest

75 is the max number of pitches.

Observe the crude Excel file I have attached for reference. Any value in GREEN is an input while any value in BLUE is calculated. The calculated cells need to apply the rules, then refer to Cell B1 and return the appropriate date. Additionally, how do you deal with the end of the month situations where say for example, Tom was to pitch on the 29th of June of this year and not be permitted to pitch again until the 2nd of July due to waiting rules?

I know for many of you this is simple but for a newbie such as myself, not so simple. Any help would be greatly appreciate.

Thanks in advance,
cheddarthief

**shg** · 05-06-2011, 11:45 AM

In C5,

=$B$1 + MATCH(B5, {0,21,36,51,66})

**cheddarthief** · 05-06-2011, 12:52 PM

Thanks, that worked great. Can you please explain to me what this equation basically "says"?

For example: equals the absolute value in cell B1 + whatever....

**shg** · 05-06-2011, 12:59 PM

The MATCH function returns the position of the largest value in the literal array that is less than or equal to the value in B5. The array is (and must be) in ascending order.

Help explains MATCH pretty clearly.