Forum Discussion

Yousef70's avatar
Yousef70
Brass Contributor
Jul 19, 2023
Solved

If formula

Dear Sirs ….

Kindly need your help with below table to distinguish between + or - numbers in the result cell to take + value cell keep in mind the following If formula used in result cell ( if there is a better way , I am open ) :-

=IF(SUM(O18:P18)>=0,SUM(O18:P18),IF(SUM(O18:P18)<=0,"0.000",(IF(SUM(O18:P18)<=0,O18+P18))))

looking forward to your help , thanks in advance

Cell O18

Cell P18

Result cell

10.000

-214.730

0.000

microsoft 365
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Jul 23, 2023

    Alternative

    = LET(
    s, BYROW(data, LAMBDA(d, MAX(SUM(d), 0))),
    VSTACK(s, SUM(s))
  )

33 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 23, 2023

    Yousef70 

    A nice thing about 365 is that it has very little in common with traditional spreadsheet practice :stareyes:

    = LET(
    t, BYROW(data,
      LAMBDA(d,
        LET(
          s, SUM(d),
          IF(s>0, s, 0)
        )
      )
    ),
  VSTACK(t, SUM(t))
  )

     

     

    • Yousef70's avatar
      Yousef70
      Brass Contributor
      Jul 25, 2023

      Dear Peter …

      In the above coding , can you give an alternative to result column positive cell amount even if negative cell amount is higher ?

      for example in above table:-
      column 1 column 2 result
      10 -214 10
      20 5 25
      -45 60 60
      15 -60 15
      110

      as per I am looking for the "honored" payments ( positive cells ) but still need to acknowledge the "not honored" payments ( negative cells ) but not in the result cell

      many thanks in advance

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Jul 23, 2023

      Alternative

      = LET(
    s, BYROW(data, LAMBDA(d, MAX(SUM(d), 0))),
    VSTACK(s, SUM(s))
  )
      • Yousef70's avatar
        Yousef70
        Brass Contributor
        Jul 25, 2023
        Dear Peter …

        Many thanks for your input

        I didn't get the chance to implement the above coding , I will ASAP and inform you about the results
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 22, 2023

    Yousef70 Try this:

    =MAX(0,SUM(O18:P18))

    The above formula returns the higher number of 0 (zero) or the sum of O18:P18. So, if the sum is negative it returns 0, otherwise the sum. Format all cells with three decimals to achieve the end result shown in your example. 

     

    Your formula uses nested IF functions that are not needed and there are several issues.

     

     

    =IF(SUM(O18:P18)>=0,SUM(O18:P18),IF(SUM(O18:P18)<=0,"0.000",(IF(SUM(O18:P18)<=0,O18+P18))))

     

    The first IF checks if the sum is >=0. If that is not the case the sum is less then zero. No need to add another IF that checks if that is indeed the fact. And then the last IF never comes into play and even if it would I believe you would to change <=0 to >=0. But, as said it's irrelevant.

     

    Then, the part "0.000" returns a text string. Probably not something you would want.

     

    • Yousef70's avatar
      Yousef70
      Brass Contributor
      Jul 22, 2023

      Dear Riny ...

      thank you for your help ...

      I have inserted the formula you sent to me but it did not work
      cell O18 is +10
      cell P18 is -214.730
      result cell still at 0.000

      only when cell O18 exceed -214.730 result cell will give positive deference

      looking forward to your help again

Resources