Jan 15 2019 10:02 AM
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"),0,IF(AND(AT34>=31.5,H34="F"),5,10,IF(AP34>=30),0,IF(AP34>=25)5,0)))))))
Thanks
Jan 15 2019 10:43 AM
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.
Jan 15 2019 11:59 AM
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
Jan 15 2019 12:28 PM
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
Jan 15 2019 01:15 PM
SolutionNope, 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
Jan 16 2019 03:05 AM
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
Jan 16 2019 03:44 AM
Hi again Sergei,
I've used both your formula's separately together the required results and they worked beautifully! Thank you
Alison
Jan 16 2019 05:00 AM
Hi Alison.
Good, glad to help
Jan 15 2019 01:15 PM
SolutionNope, 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