Forum Discussion

David Matzas's avatar
David Matzas
Copper Contributor
Feb 13, 2018

Need help with formula

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

 

davidm@pdq.net
Formulas & Functions

2 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Feb 13, 2018

    David,

     

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

    =MIN(40,SUM(F8:L13))
    • Damien_Rosario's avatar
      Damien_Rosario
      Silver Contributor
      Feb 13, 2018

      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

Resources