Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
I want to enter a value in one cell that will produce a result based on
values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
Sounds like a task for VLOOKUP but we need a clearly statement of the
problem best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "charliedog" wrote in message ... I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
How does Male, 50, Mild = 100?
and how does Mall, 55, Mild = 110? -- John C "charliedog" wrote: I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
er, not mall, male, lol
-- John C "charliedog" wrote: I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
It's an underwriting table. So for example, a male smoker age 50 with "mild"
tobacco use would warrant an additional rating factor of 100, but a male smoker age 55 with mild tobacco use would warrant an additional rating factor of 110. Note that the second gentleman is 5 years older. I have a chart which gives you the rating factor based on age, gender, and mild/moderate/heavy use. Instead of having the user have to look up the rating factor on the chart and enter it (lots of room for user error), I was hoping to have them just enter the gender and age (which they have to do anyway) and be able to select "Mild", "Moderate" or "Heavy" from a drop down list. Ideally, as soon as they select "Mild", "Moderate" or "Heavy" from the list, Excel will look at the Gender and Age fields and pull in the correct Rating Factor from the chart. Hope that clears it up a little. I'm not terribly familiar with Excel - obviously. "charliedog" wrote: I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
Okay, my question is, how is your chart set up? Are they essentially the same
numbers for age with a variance for gender? Or is there any pattern otherwise at all? Could give sample of data base. i.e.: chart gender is column A, chart age range is column B, factor is column C -- John C "charliedog" wrote: It's an underwriting table. So for example, a male smoker age 50 with "mild" tobacco use would warrant an additional rating factor of 100, but a male smoker age 55 with mild tobacco use would warrant an additional rating factor of 110. Note that the second gentleman is 5 years older. I have a chart which gives you the rating factor based on age, gender, and mild/moderate/heavy use. Instead of having the user have to look up the rating factor on the chart and enter it (lots of room for user error), I was hoping to have them just enter the gender and age (which they have to do anyway) and be able to select "Mild", "Moderate" or "Heavy" from a drop down list. Ideally, as soon as they select "Mild", "Moderate" or "Heavy" from the list, Excel will look at the Gender and Age fields and pull in the correct Rating Factor from the chart. Hope that clears it up a little. I'm not terribly familiar with Excel - obviously. "charliedog" wrote: I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
Sheet 1 is basically a Q&A with several questions, but for this example I am
mainly concerned with three questions. Cell C1 asks the user to select gender: Male or Female, C2 is where they enter age. B5 is where they select Tobacco Use status from a drop down list - the choices are No, Mild, Moderate, Heavy. Sheet 2 contains a chart that assesses a rating/risk factor for tobacco use. The chart is broken up into two sections, one for Males and one for Females. A simplified/partial version is as follows: A B C D 1 Gender/Age Mild Moderate Heavy 2 Male 50 100 105 110 3 Male 51 101 106 111 4 Male 52 102 107 112 5 Male 52 103 108 113 So, on sheet 1, when "Male" is selected in C1, and "50" is entered in C2, and the user selects "Moderate" in B5... Rather than the user having to go to sheet 2 and look up the rating for male/50/moderate, which in the above example would be 105, I would like there to be a formula that would "look it up" automatically and populate B6 with the correct value. So basically, I need a formula for Sheet 1, cell B6 that says: If C1=male, and C2=50, and B5=moderate, then B6=105. But if I change any of these values I need the value in B6 to adjust accordingly. So if I change Sheet 1, Cell B5 to "Mild" I need B6 to automatically change to 100, because that is the appropriate Male/50/Mild rating according to my chart. Thanks for your patience. Hope this clarifies things. "John C" wrote: Okay, my question is, how is your chart set up? Are they essentially the same numbers for age with a variance for gender? Or is there any pattern otherwise at all? Could give sample of data base. i.e.: chart gender is column A, chart age range is column B, factor is column C -- John C "charliedog" wrote: It's an underwriting table. So for example, a male smoker age 50 with "mild" tobacco use would warrant an additional rating factor of 100, but a male smoker age 55 with mild tobacco use would warrant an additional rating factor of 110. Note that the second gentleman is 5 years older. I have a chart which gives you the rating factor based on age, gender, and mild/moderate/heavy use. Instead of having the user have to look up the rating factor on the chart and enter it (lots of room for user error), I was hoping to have them just enter the gender and age (which they have to do anyway) and be able to select "Mild", "Moderate" or "Heavy" from a drop down list. Ideally, as soon as they select "Mild", "Moderate" or "Heavy" from the list, Excel will look at the Gender and Age fields and pull in the correct Rating Factor from the chart. Hope that clears it up a little. I'm not terribly familiar with Excel - obviously. "charliedog" wrote: I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
Okay, so close to getting the info needed. The tobacco use status going
across is one key. But I need to know a little bit more about your gender/age selection. According to the 'sample' data you gave below, Gender and age are in the same column. Is this true? Or are they in separate columns. Also, are all the Male/Age groups first? Or Female/Age groups first? Or is it sorted by age first, and then alternating gender. Also, what is the 'low' age? Are all ages single digits? Or are their 'ranges'? (i.e.: 20-22, 23-25, etc.). What is the 'upper' range? (i.e.: 65, 66, 67, 68+) I will gladly come up with something for you, but this is what I need to know. -- John C "charliedog" wrote: Sheet 1 is basically a Q&A with several questions, but for this example I am mainly concerned with three questions. Cell C1 asks the user to select gender: Male or Female, C2 is where they enter age. B5 is where they select Tobacco Use status from a drop down list - the choices are No, Mild, Moderate, Heavy. Sheet 2 contains a chart that assesses a rating/risk factor for tobacco use. The chart is broken up into two sections, one for Males and one for Females. A simplified/partial version is as follows: A B C D 1 Gender/Age Mild Moderate Heavy 2 Male 50 100 105 110 3 Male 51 101 106 111 4 Male 52 102 107 112 5 Male 52 103 108 113 So, on sheet 1, when "Male" is selected in C1, and "50" is entered in C2, and the user selects "Moderate" in B5... Rather than the user having to go to sheet 2 and look up the rating for male/50/moderate, which in the above example would be 105, I would like there to be a formula that would "look it up" automatically and populate B6 with the correct value. So basically, I need a formula for Sheet 1, cell B6 that says: If C1=male, and C2=50, and B5=moderate, then B6=105. But if I change any of these values I need the value in B6 to adjust accordingly. So if I change Sheet 1, Cell B5 to "Mild" I need B6 to automatically change to 100, because that is the appropriate Male/50/Mild rating according to my chart. Thanks for your patience. Hope this clarifies things. "John C" wrote: Okay, my question is, how is your chart set up? Are they essentially the same numbers for age with a variance for gender? Or is there any pattern otherwise at all? Could give sample of data base. i.e.: chart gender is column A, chart age range is column B, factor is column C -- John C "charliedog" wrote: It's an underwriting table. So for example, a male smoker age 50 with "mild" tobacco use would warrant an additional rating factor of 100, but a male smoker age 55 with mild tobacco use would warrant an additional rating factor of 110. Note that the second gentleman is 5 years older. I have a chart which gives you the rating factor based on age, gender, and mild/moderate/heavy use. Instead of having the user have to look up the rating factor on the chart and enter it (lots of room for user error), I was hoping to have them just enter the gender and age (which they have to do anyway) and be able to select "Mild", "Moderate" or "Heavy" from a drop down list. Ideally, as soon as they select "Mild", "Moderate" or "Heavy" from the list, Excel will look at the Gender and Age fields and pull in the correct Rating Factor from the chart. Hope that clears it up a little. I'm not terribly familiar with Excel - obviously. "charliedog" wrote: I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
Yes, gender and age are in the same column, set up as follows:
MALES: MILD MOD HEAVY Age: 40-44 100 105 110 Age: 45-49 102 107 111 Age: 50-54 105 110 116 Age: 55-59 109 115 122 etc... FEMALES: MILD MOD HEAVY Age: 40-44 95 100 105 Age: 45-49 97 102 108 Age: 50-54 101 108 112 Age: 55-59 107 111 119 etc... The current chart uses an age range (as shown above). Male grouping is first, then female, as shown above. All ages are in double digits, starting at age 40 (lowest range is 40-44) and maxing out at 89 (highest range is 85-89). Thanks again for all your help. It is greatly appreciated. "John C" wrote: Okay, so close to getting the info needed. The tobacco use status going across is one key. But I need to know a little bit more about your gender/age selection. According to the 'sample' data you gave below, Gender and age are in the same column. Is this true? Or are they in separate columns. Also, are all the Male/Age groups first? Or Female/Age groups first? Or is it sorted by age first, and then alternating gender. Also, what is the 'low' age? Are all ages single digits? Or are their 'ranges'? (i.e.: 20-22, 23-25, etc.). What is the 'upper' range? (i.e.: 65, 66, 67, 68+) I will gladly come up with something for you, but this is what I need to know. -- John C "charliedog" wrote: Sheet 1 is basically a Q&A with several questions, but for this example I am mainly concerned with three questions. Cell C1 asks the user to select gender: Male or Female, C2 is where they enter age. B5 is where they select Tobacco Use status from a drop down list - the choices are No, Mild, Moderate, Heavy. Sheet 2 contains a chart that assesses a rating/risk factor for tobacco use. The chart is broken up into two sections, one for Males and one for Females. A simplified/partial version is as follows: A B C D 1 Gender/Age Mild Moderate Heavy 2 Male 50 100 105 110 3 Male 51 101 106 111 4 Male 52 102 107 112 5 Male 52 103 108 113 So, on sheet 1, when "Male" is selected in C1, and "50" is entered in C2, and the user selects "Moderate" in B5... Rather than the user having to go to sheet 2 and look up the rating for male/50/moderate, which in the above example would be 105, I would like there to be a formula that would "look it up" automatically and populate B6 with the correct value. So basically, I need a formula for Sheet 1, cell B6 that says: If C1=male, and C2=50, and B5=moderate, then B6=105. But if I change any of these values I need the value in B6 to adjust accordingly. So if I change Sheet 1, Cell B5 to "Mild" I need B6 to automatically change to 100, because that is the appropriate Male/50/Mild rating according to my chart. Thanks for your patience. Hope this clarifies things. "John C" wrote: Okay, my question is, how is your chart set up? Are they essentially the same numbers for age with a variance for gender? Or is there any pattern otherwise at all? Could give sample of data base. i.e.: chart gender is column A, chart age range is column B, factor is column C -- John C "charliedog" wrote: It's an underwriting table. So for example, a male smoker age 50 with "mild" tobacco use would warrant an additional rating factor of 100, but a male smoker age 55 with mild tobacco use would warrant an additional rating factor of 110. Note that the second gentleman is 5 years older. I have a chart which gives you the rating factor based on age, gender, and mild/moderate/heavy use. Instead of having the user have to look up the rating factor on the chart and enter it (lots of room for user error), I was hoping to have them just enter the gender and age (which they have to do anyway) and be able to select "Mild", "Moderate" or "Heavy" from a drop down list. Ideally, as soon as they select "Mild", "Moderate" or "Heavy" from the list, Excel will look at the Gender and Age fields and pull in the correct Rating Factor from the chart. Hope that clears it up a little. I'm not terribly familiar with Excel - obviously. "charliedog" wrote: I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
Okay, you had said earlier that gender age were in the same cell, I am
assuming such as this: Male 40-44 Male 45-49 Male 50-54 .... then followed by the Female age groups. Note the following: My table is on a tab called Risk, and located in columns A-D, with headers in row 1. My variables are Age, Gender, and Use. Here is the following formula: =IF(OR(Gender="",Use="",Age=""),"",VLOOKUP(Gender& " "&LOOKUP(Age,{0,"40-44";45,"45-49";50,"50-54";55,"55-59";60,"60-64";65,"65-69";70,"70-74";75,"75-79";80,"80-84";85,"85-89"}),Risk!$A$1:$D$22,MATCH(Use,Risk!$A$1:$D$1,0), FALSE)) I first check to see if Gender, Use, or Age are blank. If any of them are blank, I want no data to appear. This is so that false data won't appear (and may trip up the user in to thinking they have answered the necessary questions to retrieve the appropriate number). Then I do a VLOOKUP, but I need to make the lookup value match what is in column A of the Risk tab, so I combine my variable Gender, with an age range. The age range is determined by the lookup that you see there. Any age 45 or below will be the 40-44 range, any age above 85 will be the 85-89 range. Note: I do not know how you handle <40 or 89. You can add clarifications to the initial OR statement in regards to AGE to eliminate pulling any data if you need to. (i.e.: OR(...,AGE<40,AGE89) After I come up with my lookup value of Gender + Age Range (note the space in my vlookup, it needs to be consistent with Risk table as well, if it is 2 spaces, put 2, etc). Then I define the table range, Risk!$A$1:$D$22. Then I need to figure what column it is pulling from. Mild, Mod, or Heavy. Note, the headers must exactly match the choices. (You have Mod 1 place, and Moderate in another, I am assuming these should both say Moderate). Anyway, I hope this helps. If you have any more questions, I'll check back a little later. -- John C "charliedog" wrote: Yes, gender and age are in the same column, set up as follows: MALES: MILD MOD HEAVY Age: 40-44 100 105 110 Age: 45-49 102 107 111 Age: 50-54 105 110 116 Age: 55-59 109 115 122 etc... FEMALES: MILD MOD HEAVY Age: 40-44 95 100 105 Age: 45-49 97 102 108 Age: 50-54 101 108 112 Age: 55-59 107 111 119 etc... The current chart uses an age range (as shown above). Male grouping is first, then female, as shown above. All ages are in double digits, starting at age 40 (lowest range is 40-44) and maxing out at 89 (highest range is 85-89). Thanks again for all your help. It is greatly appreciated. "John C" wrote: Okay, so close to getting the info needed. The tobacco use status going across is one key. But I need to know a little bit more about your gender/age selection. According to the 'sample' data you gave below, Gender and age are in the same column. Is this true? Or are they in separate columns. Also, are all the Male/Age groups first? Or Female/Age groups first? Or is it sorted by age first, and then alternating gender. Also, what is the 'low' age? Are all ages single digits? Or are their 'ranges'? (i.e.: 20-22, 23-25, etc.). What is the 'upper' range? (i.e.: 65, 66, 67, 68+) I will gladly come up with something for you, but this is what I need to know. -- John C "charliedog" wrote: Sheet 1 is basically a Q&A with several questions, but for this example I am mainly concerned with three questions. Cell C1 asks the user to select gender: Male or Female, C2 is where they enter age. B5 is where they select Tobacco Use status from a drop down list - the choices are No, Mild, Moderate, Heavy. Sheet 2 contains a chart that assesses a rating/risk factor for tobacco use. The chart is broken up into two sections, one for Males and one for Females. A simplified/partial version is as follows: A B C D 1 Gender/Age Mild Moderate Heavy 2 Male 50 100 105 110 3 Male 51 101 106 111 4 Male 52 102 107 112 5 Male 52 103 108 113 So, on sheet 1, when "Male" is selected in C1, and "50" is entered in C2, and the user selects "Moderate" in B5... Rather than the user having to go to sheet 2 and look up the rating for male/50/moderate, which in the above example would be 105, I would like there to be a formula that would "look it up" automatically and populate B6 with the correct value. So basically, I need a formula for Sheet 1, cell B6 that says: If C1=male, and C2=50, and B5=moderate, then B6=105. But if I change any of these values I need the value in B6 to adjust accordingly. So if I change Sheet 1, Cell B5 to "Mild" I need B6 to automatically change to 100, because that is the appropriate Male/50/Mild rating according to my chart. Thanks for your patience. Hope this clarifies things. "John C" wrote: Okay, my question is, how is your chart set up? Are they essentially the same numbers for age with a variance for gender? Or is there any pattern otherwise at all? Could give sample of data base. i.e.: chart gender is column A, chart age range is column B, factor is column C -- John C "charliedog" wrote: It's an underwriting table. So for example, a male smoker age 50 with "mild" tobacco use would warrant an additional rating factor of 100, but a male smoker age 55 with mild tobacco use would warrant an additional rating factor of 110. Note that the second gentleman is 5 years older. I have a chart which gives you the rating factor based on age, gender, and mild/moderate/heavy use. Instead of having the user have to look up the rating factor on the chart and enter it (lots of room for user error), I was hoping to have them just enter the gender and age (which they have to do anyway) and be able to select "Mild", "Moderate" or "Heavy" from a drop down list. Ideally, as soon as they select "Mild", "Moderate" or "Heavy" from the list, Excel will look at the Gender and Age fields and pull in the correct Rating Factor from the chart. Hope that clears it up a little. I'm not terribly familiar with Excel - obviously. "charliedog" wrote: I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
Thanks so much for the help. My set up is a little different, but now that I
see how the formula is constructed I should be good to go. THANK YOU. "John C" wrote: Okay, you had said earlier that gender age were in the same cell, I am assuming such as this: Male 40-44 Male 45-49 Male 50-54 ... then followed by the Female age groups. Note the following: My table is on a tab called Risk, and located in columns A-D, with headers in row 1. My variables are Age, Gender, and Use. Here is the following formula: =IF(OR(Gender="",Use="",Age=""),"",VLOOKUP(Gender& " "&LOOKUP(Age,{0,"40-44";45,"45-49";50,"50-54";55,"55-59";60,"60-64";65,"65-69";70,"70-74";75,"75-79";80,"80-84";85,"85-89"}),Risk!$A$1:$D$22,MATCH(Use,Risk!$A$1:$D$1,0), FALSE)) I first check to see if Gender, Use, or Age are blank. If any of them are blank, I want no data to appear. This is so that false data won't appear (and may trip up the user in to thinking they have answered the necessary questions to retrieve the appropriate number). Then I do a VLOOKUP, but I need to make the lookup value match what is in column A of the Risk tab, so I combine my variable Gender, with an age range. The age range is determined by the lookup that you see there. Any age 45 or below will be the 40-44 range, any age above 85 will be the 85-89 range. Note: I do not know how you handle <40 or 89. You can add clarifications to the initial OR statement in regards to AGE to eliminate pulling any data if you need to. (i.e.: OR(...,AGE<40,AGE89) After I come up with my lookup value of Gender + Age Range (note the space in my vlookup, it needs to be consistent with Risk table as well, if it is 2 spaces, put 2, etc). Then I define the table range, Risk!$A$1:$D$22. Then I need to figure what column it is pulling from. Mild, Mod, or Heavy. Note, the headers must exactly match the choices. (You have Mod 1 place, and Moderate in another, I am assuming these should both say Moderate). Anyway, I hope this helps. If you have any more questions, I'll check back a little later. -- John C "charliedog" wrote: Yes, gender and age are in the same column, set up as follows: MALES: MILD MOD HEAVY Age: 40-44 100 105 110 Age: 45-49 102 107 111 Age: 50-54 105 110 116 Age: 55-59 109 115 122 etc... FEMALES: MILD MOD HEAVY Age: 40-44 95 100 105 Age: 45-49 97 102 108 Age: 50-54 101 108 112 Age: 55-59 107 111 119 etc... The current chart uses an age range (as shown above). Male grouping is first, then female, as shown above. All ages are in double digits, starting at age 40 (lowest range is 40-44) and maxing out at 89 (highest range is 85-89). Thanks again for all your help. It is greatly appreciated. "John C" wrote: Okay, so close to getting the info needed. The tobacco use status going across is one key. But I need to know a little bit more about your gender/age selection. According to the 'sample' data you gave below, Gender and age are in the same column. Is this true? Or are they in separate columns. Also, are all the Male/Age groups first? Or Female/Age groups first? Or is it sorted by age first, and then alternating gender. Also, what is the 'low' age? Are all ages single digits? Or are their 'ranges'? (i.e.: 20-22, 23-25, etc.). What is the 'upper' range? (i.e.: 65, 66, 67, 68+) I will gladly come up with something for you, but this is what I need to know. -- John C "charliedog" wrote: Sheet 1 is basically a Q&A with several questions, but for this example I am mainly concerned with three questions. Cell C1 asks the user to select gender: Male or Female, C2 is where they enter age. B5 is where they select Tobacco Use status from a drop down list - the choices are No, Mild, Moderate, Heavy. Sheet 2 contains a chart that assesses a rating/risk factor for tobacco use. The chart is broken up into two sections, one for Males and one for Females. A simplified/partial version is as follows: A B C D 1 Gender/Age Mild Moderate Heavy 2 Male 50 100 105 110 3 Male 51 101 106 111 4 Male 52 102 107 112 5 Male 52 103 108 113 So, on sheet 1, when "Male" is selected in C1, and "50" is entered in C2, and the user selects "Moderate" in B5... Rather than the user having to go to sheet 2 and look up the rating for male/50/moderate, which in the above example would be 105, I would like there to be a formula that would "look it up" automatically and populate B6 with the correct value. So basically, I need a formula for Sheet 1, cell B6 that says: If C1=male, and C2=50, and B5=moderate, then B6=105. But if I change any of these values I need the value in B6 to adjust accordingly. So if I change Sheet 1, Cell B5 to "Mild" I need B6 to automatically change to 100, because that is the appropriate Male/50/Mild rating according to my chart. Thanks for your patience. Hope this clarifies things. "John C" wrote: Okay, my question is, how is your chart set up? Are they essentially the same numbers for age with a variance for gender? Or is there any pattern otherwise at all? Could give sample of data base. i.e.: chart gender is column A, chart age range is column B, factor is column C -- John C "charliedog" wrote: It's an underwriting table. So for example, a male smoker age 50 with "mild" tobacco use would warrant an additional rating factor of 100, but a male smoker age 55 with mild tobacco use would warrant an additional rating factor of 110. Note that the second gentleman is 5 years older. I have a chart which gives you the rating factor based on age, gender, and mild/moderate/heavy use. Instead of having the user have to look up the rating factor on the chart and enter it (lots of room for user error), I was hoping to have them just enter the gender and age (which they have to do anyway) and be able to select "Mild", "Moderate" or "Heavy" from a drop down list. Ideally, as soon as they select "Mild", "Moderate" or "Heavy" from the list, Excel will look at the Gender and Age fields and pull in the correct Rating Factor from the chart. Hope that clears it up a little. I'm not terribly familiar with Excel - obviously. "charliedog" wrote: I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Enter formula that uses values from other cells
No problem. Thanks for the feedback. I'll check this post again in a couple
of days if you still have further questions. -- ** John C ** "charliedog" wrote: Thanks so much for the help. My set up is a little different, but now that I see how the formula is constructed I should be good to go. THANK YOU. "John C" wrote: Okay, you had said earlier that gender age were in the same cell, I am assuming such as this: Male 40-44 Male 45-49 Male 50-54 ... then followed by the Female age groups. Note the following: My table is on a tab called Risk, and located in columns A-D, with headers in row 1. My variables are Age, Gender, and Use. Here is the following formula: =IF(OR(Gender="",Use="",Age=""),"",VLOOKUP(Gender& " "&LOOKUP(Age,{0,"40-44";45,"45-49";50,"50-54";55,"55-59";60,"60-64";65,"65-69";70,"70-74";75,"75-79";80,"80-84";85,"85-89"}),Risk!$A$1:$D$22,MATCH(Use,Risk!$A$1:$D$1,0), FALSE)) I first check to see if Gender, Use, or Age are blank. If any of them are blank, I want no data to appear. This is so that false data won't appear (and may trip up the user in to thinking they have answered the necessary questions to retrieve the appropriate number). Then I do a VLOOKUP, but I need to make the lookup value match what is in column A of the Risk tab, so I combine my variable Gender, with an age range. The age range is determined by the lookup that you see there. Any age 45 or below will be the 40-44 range, any age above 85 will be the 85-89 range. Note: I do not know how you handle <40 or 89. You can add clarifications to the initial OR statement in regards to AGE to eliminate pulling any data if you need to. (i.e.: OR(...,AGE<40,AGE89) After I come up with my lookup value of Gender + Age Range (note the space in my vlookup, it needs to be consistent with Risk table as well, if it is 2 spaces, put 2, etc). Then I define the table range, Risk!$A$1:$D$22. Then I need to figure what column it is pulling from. Mild, Mod, or Heavy. Note, the headers must exactly match the choices. (You have Mod 1 place, and Moderate in another, I am assuming these should both say Moderate). Anyway, I hope this helps. If you have any more questions, I'll check back a little later. -- John C "charliedog" wrote: Yes, gender and age are in the same column, set up as follows: MALES: MILD MOD HEAVY Age: 40-44 100 105 110 Age: 45-49 102 107 111 Age: 50-54 105 110 116 Age: 55-59 109 115 122 etc... FEMALES: MILD MOD HEAVY Age: 40-44 95 100 105 Age: 45-49 97 102 108 Age: 50-54 101 108 112 Age: 55-59 107 111 119 etc... The current chart uses an age range (as shown above). Male grouping is first, then female, as shown above. All ages are in double digits, starting at age 40 (lowest range is 40-44) and maxing out at 89 (highest range is 85-89). Thanks again for all your help. It is greatly appreciated. "John C" wrote: Okay, so close to getting the info needed. The tobacco use status going across is one key. But I need to know a little bit more about your gender/age selection. According to the 'sample' data you gave below, Gender and age are in the same column. Is this true? Or are they in separate columns. Also, are all the Male/Age groups first? Or Female/Age groups first? Or is it sorted by age first, and then alternating gender. Also, what is the 'low' age? Are all ages single digits? Or are their 'ranges'? (i.e.: 20-22, 23-25, etc.). What is the 'upper' range? (i.e.: 65, 66, 67, 68+) I will gladly come up with something for you, but this is what I need to know. -- John C "charliedog" wrote: Sheet 1 is basically a Q&A with several questions, but for this example I am mainly concerned with three questions. Cell C1 asks the user to select gender: Male or Female, C2 is where they enter age. B5 is where they select Tobacco Use status from a drop down list - the choices are No, Mild, Moderate, Heavy. Sheet 2 contains a chart that assesses a rating/risk factor for tobacco use. The chart is broken up into two sections, one for Males and one for Females. A simplified/partial version is as follows: A B C D 1 Gender/Age Mild Moderate Heavy 2 Male 50 100 105 110 3 Male 51 101 106 111 4 Male 52 102 107 112 5 Male 52 103 108 113 So, on sheet 1, when "Male" is selected in C1, and "50" is entered in C2, and the user selects "Moderate" in B5... Rather than the user having to go to sheet 2 and look up the rating for male/50/moderate, which in the above example would be 105, I would like there to be a formula that would "look it up" automatically and populate B6 with the correct value. So basically, I need a formula for Sheet 1, cell B6 that says: If C1=male, and C2=50, and B5=moderate, then B6=105. But if I change any of these values I need the value in B6 to adjust accordingly. So if I change Sheet 1, Cell B5 to "Mild" I need B6 to automatically change to 100, because that is the appropriate Male/50/Mild rating according to my chart. Thanks for your patience. Hope this clarifies things. "John C" wrote: Okay, my question is, how is your chart set up? Are they essentially the same numbers for age with a variance for gender? Or is there any pattern otherwise at all? Could give sample of data base. i.e.: chart gender is column A, chart age range is column B, factor is column C -- John C "charliedog" wrote: It's an underwriting table. So for example, a male smoker age 50 with "mild" tobacco use would warrant an additional rating factor of 100, but a male smoker age 55 with mild tobacco use would warrant an additional rating factor of 110. Note that the second gentleman is 5 years older. I have a chart which gives you the rating factor based on age, gender, and mild/moderate/heavy use. Instead of having the user have to look up the rating factor on the chart and enter it (lots of room for user error), I was hoping to have them just enter the gender and age (which they have to do anyway) and be able to select "Mild", "Moderate" or "Heavy" from a drop down list. Ideally, as soon as they select "Mild", "Moderate" or "Heavy" from the list, Excel will look at the Gender and Age fields and pull in the correct Rating Factor from the chart. Hope that clears it up a little. I'm not terribly familiar with Excel - obviously. "charliedog" wrote: I want to enter a value in one cell that will produce a result based on values in other cells. For example: Cell B5 has a drop down list where I select the level of Tobacco use (mild, moderate or heavy). Once I've selected from the list, I want to return a value in C5 that reflects not only my B5 selection, but also the value in C1 (Gender - male or female) and C2 (age). So if C1=Male, and C2=50, when I select "mild" in B5 I want C5 to show "100", but if C2=55 then I want C5 to result in "110." I hope my description makes sense. I would appreciate any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to select cells in column enter data then press enter | New Users to Excel | |||
enter consecutive cells into a formula | Excel Discussion (Misc queries) | |||
Run formula only if values are enter, otherwise leave blank | Excel Worksheet Functions | |||
Is there way to enter multiple values into excel cells w/ a form? | Excel Discussion (Misc queries) | |||
Can I enter a formula using data from cells in seprate worksheets | Excel Worksheet Functions |