Forum Discussion
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)))
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))
5 Replies
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.
