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+11 674),"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*A 10)+A10,"Reduced","Paid")) |
jbf frylock
|
jbf frylock
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),N1:P8,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 |
jbf frylock
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,N1:P8,3,0),"Reduced","Paid"))) This will cover households up to 8 - extend the table and amend the ranges as described earlier for more. Pete |
All times are GMT +1. The time now is 07:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com