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

GerryScs's avatar
GerryScs
Copper Contributor
Jan 15, 2019

Hi Alison,

In the formula you have 

=IF(AND(AT34>=40,H34="M"),0,

IF(AND(AT34>=37,H34="M"),5,10,

IF(AND(AT34>=35,H34="F"),0,

IF(AND(AT34>=31.5,H34="F"),5,10,

IF(AP34>=30),0,

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

 

The extra 10, on line 2 and 4 should be removed, and the , before the 5 on the last line is missing.

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

Hi Gerry,

 

You're very good to respond, I'm afraid it's not working still.  I have 7 brackets at the end - is that the right amount or would that make a difference?

 

The 30 in line 5 is highlighting as if this is the problem, but I don't see how, any ideas?

 

Can you tell me why I would take out the 10? I need to return a score of 10 if it's under 37 and Male or a score of 10 if it's under 35 and Female.

 

Thanks a million for your help.

Alison

 

Resources