max number then deduct in excel

Hi i got a worksheet that counts goals scored in a week and totals them in
the last column using the "sum" function, but what i want it to do is not
allow the total to go over 21, e.g if someone is on 20 goals and there team
scores 3 then i want it to total to 21 then deduct whats left, in this case
it would display a total of 19, one to make it 21 then two deducted, is this
possible please
--
thanks

So you want to penalize going over 21 then? Try this:

=IF(SUM(A1:E1)>21,42-SUM(A1:E1),SUM(A1:E1))

Modify the range A1:E1 to fit your data.
Does that help?
--
Regards,
Dave


"Mike" wrote:

> Hi i got a worksheet that counts goals scored in a week and totals them in
> the last column using the "sum" function, but what i want it to do is not
> allow the total to go over 21, e.g if someone is on 20 goals and there team
> scores 3 then i want it to total to 21 then deduct whats left, in this case
> it would display a total of 19, one to make it 21 then two deducted, is this
> possible please
> --
> thanks

TRY:

Assuming scores in columns A to G and SUM is in H:

=IF(SUM(A1:G1)>21,42-SUM(A1:G1),SUM(A1:G1))

HTH

"Mike" wrote:

> Hi i got a worksheet that counts goals scored in a week and totals them in
> the last column using the "sum" function, but what i want it to do is not
> allow the total to go over 21, e.g if someone is on 20 goals and there team
> scores 3 then i want it to total to 21 then deduct whats left, in this case
> it would display a total of 19, one to make it 21 then two deducted, is this
> possible please
> --
> thanks