Enter formula that uses values from other cells

[charliedog]

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.

 

[Bernard Liengme]

Sounds like a task for VLOOKUP but we need a clearly statement of the
problem
best wishes

 

[John C]

How does Male, 50, Mild = 100?
and how does Mall, 55, Mild = 110?

 

[John C]

er, not mall, male, lol

 

[charliedog]

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.

 

[John C]

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

 

[charliedog]

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]

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.

 

[charliedog]

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]

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,AGE>89)

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.

 

[charliedog]

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]

No problem. Thanks for the feedback. I'll check this post again in a couple
of days if you still have further questions.