• 5 Subscribers
  • 114 Views
-1

enter image description here

I assume x is the number I'm going to analyze and "y" is as "male" or "female" and "z" is as age group. x is a number that the result will be "underweight", healthy " or overweight". but x which is actually the BPI is not the same for male and female. eg, if x = 21 then depends on gender and age it would be categorized as either "underweight", "healthy " or overweight".

 Function BAI(x As Integer, y As String, z As Integer) As String


 If x < 21 And y = "female" And 20 <= z <= 39 Then
    BAI = "UNDERWEIGHT"

 Elseif 22 <= x <= 33 And y = "Female" And 20 <= z <= 39 Then
    BAI = "Healthy"

ElseIf 34 <= x <= 38 And y = "Female" And 20 <= z <= 39 Then
    BAI = "overweight"

ElseIf x >= 39 And y = "Female" And 20 <= z <= 39 Then
    BAI = "OBESE"

ElseIf x <= 23 And y = "female" And 40 <= z <= 59 Then
    BAI = "UNDERWEIGHT"

ElseIf 24 <= x <= 35 And y = "female" And 40 <= z <= 59 Then
    BAI = "Healthy"

ElseIf 36 <= x <= 41 And y = "female" And 40 <= z <= 59 Then
    BAI = "Overweight"

ElseIf x >= 42 And y = "female" And 40 <= z <= 59 Then
    BAI = "OBESE"

ElseIf x <= 25 And y = "female" And 60 <= z <= 79 Then
    BAI = "UNDERWEIGHT"

ElseIf 26 <= x <= 38 And y = "female" And 60 <= z <= 79 Then
    BAI = "Healthy"

ElseIf 39 <= x <= 43 And y = "female" And 60 <= z <= 79 Then
    BAI = "Overweight"

ElseIf x >= 44 And y = "female" And 60 <= z <= 79 Then
    BAI = "obese"

ElseIf x <= 8 And y = "male" And 20 <= z <= 39 Then
    BAI = "Underweight"

ElseIf 9 <= x <= 21 And y = "male" And 20 <= z <= 39 Then
    BAI = "Healthy"

ElseIf 22 <= x <= 26 And y = "male" And 20 <= z <= 39 Then
    BAI = "overweight"

ElseIf x >= 27 And y = "male" And 20 <= z <= 39 Then
    BAI = "OBESE"

ElseIf x <= 11 And y = "male" And 40 <= z <= 59 Then
    BAI = "UNDERWEIGHT"

ElseIf 12 <= x <= 23 And y = "male" And 40 <= z <= 59 Then
    BAI = "Healthy"

ElseIf 24 <= x <= 28 And y = "male" And 40 <= z <= 59 Then
    BAI = "Overweight"

ElseIf x >= 29 And y = "male" And 40 <= z <= 59 Then
    BAI = "OBESE"

ElseIf x <= 13 And y = "male" And 60 <= z <= 79 Then
    BAI = "UNDERWEIGHT"

ElseIf 14 <= x <= 25 And y = "male" And 60 <= z <= 79 Then
    BAI = "Healthy"

ElseIf 26 <= x <= 30 And y = "male" And 60 <= z <= 79 Then
    BAI = "Overweight"

 End If

 End Function

1 answers in total

0
elmer007 Posted at 2017-01-12 13:36:41Z

Here's a new answer given the edited question.

If you're going to go the If-Then-Else route, then I would recommend breaking down your logic into groups. I tried, but I'm not familiar with your field so I may not have done it perfectly:

  1. First, test if the person is male or female.
  2. Second, test to see which range their Age is within.
  3. Third, test to see which range their BPI is within.

Also, for readability, I would recommend renaming your variables to something more meaningful. I renamed "x" to "BPI", "y" to "Gender", and "z" to "Age".


Edit: For the case-sensitive "male" and "female", I've added the UCase function to the code below. It converts a string to all uppercase, which allows it to be compared to an all uppercase string. For example, "MaLe" would be converted to "MALE", and then compared to "MALE". In other words, this should eliminate the case-sensitivity from the gender portion of the function.

Also, for possible spaces before/after the gender, using the Trim function will eliminate them. So, Trim("Male ") becomes just "Male" (no spaces).


Look at the following and see if it's more what you're looking for:

Function BAI(BPI As Integer, Gender As String, Age As Integer) As String
If UCase(Trim(Gender)) = "FEMALE" Then
    If 20 <= Age And Age <= 39 Then
        If BPI <= 21 Then BAI = "UNDERWEIGHT"
        If 22 <= BPI And BPI <= 33 Then BAI = "Healthy"
        If 34 <= BPI And BPI <= 38 Then BAI = "Overweight"
        If BPI >= 39 Then BAI = "OBESE"
    ElseIf 40 <= Age And Age <= 59 Then
        If BPI <= 23 Then BAI = "UNDERWEIGHT"
        If 24 <= BPI And BPI <= 35 Then BAI = "Healthy"
        If 36 <= BPI And BPI <= 41 Then BAI = "Overweight"
        If BPI >= 42 Then BAI = "OBESE"
    ElseIf 60 <= Age And Age <= 79 Then
        If BPI <= 25 Then BAI = "UNDERWEIGHT"
        If 26 <= BPI And BPI <= 38 Then BAI = "Healthy"
        If 39 <= BPI And BPI <= 43 Then BAI = "Overweight"
        If BPI >= 44 Then BAI = "OBESE"
    Else
        BAI = "Error - Age outside acceptable range"
    End If
ElseIf UCase(Trim(Gender)) = "MALE" Then
    If 20 <= Age And Age <= 39 Then
        If BPI <= 8 Then BAI = "UNDERWEIGHT"
        If 9 <= BPI And BPI <= 21 Then BAI = "Healthy"
        If 22 <= BPI And BPI <= 26 Then BAI = "Overweight"
        If BPI >= 27 Then BAI = "OBESE"
    ElseIf 40 <= Age And Age <= 59 Then
        If BPI <= 11 Then BAI = "UNDERWEIGHT"
        If 12 <= BPI And BPI <= 23 Then BAI = "Healthy"
        If 24 <= BPI And BPI <= 28 Then BAI = "Overweight"
        If BPI >= 29 Then BAI = "OBESE"
    ElseIf 60 <= Age And Age <= 79 Then
        If BPI <= 13 Then BAI = "UNDERWEIGHT"
        If 14 <= BPI And BPI <= 25 Then BAI = "Healthy"
        If 26 <= BPI And BPI <= 30 Then BAI = "Overweight"
        If BPI >= 31 Then BAI = "OBESE"
    Else
        BAI = "Error - Age outside acceptable range"
    End If
Else
    BAI = "Error - Choose male or female"
End If
End Function

Answer this questsion