Range of numbers in a formula...PLEASE HELP!!!

[jbf frylock]

I'm making an eligibility chart based on income. If 1 person (equals a
household) makes between $0 and $1000 (household income) they are in "x"
category. if that person makes between $1001 and $2000, they are in "y"
category. If that person exceeds $2000, they are in "z" category. So I have
3 categories they can be included in (x=Free, y=reduced, z=paid...it isn't
just a true and false statement). The tricky part is the number of people in
the household. 2 to infinite # of people making in the range of $0-$1000
need to be in "free" category. 2 cells are being drawn from. A1 is the
number of people, A2 is the income, A3 is the formula for figuring their
eligibility. I need A3 to say "Free", or "Reduced", or "Paid" as the end
result of the data. Thank you for any help you may provide.

 

[Pete]

I'm not sure what the effect of the number of people is on the higher
incomes - could you explain that part a bit more?

A lookup table as follows will allow you to get the categories from the
incomes:

0 Free
1001 Reduced
2001 Paid

I can explain how to use it when I understand about the number of
people.

Pete

 

[jbf frylock]

Thanks for the quick reply Pete, I'm driving myself crazy with this. Let me
see if I can explain it better.

The chart will be used to figure categories without using a calculator for
income conversions or an income eligibility table as a reference to tell me
what category a family falls in. So when I enter in A1 (household size) and
then enter the income amount in A2, I need A3 to tell pop up and read "FREE",
"REDUCED, or "FULL PRICE"
This is a rough mock-up of what I need to make a spreadsheet of.
Free Reduced
household size annual inc. annual inc.
1 $10,000 $15,000
2 $14,000 $20,000
3 $18,000 $25,000
4 $22,000 $30,000
5 $26,000 $35,000
6 $30,000 $40,000
7 $34,000 $45,000
8 $38,000 $50,000

If you look at a household of 4 and they make 15,000. They are under the
22,000 mark, so they are in the free category. If they made 22,001...they
would be in the reduced category because they are $1 over the cutoff. If
this family exceeded 30,000, they would be Full Price. I need to be able to
enter in a spreadsheet for example in A1 (household) is 4...A2 (income) is
$24,618...A3 would then pop up and say "REDUCED". I need the range because
theoretically the household of 4 could make $0-$22,000...or
$22,001-$30,000....or might make $30,001-$$$$$$$. A houshold of 8 may only
make 25,000. That would put them in the FREE category since the cutoff for 8
is 38,000. If they made 38,001, it would put them in the reduced category.

I am stumped as to how to write this formula. I've been trying to use
=IF(AND(....

 

[Pete]

Okay, there is a simple relationship between the threshold levels in
your example, so all you will need is the following formula in cell A3:

=IF(A2<=(A1*4000+6000),"Free",IF(A2<=(A1*5000+10000),
"Reduced","Paid"))

assuming that A1 contains the number of people and A2 is the income.

Try that out with some example values.

Pete

 

[jbf frylock]

Thanks Pete, this is so much simpler than what I was trying to do. You are a
life saver. One more question if you don't mind.

Why 4000+6000 or 5000+10000. I have different numbers that will need to be
used in the chart.

Free Reduced
household size annual inc. annual inc.
1 $12441 $17,705
2 $16,679 $23,736
3 $20,917 $29,767
4 $25,155 $35,798
5 $29,393 $41,829
6 $33,631 $47,860
7 $37,869 $53,891
8 $42,107 $59,922

I also need to add $4,238 for each additional member in the FREE category
and $6,031 for each additional in the REDUCED. So if there were 15 in the
free it would add 4238 to income for each additional member.

 

[jbf frylock]

nevermind pete, I figured it out. Thanks so much for your time.

 

[Pete]

Just for the benefit of others, then...

Though your numbers in the second table look a bit messier, they follow
a similar relationship - the difference between each value in your
first threshold is 4,238 (was 4,000) with a constant value of 8,203
(was 6,000). Presumably, there has been some kind of cost-of-living
adjustment. For your second threshold the difference is 6,031 (was
5,000) with a constant of 11,674 (was 10,000), so you can substitute
these new values for the old at the appropriate places. The formula
will cater for any size of household.

If the numbers are likely to change in the future, you might like to
record them in the sheet and refer to the cells in the formula. For
example, if you do a little table in cells C1 to E2 as follows:

Constant 8203 11674
Increment 4238 6031

then you can change the formula in A3 to:

=IF(A2<=(A1*D2 + D1),"FREE",IF(A2<=(A1*E2 + E1),
"REDUCED","PAID"))

In future you only need to change the values in the table to keep up to
date.

Pete

 

[jbf frylock]

Hope you are still around Pete.

What if the difference isn't the same? what if it jumps from 1037 for a
household of 1 to 1390 for household of 2, then 1744 for a household of 3.
The difference goes from 353 to354, but down the list there may be a
difference of 356?

 

[Pete]

Yes, I'm still here. Again, the difference between these numbers has a
simple relationship - it is:

353 x N + N,

where N is the number in the household. I would suggest, therefore,
that your sequence continues 2098, 2452, 2806 etc, and the overall
relationship is:

= 682 + (353 * N) + N

Actually, this is one less than your value for N=1, but the other
values are the same.

Pete

 

[jbf frylock]

Little confused on the formula. I understand the algebra, but don't quite
know how to put it in a formula...this is an example of what I'm dealing
with. The difference on all of these is 353 except for the jump from 1390 to
1744. That one is 354.
Category 1 Category 2
Household Inc.
1 1037
2 1390
3 1744
4 2097
5 2450
6 2803
7 3156
8 3509


Every additional household after this is increased by 354. I don't know how
to approach it when the relationship doesn't involve the same amount.

this is what I have right now.
=IF(L4<=683+(353*A10)+A10,"Free",IF(L4<=973+(503*A10)+A10,"Reduced","Paid"))

L4 = income
A10 = household size

Thanks for any help you can provide and have provided.