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. |
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 |