# Solved Amount greater than, but less than July 28, 2014 at 14:02:58
Specs: Windows 7
 What is the Excel 2010 formula for the following scenario:Income is \$162,000. I need to calculate the amount that is greater than \$90,000, but less than \$140,000. See More: Amount greater than, but less than July 29, 2014 at 06:18:37
 MRSESQ,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(B2>140000,50792,IF(B2>89208,B2-89208,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:``` A B 1 140000 89208 2 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,000message edited by DerbyDad03

#1 July 28, 2014 at 14:07:05
 I think I must have misunderstood your question because this appears trivial to me. It's just the difference between \$140,000 and \$90,000, isn't it? In other words, \$50,000. So, as an Excel formula, it's just the value \$50,000.

Report •

#2
July 28, 2014 at 14:11:40
 My problem is that the income amount, although I used \$162,000 in my example, can be any amount, including (and especially) an amount less than the \$90,000 amount, or it could be greater than \$90,000, but less than \$140,000. I need to cover all income possibilities and only display what is greater than \$90,000, and if the income amount is greater than \$140,000, only the amount between \$90,000 and \$140,000, but if the income is less than \$140,000, only the amount between \$90,000 and the income amount entered (such as \$132,000 for example). This is probably very elementary for most Excel users, but I am a self-taught user trying to plow my way through what is above my experience/skill level.

Report •

#3 July 28, 2014 at 16:00:00
 Not sure what your after, please post a small sample of your work sheet, with ROW numbers and COLUMN Letters, after reading this How-ToIt explains how to use the < pre > tags to post data in these forums:http://www.computing.net/howtos/sho...A before and after is always helpful.MIKEmessage edited by mmcconaghy

Report •

Related Solutions

#4
July 28, 2014 at 16:12:05
 ```YEAR ANNUAL INCOME INCOME GREATER THAN \$89,208, BUT LESS THAN \$140,000 2013 2014 2015 2016 ```

Report •

#5 July 28, 2014 at 16:23:32
 Not sure I understand what your looking for,is this it?So you have this:```YEAR ANNUAL INCOME INCOME > \$89,208, < \$140,000 2013 \$162,000 \$72,792.00 ```Like I mentioned, a before and after of what your looking for is helpful.MIKEmessage edited by mmcconaghy

Report •

#6
July 28, 2014 at 16:59:12
 Mike, I really appreciate your attempting to help me! What I've been poorly trying to says is I need a formula to calculate the amount that falls between \$89,208 and \$140,000. I've taken another stab at trying to demonstrate what I'm looking for with some actual data:```YEAR ANNUAL INCOME INCOME GREATER THAN \$89,208, BUT LESS THAN \$140,000 2013 \$98,000.00 \$8,792.00 2014 \$145,000.00 \$50,792.00 2015 \$162,000.00 \$50,792.00 ```

Report •

#7 July 28, 2014 at 17:07:25
 OK, that works for the Greater the \$89,208, but what do you want to do with the Less than \$140,000Somthing like this:```YEAR ANNUAL INCOME INCOME > \$89,208, < \$140,000 2013 \$98,000.00 \$8,792.00 \$42,000.00 2014 \$145,000.00 \$55,792.00 2015 \$162,000.00 \$72,792.00 ```MIKEhttp://www.skeptic.com/

Report •

#8
July 28, 2014 at 17:13:38
 Darn--I couldn't figure out how to add the column letters, so my example was unclear. There are just three columns--A is YEAR, B is ANNUAL INCOME and C is INCOME > \$89,208 BUT < \$140,000.

Report •

#9 July 28, 2014 at 17:36:09
 So your sheet look like this:``` A B C ANNUAL INCOME 1) YEAR INCOME > \$89,208 & < \$140,000 2) 2013 \$98,000.00 \$8,792.00 3) 2014 \$145,000.00 \$55,792.00 4) 2015 \$162,000.00 \$72,792.00 ```I still don't understand what you are trying to do.98,000 is greater than 89208 and less then 140000while145000 is greater that 89208 and greater than 140000while 162000 is greater that 89208 and greater than 140000 I need to calculate the amount that is greater than \$90,000, but less than \$140,000.The only Income that qualifies for BOTH calculations is the 98000What am I missing? MIKEmessage edited by mmcconaghy

Report •

#10 July 29, 2014 at 06:18:37
 MRSESQ,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(B2>140000,50792,IF(B2>89208,B2-89208,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:``` A B 1 140000 89208 2 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,000message edited by DerbyDad03 