Nest IF....maybe? IF/AND??

[Leigh]

I tried to post this once before but I realize now that I need to include
more detailed information.

I have a sheet with multiple tenant/rental information. In column G is a
move-in date. Column H has the move-in income and Column J has the household
size (1-8).
In Column I, I need to know the percent of median income at move-in.
I need the formula to analyze the data in all columns: G, H & J. If there is
1 person in the household, and they made less than $13800 a year at move in
and they moved in between 1995 and 2000, then the cell needs to read "30%".
The data is found on another table within the workbook. If that one person
made between $13801 and $23050, then the cell should read "50%". For the
years 2001-2007 there are separate tables for each year so the formula would
only have the one year to look at. I have been trying to figure this formula
out for WEEKS and I am just at my wits end.
In former IF formulas, I've had trouble with income around 50% and so have
used the ROUND feature to 2 places.

To say that any help would be appreciated is an understatement. Please help!

Thank you one and all for your time and effort on my behalf,
Leigh

 

[Mike Anas]

I'm not sure I understand what you mean by

"The data is found on another table within the workbook. "

Are you saying that you are looking up the values, such as 30% and
50%, from the other tables, depending upon what the year is?

 

[T. Valko]

It sounds like you have multiple lookup tables and the one to use depends on
the move-in year.

How to do this depends on how the table(s) are setup. You may even be able
to reduce multiple tables down to a single table which would make things
easier!

I can (probably) fix this for you but it would be much easier if I could
actually see the file. Any chance you can send a copy of the file to me? If
so, I'm at:

xl can help at comcast period net

Remove "can" and change the obvious. Also of note, I use Excel 2002 so I
need files in the *.xls format.

 

[Leigh]

I have the main sheet that I'm working on. The median income table for 2007
is on a separate sheet named 2007. Likewise for 2006-2000.

 

[Leigh]

I cannot email you the file for confidentiality reasons. I wish I could!

The median income tables are set up pretty basic. column A is Number in
Household, 30%, 50%, 80%, Median. Column B is 1 and then the amounts for 30%
etc. Column C is 2 and follows the same pattern as Column B through number in
household being 8. The whole range is A1-I5.

Does that help at all??

 

[Mike Anas]

I agree it's hard to understand precisely what you're trying to do
without seeing the file. Could you create a copy of the file, delete
the confidential data, and send the file along with one row showing
the desired outcome?

 

[T. Valko]

Does that help at all??

It's a start!

If...1 person...made less than $13800...then the cell needs to read "30%".
If that...one person made between $13801 and $23050...the cell should read
"50%".

So, how do you equate <13800 to 30% and 13801 - 23050 to 50% ?

BTW, you probably meant: <=13800.