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

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

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

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?

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

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.


--
Biff
Microsoft Excel MVP


"Leigh" wrote in message
...
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

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

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.

"Mike Anas" wrote:

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?

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

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

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"Leigh" wrote in message
...
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

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

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?

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

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.

--
Biff
Microsoft Excel MVP


"Leigh" wrote in message
...
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??

"T. Valko" wrote:

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.


--
Biff
Microsoft Excel MVP


"Leigh" wrote in message
...
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