Need help with formula

[David Matzas]

I would like to build a formula that says:

In M16 If the sum (F8:L13) > 40 then M17 = 40, If the sum (F8:L13) <or = 40 then M17 = F8:L13

Next:

If M17 > 40 then M20 = Sum (F8:L13)-40, If M17 < or = 40 then M17 = 0

[#2]

David,

a formula cannot change the value of another cell. So the formula will be in M17:

=MIN(40,SUM(F8:L13))

[#3]

In addition to Detlef's observation, I also note that there may be a logical error too in your NEXT formula.

The first formula in M17 will not exceed 40 as you have asked for it to be fixed to 40 if the SUM > 40, but your second formula calls for M17 > 40 which will never happen. So your value in M20 will always be 0.

I've attached a mock up of your requirements with working formulas.

If you're able to please clarify your requirements, we will try to assist you.

Cheers

Damien