Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




Range of numbers in a formula...PLEASE HELP!!!
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. 
#2
Posted to microsoft.public.excel.worksheet.functions




Range of numbers in a formula...PLEASE HELP!!!
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 
#3
Posted to microsoft.public.excel.worksheet.functions




Range of numbers in a formula...PLEASE HELP!!!
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 mockup 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" wrote: 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 
#4
Posted to microsoft.public.excel.worksheet.functions




Range of numbers in a formula...PLEASE HELP!!!
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 
#5
Posted to microsoft.public.excel.worksheet.functions




Range of numbers in a formula...PLEASE HELP!!!
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. "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 
#6
Posted to microsoft.public.excel.worksheet.functions




Range of numbers in a formula...PLEASE HELP!!!
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 
#7
Posted to microsoft.public.excel.worksheet.functions




Range of numbers in a formula...PLEASE HELP!!!
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 costofliving 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 
#8
Posted to microsoft.public.excel.worksheet.functions




Range of numbers in a formula...PLEASE HELP!!!
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 costofliving 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 
#9
Posted to microsoft.public.excel.worksheet.functions




Range of numbers in a formula...PLEASE HELP!!!
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 
#10
Posted to microsoft.public.excel.worksheet.functions




Range of numbers in a formula...PLEASE HELP!!!
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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
What formula is used for subtracting a range of different cells f.  Excel Worksheet Functions  
Match then lookup  Excel Worksheet Functions  
Count comma separated numbers, numbers in a range with dash, not t  Excel Discussion (Misc queries)  
Formula checking multiple worksheets  Excel Worksheet Functions  
Formula checking multiple worksheets  Excel Worksheet Functions 