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.

✔ 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

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.

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

message edited by mmcconaghy

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

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.00Like I mentioned, a before and after of what your looking for is helpful.

MIKE

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

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

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.

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.00I still don't understand what you are trying to do.

98,000 is

greaterthan 89208 andlessthen 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

message edited by mmcconaghy

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.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History