Help Correcting a Formula

[Steven560]

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

[#2 · Accepted solution]

@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))

[#3]

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.

[#4]

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

[#5]

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

[#6]

@Steven560 

A bit shorter.

=MAX(0,A4-F49)