Forum Discussion

Copper Contributor

Jul 24, 2022

Solved

Help Correcting a Formula

I am trying to use this formula and Excel keeps returning an error. Any help in identifying the error would be very appreciated. =IF(A4="","",IF(SUM(A4-F49)>0),0,IF(SUM(A4-F49)>0),(SUM(A4-F49))) ...

Formulas and Functions

Riny_van_Eekelen
Jul 24, 2022
Steven560 The error occurs due to incorrect placement of several brackets.
A correct syntax would be (note that you don't need all the SUM functions):

=IF(A4="","",IF(A4-F49>0,0,IF(A4-F49>0,A4-F49)))

But, this formula makes no sense as you include two conditions IF(A4-F49>0,....... . You probably want the first one to be IF(A4-F49<=0,...... .

And then you could just write: =MIN(0,A4-F49)

Or, if you must return a blank if A4 is blank:
=IF(A4="","",MIN(0,A4-F49))

Riny_van_Eekelen

Platinum Contributor

Jul 24, 2022

Steven560 The error occurs due to incorrect placement of several brackets.

A correct syntax would be (note that you don't need all the SUM functions):

=IF(A4="","",IF(A4-F49>0,0,IF(A4-F49>0,A4-F49)))

But, this formula makes no sense as you include two conditions IF(A4-F49>0,....... . You probably want the first one to be IF(A4-F49<=0,...... .

And then you could just write: =MIN(0,A4-F49)

Or, if you must return a blank if A4 is blank:

=IF(A4="","",MIN(0,A4-F49))

Steven560

Copper Contributor

Jul 24, 2022

The MIN function doesn't work because I don't want the first condition to be <=0. However, you helped me correct the formula I needed. I worked off of your suggestion and came up with this:

=IF(A4="","",IF(F49="",A4,IF(A4-F49<0,0,IF(A4-F49>0,A4-F49))))

Thank you very much for your help.

Riny_van_Eekelen
Platinum Contributor
Jul 24, 2022
Steven560 Fine with me, but be aware that when A4 = F49, this will return FALSE. Not sure if that is likely to happen, of course. Up to you.
- Steven560
  Copper Contributor
  Jul 24, 2022
  Riny_van_Eekelen
  Already encountered that. I changed the formula to this:
  
  =IF(A4="","",IF(F49="",A4,IF(A4-F49<=0,0,IF(A4-F49>0,A4-F49))))
  
  Working perfectly now. Thanks again.
  - Detlef_Lewin
    Silver Contributor
    Jul 24, 2022
    Steven560
    A bit shorter.
    =MAX(0,A4-F49)