Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
jbf frylock
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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")) "Gary L Brown" wrote: This makes NO sense!!! -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "jbf frylock" wrote: 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")) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|