Formulae or Lookup Tables?

[OverKnight]

I am trying to create a spreadsheet that will calculate medical predicted values. Which prediction equation to use is based upon gender, age and race. I think the best way to do this is to have the sheet first decide which equation should be used, and then use the correct equation to perform the calculation. Gender (of course) has two variables, male or female. Age has three variables: pediatric, adolescent and adult. Race has four variables: Asian, Black, Caucasian and Mexican. This means there could be a total of 18 equations (the equations themselves utilize gender, age and height as variables). What is the best way to do this? Can a formula be used for all of these values, or would lookup tables be the best approach? I was able to create a lookup table that looks at the gender, age and race entries, with the result being a text description, i.e., Pediatric Caucasian Male, Asian Adult Female, etc., but I don’t know how to “point” these results from the lookup table towards the correct equation. I don’t know which is the best approach; can anyone show me how to do this?

Thank you - please let me know if this isn't clear.

[FDibbins]

knee-jerk reaction would be to use a helper column to combine those 3, and then use a vlookup or index match, based on the helper, to return values from a table

[daffodil11]

Are the values for each descriptor currently in a cell by themselves or are they entered into a string?

[OverKnight]

I think what you're suggesting is what I'm trying to do, FDibbins. I don't know how to do this.
Daffodil11, the values are in a cell be themselves. For example, the text result from the lookup table is in E2, and all of the individual equations are in J2:J19.

Thank you.

[FDibbins]

do you have a dummy sample workbook to share?

[daffodil11]

I'm assuming the equation is the same and only the values for each descriptor changes?

So, if Male = 5, Adult = 10, and Asian = 20, then the equation might be 5*10/20 or whatever.
If so, you can just make a table of values and vlookup the numbers to pop in the equation.
medicinestuff.xlsx

[OverKnight]

I've attached a sample spreadsheet. This sample only has Black, Caucasian and Hispanic races, and there is no differentiation between the pediatric and adolescent equations (but there are with other equation sets I'll be using). I've listed the equations I'll be using for this group; note that the parentheses may not be correct (I just pasted them in).

I just realized there's a problem with my lookup table; the age for adult males begins at age 20 while the age for adult females begins at age 18 . Any suggestions for this?

Thank you.

[FDibbins]

OK I think you are making this way too complex

I assume that your formulas are based on certain combinations of those 3 categories, so I would suggest you have a set of 3 tables, 1 for each category. Then in 1 formula (or maybe 3, each in a helper), yuu calc the value for each part, and then add them together

so for instance, for Male, you have a value, for Caucasian, you have a value etc. we can put something together that will grab the values based on those criteria, but you will need to put the tables together for me

[OverKnight]

Thank you, FDibbins. I do, at times, have a predilection to make things more complicated than necessary...

I'm not sure if I'm following you. What do you mean by "category"? Do you mean age, gender and race? As in the example, there are four age ranges (the adolescent range changes based on gender), three races and two genders. On the attached sheet, columns H and I list the gender-race-age categories and the associated equations. I've modified this sheet to include where the equation result will be (F2). The issue is selecting the correct equation based upon the data in A2:C2, and then displaying the result in F2 (data in B2 and D2 are used in the equations).

Thanks again.

[daffodil11]

What he's asking is if the equations can be broken up into components, that can be then called upon.

If the Black part of the equation = (.999*2) we can just store this in a table. The same would go for all equation components.

We could then use a series of vlookups that would calculate Black+Age/Gender etc without using the specific predefined equations.

[OverKnight]

I see. Unfortunately, no, the equations do not have an "age section", a "race section" and a "gender section". Based upon the age, gender and race, I need the sheet to be able to select the correct equation, and then solve it.

[daffodil11]

I have a new idea. We can build a table where: column1 = gender, column2 = race, column3 = ageterm, column4 = associated equation.

Then nested IFs or SumProduct to find the values from the list of people, to the associated equation based on qualifiers.

[OverKnight]

That's kind of where I was initially headed. I built the table where the result is the name of the equation, but I always have a mental block with long nested IFs. Isn't there a limit of seven arguments in nested IFs?

[David_Schop]

What about using formula labels? You already get your Text string to use for the vlookup.

I've started what the solution could look like. One thing I had to change was rather than spelling out the formula, I had to evaluate the formula.


PredHelpSolution.xlsx

[OverKnight]

Thank you, David. What you did was exactly what I was trying to do. I clearly need to brush up on the syntax for lookup tables; it's so somple when someone else does it for you first!
Thank you also, FDibbins and daffodil11.