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

"Pete" wrote:

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+1000 0),
"Reduced","Paid"))

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

Try that out with some example values.

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

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" wrote:

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

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

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*A 10)+A10,"Reduced","Paid"))

L4 = income
A10 = household size

Thanks for any help you can provide and have provided.


"Pete" wrote:

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