MrExcel Publishing
Your One Stop for Excel Tips & Solutions

NEED HELP WITH FORMULAS!


Posted by Yakisoba on November 09, 2001 1:12 AM

I don't really know how to put it in words but the ultimate result I would like is---If someone makes $19,000.00 annualy, and have 2 or more people in the household, then they are eligible for a benefit. There is BTW a table with fixed figures that I will base my findings on. I have already made a calculator to enter all the household income and has a cell that has them all annualized, the next result I would like to see is if the family is eligible or not eligible for the benefit. I really hope that someone is understanding what I need help on and can help me....please, please...thank you ever so much


Posted by Tom Urtis on November 09, 2001 4:53 AM

Let's say in your table, you have the annualized household income located in cell A10, and that in cell B10 you enter the household's # of people. I bet your reference to $19,000 does not mean exactly $19,000...you probably mean $19,000 and above, or from $0 up to $19,000. Assuming the latter (less than or equal to $19,000), enter this formula in the cell of your choice:
=IF(AND(A10<=19000,B10>=2),"Eligible","Not eligible").

Substitute "Eligible" and "Not eligible" with dollar figures or other phrases that make more sense to you.

Hope this helps.

Tom Urtis


Posted by Aladin Akyurek on November 09, 2001 5:05 AM

Care to elaborate a bit more? Or send a copy of your workbook along with some examples of expected results?

Aladin

Posted by Luke on November 09, 2001 5:41 AM

I have a routine in an excel worksheet which analises the entire income of a person, compares the entire income with 5 tax tarives eg 0-7999 = 0 %tax, 8000-16999 = 32.65 % tax, 17000-49999 = 36.95% tax etc, with the possibility to change values as you please. you can then take each tax payment, add the and subtract them from the total income and you've got the net income.

basic formula is:
if($C2>B$7;ALS($C2>C$7;C$7-B$7;$C2-B$7);0)*C$6
where $c2 = income of one person or as you wish the total income of all persons involved, b$7 and c$7 are the low and high borders of tax e.g. 8000-17000, and c$6 the tax percentage. If there is and income within the tax range it will then be calculated.

If you want, I can mail this portion of the worksheet if it would help you.

Posted by Yakisoba on November 11, 2001 2:33 AM

I would like to say thank you to the responders to this post. Tom, you hit the nail on the head. The next question is, can I use a whole string of formula in one cell. You see the income will change and so will the number of people in the household. Once the income is calculated and they tell me the number of people in the household, I would normally look at my income eligibility table and then make my assesment. I understand that one is limited to 7 arguments for one cell. I can deal with that, there is actually 8 in the eligibility table but the likelyhood of even getting there is pretty null. If what I am asking is not possible then I think I have another way of doing it. Make a column for each number of people table. Thanks once again.
Yakisoba