Solved Amount greater than, but less than

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

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.

mmcconaghy 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-To It 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. MIKE http://www.skeptic.com/ message edited by mmcconaghy

YEAR ANNUAL INCOME INCOME GREATER THAN $89,208, BUT LESS THAN $140,000 2013 2014 2015 2016

mmcconaghy 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. MIKE http://www.skeptic.com/ message edited by mmcconaghy

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

mmcconaghy 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,000 Somthing 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 MIKE http://www.skeptic.com/

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.

mmcconaghy 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 140000 while 145000 is greater that 89208 and greater than 140000 while 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 98000 What am I missing? MIKE http://www.skeptic.com/ message edited by mmcconaghy

DerbyDad03 July 29, 2014 at 06:18:37

✔ Best Answer

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,000 Click Here Before Posting Data or VBA Code ---> How To Post Data or Code. message edited by DerbyDad03

Many thanks to DerbyDad03 for your assistance. Your formula solved my problem and I am very grateful! My thanks also to Mike (MMCCONAGHY) for your efforts.