Complicated formula?

[Leigh]

Hello everyone....

I need some help. I have a column of dates and a column of income amounts. I
have multiple sheets in the workbook with income tables by year by
percentage. I need to create a formula to look in column F and pull data from
the sheet with the corresponding year. I also need the formula to look at the
income amount (Column G) and determine what percentage it is based on the
table. So if the date is sometime in 1999 and the income is within a certain
range, the cell would print 30%. If the date is in 1999 and the income is in
a higher range of amounts, it would print 50%. Or if the year is 2000 or 2004
or whatever. I only have 3 ranges of income to work with: 30%, 50% and 80%
but I have years 1996-2007.

This seems pretty complicated to me and if it's a huge hassle, please don't
lose any sleep over it. I can always come up with a plan B.

Thank you all for your help in advance.

Leigh

 

[Leigh]

Actually......it also depends on the number of people, not just the amount
and the year. *sigh* I may just have to do it manually.....

 

[iliace]

How are your rates set up? You should have a separate table, that
contains ranges of income and number of people.

Next, you can use named ranges to specify each year's figures. So, on
sheet 1996, you'll name a workbook-wide range called Rates1996, and
use a lookup function to find the range.

It's easier than it sounds, really. With formulas you'll probably get
consistently accurate results, so don't give up quite yet.

 

[Leigh]

Hi iliace,
What I'm working with are median income tables. I need Excel to say "ok, if
Cell X is a 1 (or 2-8) and Cell Y is in year 1999 (or any year between 1996
and 2007) and Cell Z is a yearly income of M, then the percentage of the
yearly income of the median income for that year is L. The median income
tables look like this:
2007
1 2 3 4 etc (person per household, up to 8)
16350 18700 21050 23350 (30% of median)
27250 31150 35050 38950 (50% of median)
41700 47700 53650 59600 (80% of median)
52000 59450 66850 75600 (median income)

So my formula needs to be able to recognize a year in the date in the date
column (F) and the number of people in the household in I column and the
yearly income in column G. So if someone has a date of 5/5/2007 and an income
of 10000 and 2 people in the household, the formula needs to either put the
exact percentage (16% or so) OR the general percentage (30%) in the H column.

It sounds so easy when I write it like that but I'm just not seeing the
formula.

Thanks!
Leigh

 

[pinmaster]

Hi,

it would help if you gave more details as to how your sheets are setup and
how they are named. In the mean time have a look at the YEAR function, this
will tell you the year of the date to lookup: =YEAR(A1), also have a look at
the INDIRECT function, for example, if you had a cell..say (B1) with the date
1/1/1999 and wanted to get the value in A1 from the sheet named "1999" then
you would use something like this: =INDIRECT(YEAR(B1)&"!A1") or if you wanted
to lookup a value in a table in sheet "1999"
:=VLOOKUP(F1,INDIRECT(YEAR(B1)&"!A1:G100"),2,0)...hopefully this will guide
you in the right direction.

HTH
Jean-Guy