max number then deduct in excel

[Mike]

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

 

[David Billigmeier]

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?

 

[Toppers]

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