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

Report •


✔ Best Answer
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,000


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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


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

http://www.skeptic.com/

message 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,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/


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


Report •

#10
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


Report •

#11
July 29, 2014 at 08:35:53
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.


Report •

Ask Question