Forum Discussion

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...

microsoft 365

PeterBartholomew1

Jul 23, 2023

Alternative

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

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

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

Riny_van_Eekelen
Platinum Contributor
Jul 22, 2023
Yousef70 What should be the result then? And why?
- Yousef70
  Brass Contributor
  Jul 22, 2023
  Dear Riny ...
  
  thanks for your help
  
  i have tried the following and it did work
  
  =max(0,O18,P18,sum(O18:P18))
  
  it did give the needed result of one or ether sum of positive cells
  
  Thank you Sir you have been very helpful
  - Riny_van_Eekelen
    Platinum Contributor
    Jul 22, 2023
    Yousef70 GReat, though I don't understand that formula.