frustrated over something probably simple

[worthingto]

i am helping my teammate (sec/tres of league) by taking care of the scores for our bowling league. i have almost got the spreadsheet to do everything i want it to do except for one thing. the league is head to head and the handicap is setup with the difference of the two ave's with a max of 50. i'm trying to find a formula to do this if (J4-A4)>50 than value is 50. if (J4-A4)<0 (i.e -4) than value is 0. i almost have it setup that all i have to do is tye in the individual scores. any and all help is greatly appreciated

 

[shg]

Welcome to the board.

=MIN(50, MAX(0, J4-A4))

or

=MEDIAN(0, J4-A4, 50)

 

[worthingto]

thanks, see something so simple... new dilemma just surfaced.
i used =IF((D4+C4)>(L4+M4),"TRUE", IF((D4+C4)=(L4+M4), "HALF","FALSE"))
to calculate tie's
and used
=COUNTIF(D16:D20,"TRUE")
to figure out points won and it doesn't count anything. do i have to adjust the countif formula to compensate for the if formula?

 

[shg]

It might be easier to use,

=CHOOSE(SIGN(D4+C4-L4-M4) + 2, "Lose", "Draw", "Win")

... and

=COUNTIF(that range, "Win")