SOLVED

Help Correcting a Formula

Copper Contributor

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

 

 

5 Replies
best response confirmed by Steven560 (Copper Contributor)
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))

 

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.

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

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

@Steven560 

A bit shorter.

=MAX(0,A4-F49)

 

1 best response

Accepted Solutions
best response confirmed by Steven560 (Copper Contributor)
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))

 

View solution in original post