jbf frylock

[jbf frylock]

Need some help. This is the formula I have now. It figures a salary range
and a categorilcally eligible logic test. If a household with x number of
people (A10) makes n they are free or reduced or paid. The differnce for
each increment is 4200 for the free category and 6000 for the reduced and
paid category.

=IF(L3<=(A10*4200+8203),"Free",IF(L3<=(A10*6000+11674),"Reduced","Paid"))

What do you do if the difference isn't the same. What if x = 1 and on the
next cell n = 2 and the jump from income is 1000 to 2004 and then for a
houselhold of 3, the income is 3007. Then the rest of the household numbers
have a difference of 1004. How do I work the difference of 1003 into an
equation where all of the other amounts are 1004? I was told to use: A10 =
number of people. L4 = income for the people, 353 is the difference and 503
is the difference. What if the diffference was 354 for all but one (353) and
503 for all but one (504)? this is the equation I've been playing with.

=IF(L4<=684+(353*A10)+A10,"Free",IF(L4<=973+(503*A10)+A10,"Reduced","Paid"))

 

[jbf frylock]

Yes, apparently it isn't easy to explain. Just need a logic test to pop up 3
different results. 1)Free category 2)Reduced category 3)paid category.
These will be determined by income. It would be easy if they all had a
simple relationships, but they don't. The categories are determined by
income and number of people that income includes. So 1 situation might be a
household of 3 with a monthly income of 2500, another might be a household of
5 with a monthly income of 3500. Based on the salary and number of people it
would place them in a category. I have this figured out for part of my
spreadsheet. Works great because there is a constant and the increments are
always the same. I need to figure out how to calculate the same thing when
the increments aren't the same.

Here is the formula for the first part of spreadsheet. D10= total income,
A10 = household size, 4238 = increment, 8203 = constant, 6031 = increment and
11674 = constant. Need a formula that does this below, but with a variable
increment.

=IF(D10<=(A10*4238+8203),"Free",IF(D10<=(A10*6031+11674),"Reduced","Paid"))

 

[Pete]

Jeff,

I suspect that whoever has given you the new numbers for the table
(your boss?) has made a simple mistake in one of them that has
propagated through.

When you first posed this question in December, my instinct was to use
a lookup table, but then I spotted a simple relationship between the
numbers in the table which made the table redundant. If you definitely
have to stick with these new numbers, then I would suggest a table this
time. Assume you have the following table occupying cells N1 to P8
(I've had to guess the second set of values - correct as necessary).

1 1037 1476
2 1390 1979
3 1744 2483
4 2097 2986
5 2450 3489
6 2803 3992
7 3156 4495
8 3509 4998

The following formula will give you "Free", "Reduced" or "Paid" to suit
your criteria:

=IF(L4<VLOOKUP((A10+1),N1:O8,2,0),"Free",
IF(L4<VLOOKUP((A10+1),N18,3,0),"Reduced","Paid"))

This is all one formula, and assumes L4 is current income and A10 is
the number in the household, as in your earlier posting. This will only
cope with up to 7 in the household - if you have more then you will
need to extend the table down and adjust the references to O8 and P8 in
the formula.

Hope this helps this time - you can always apply it to the previous
situation.

Pete

 

[Pete]

I'm a bit confused - do these values represent the largest amount that
can be earned? In other words, can a household of 1 earn up to (and
including) 1037 and still be regarded as "Free", or up to 1476 and be
"Reduced"? If so, you need this amended formula:

=IF(A10<1,"Not valid",IF(L4<=VLOOKUP(A10,N1:O8,2,0),"Free",
IF(L4<=VLOOKUP(A10,N18,3,0),"Reduced","Paid")))

This will cover households up to 8 - extend the table and amend the
ranges as described earlier for more.

Pete