IF STATEMENT - NESTED

[Guest]

I am creating a spreadsheet to track pitch counts in Little League. There are
new rules about a number of days rest required depending on pitch counts. The
criteria is below:

If a player pitches 61 or more pitches in a day, three (3) calendar days of
rest must be observed.
If a player pitches 41 - 60 pitches in a day, two (2) calendar days of rest
must be observed.
If a player pitches 21 - 40 pitches in a day, one (1) calendar days of rest
must be observed.
If a player pitches 1-20 pitches in a day, no (0) calendar day of rest must
be observed.

I writing an IF statement to give the players next available date based on
this criteria. this is what I have so far - not sure where the error in my
formula is -it may be in the second IF part where I am looking for a range
between two number. My formula gives back TRUE.

cell D15 contains number of pitches, D9 is the date pitched.

=IF(D15>=61,D$9+4,IF(D15>=41,D15<=60,""))


Appreciate the help!!

 

[JE McGimpsey]

Well, your syntax is wrong. You COULD use

=IF(D15>=61,D$9+4,IF(AND(D15>=41,D15<=60),""))

But that's not necessary because the second IF will only be executed if
D15<61, so you could write

=IF(D15>=61,D$9+4,IF(D15>=41,"","something else"))

BUT, I'd use

=D$9 + MIN(4,TRUNC((D15-1)/20)+1)



=IFIn article <[email protected]>,

 

[Guest]

=LOOKUP(D15,{1,21,41,61},{0,1,2,3})

will give number of days rest

so your result is .....

=D$9+LOOKUP(D15,{1,21,41,61},{0,1,2,3})+1

or

=D$9+LOOKUP(D15,{1,21,41,61},{1,2,3,4})

HTH

 

[Guest]

See if this works.

=IF(D15<21,D9,IF(D15<41,D9+1,IF(D15<61,D9+2,D9+3)))

Mike

 

[Guest]

Thanks all for you help - they all worked.

I found the Lookup easier - I have used VLOOKUP before but never this -
very cool.


Thanks Again...