Forum Discussion

Alison Flynn's avatar
Alison Flynn
Brass Contributor
Jan 15, 2019
Solved

Something wrong with this formula, but can't figure it out?

Hi,   I have a formula that doesn't seem to be working - I wonder if someone might spot where it's incorrect?     =IF(AND(AT34>=40,H34="M"),0,IF(AND(AT34>=37,H34="M"),5,10,IF(AND(AT34>=35,H34="F"...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 15, 2019

    Nope, the logic is unclear. One condition is

    Also if the BMI is under 25 the score in both cases it is also 10, over 25 it's a 5 and over 30 it's a zero score.

    or

    =IF(AP34>=30,0,
   IF(AP34>=25,5,10))

    which doesn't care of which gender is defined and covers all possible values in AP34.

    If you want to check the gender independently of above, when like

    =IF(H34="M",
      IF(AT34>=40,0,
      IF(AT34>=37,5,10)),
   IF(H34="F",
      IF(AT34>=35,0,
      IF(AT34>=31.5,5,10)),
      "Gender is not defined"
   ))

    which covers all possible values in AT34 and any gender if defined.

     

    Thus one or other

Alison Flynn's avatar
Alison Flynn
Brass Contributor
Jan 15, 2019

Gerry,

 

If I explain in english it might help:

If the waist circumference is greater than or equal to 40 and it's a male, then they score 0

If it's between 37 and 39 and it's male the score is 5 and if it's less than 37 the score is 10

If however it's a female, if the WC is greater than or equal to 35 the score is 0

If it's between 31.5 and 34 it's a score of 5 and under 31.5 it's a score of 10

Also if the BMI is under 25 the score in both cases it is also 10, over 25 it's a 5 and over 30 it's a zero score.

So it's an other or - if either of the conditions are met -WC or BMI.

Does that make sense to you?

Thanks for helping

Alison 

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 15, 2019

Nope, the logic is unclear. One condition is

Also if the BMI is under 25 the score in both cases it is also 10, over 25 it's a 5 and over 30 it's a zero score.

or

=IF(AP34>=30,0,
   IF(AP34>=25,5,10))

which doesn't care of which gender is defined and covers all possible values in AP34.

If you want to check the gender independently of above, when like

=IF(H34="M",
      IF(AT34>=40,0,
      IF(AT34>=37,5,10)),
   IF(H34="F",
      IF(AT34>=35,0,
      IF(AT34>=31.5,5,10)),
      "Gender is not defined"
   ))

which covers all possible values in AT34 and any gender if defined.

 

Thus one or other

  • Alison Flynn's avatar
    Alison Flynn
    Brass Contributor
    Jan 16, 2019

    Thank you Sergei,

     

    Actually there is a flaw in my whole formula.  Our initial formula was =IF(AND(AT2>40,H2="M"),1,IF(AND(AT2>35,H2="F"),1,IF(AP2>30,1,0))) which worked perfectly - giving a 1 or 0, i.e. a risk factor or not.

     

    Expanding it to have different conditions won't actually work, so I need to separate them out and average the result.

     

    I really appreciate your time and help.  You are very kind.

    Alison

    • Alison Flynn's avatar
      Alison Flynn
      Brass Contributor
      Jan 16, 2019

      Hi again Sergei,

      I've used both your formula's separately together the required results and they worked beautifully! Thank you

      Alison

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 16, 2019

        Hi Alison.

        Good, glad to help

Resources