If the table that you posted in Response 6 is what you are looking for, then a Nested IF formula can be used.
Since the difference between $140K and $89,208 is always going to be $50,792, first check for Annual Salary >$140K. If the Salary is >$140K, the result should be $50,792. If the salary is <$140K, then check for Salary >$89,208 and compute the difference. If the salary is <$89,208, then (I assume) the result should be $0.
If your criteria (e.g. $140K) might change in the future, you may not want to hardcode the values into the formula. If you put your criteria in cells, you can reference those cells in the formula. That way, should the criteria change (e.g. $140K changes to $150K) you would only need to change the criteria cells and leave the formulas alone:
1 140000 89208
3 Annual Income Between
4 Salary Limits
5 $98,000 =IF(B5>$A$1,$A$1-$B$1,IF(B5>$B$1,B5-$B$1,0))
6 $145,000 =IF(B6>$A$1,$A$1-$B$1,IF(B6>$B$1,B6-$B$1,0))
7 $162,000 =IF(B7>$A$1,$A$1-$B$1,IF(B7>$B$1,B7-$B$1,0))
If you want to replace the word "Limits" in B4 with the actual values from A1 and B1, you can use this formula in B4:
=">" & TEXT(B1,"$#,##0")&" & " & "<" & TEXT(A1,"$#,##0")
B4 will now show the following, but will change if A1 and/or B1 are changed.
>$89,208 & <$140,000
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03