SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-315363%22%20slang%3D%22en-US%22%3ESomething%20wrong%20with%20this%20formula%2C%20but%20can't%20figure%20it%20out%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315363%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20formula%20that%20doesn't%20seem%20to%20be%20working%20-%20I%20wonder%20if%20someone%20might%20spot%20where%20it's%20incorrect%3F%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(AT34%26gt%3B%3D40%2CH34%3D%22M%22)%2C0%2CIF(AND(AT34%26gt%3B%3D37%2CH34%3D%22M%22)%2C5%2C10%2CIF(AND(AT34%26gt%3B%3D35%2CH34%3D%22F%22)%2C0%2CIF(AND(AT34%26gt%3B%3D31.5%2CH34%3D%22F%22)%2C5%2C10%2CIF(AP34%26gt%3B%3D30)%2C0%2CIF(AP34%26gt%3B%3D25)5%2C0)))))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-315363%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319383%22%20slang%3D%22en-US%22%3ERe%3A%20Something%20wrong%20with%20this%20formula%2C%20but%20can't%20figure%20it%20out%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319383%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Alison.%3C%2FP%3E%0A%3CP%3EGood%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319328%22%20slang%3D%22en-US%22%3ERe%3A%20Something%20wrong%20with%20this%20formula%2C%20but%20can't%20figure%20it%20out%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319328%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20again%20Sergei%2C%3C%2FP%3E%3CP%3EI've%20used%20both%20your%20formula's%20separately%20together%20the%20required%20results%20and%20they%20worked%20beautifully!%20Thank%20you%3C%2FP%3E%3CP%3EAlison%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319316%22%20slang%3D%22en-US%22%3ERe%3A%20Something%20wrong%20with%20this%20formula%2C%20but%20can't%20figure%20it%20out%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319316%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActually%20there%20is%20a%20flaw%20in%20my%20whole%20formula.%20%26nbsp%3BOur%20initial%20formula%20was%26nbsp%3B%3DIF(AND(AT2%26gt%3B40%2CH2%3D%22M%22)%2C1%2CIF(AND(AT2%26gt%3B35%2CH2%3D%22F%22)%2C1%2CIF(AP2%26gt%3B30%2C1%2C0)))%20which%20worked%20perfectly%20-%20giving%20a%201%20or%200%2C%20i.e.%20a%20risk%20factor%20or%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExpanding%20it%20to%20have%20different%20conditions%20won't%20actually%20work%2C%20so%20I%20need%20to%20separate%20them%20out%20and%20average%20the%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20appreciate%20your%20time%20and%20help.%20%26nbsp%3BYou%20are%20very%20kind.%3C%2FP%3E%3CP%3EAlison%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-316214%22%20slang%3D%22en-US%22%3ERe%3A%20Something%20wrong%20with%20this%20formula%2C%20but%20can't%20figure%20it%20out%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-316214%22%20slang%3D%22en-US%22%3E%3CP%3ENope%2C%20the%20logic%20is%20unclear.%20One%20condition%20is%3C%2FP%3E%0A%3CBLOCKQUOTE%3E%0A%3CP%3E%3CSPAN%3EAlso%20if%20the%20BMI%20is%20under%2025%20the%20score%20in%20both%20cases%20it%20is%20also%2010%2C%20over%2025%20it's%20a%205%20and%20over%2030%20it's%20a%20zero%20score.%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FBLOCKQUOTE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%3E%3DIF(AP34%26gt%3B%3D30%2C0%2C%0A%20%20%20IF(AP34%26gt%3B%3D25%2C5%2C10))%0A%3C%2FPRE%3E%0A%3CP%3Ewhich%20doesn't%20care%20of%20which%20gender%20is%20defined%20and%20covers%20all%20possible%20values%20in%20AP34.%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20check%20the%20gender%20independently%20of%20above%2C%20when%20like%3C%2FP%3E%0A%3CPRE%3E%3DIF(H34%3D%22M%22%2C%0A%20%20%20%20%20%20IF(AT34%26gt%3B%3D40%2C0%2C%0A%20%20%20%20%20%20IF(AT34%26gt%3B%3D37%2C5%2C10))%2C%0A%20%20%20IF(H34%3D%22F%22%2C%0A%20%20%20%20%20%20IF(AT34%26gt%3B%3D35%2C0%2C%0A%20%20%20%20%20%20IF(AT34%26gt%3B%3D31.5%2C5%2C10))%2C%0A%20%20%20%20%20%20%22Gender%20is%20not%20defined%22%0A%20%20%20))%0A%3C%2FPRE%3E%0A%3CP%3Ewhich%20covers%20all%20possible%20values%20in%20AT34%20and%20any%20gender%20if%20defined.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThus%20one%20or%20other%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-315871%22%20slang%3D%22en-US%22%3ERe%3A%20Something%20wrong%20with%20this%20formula%2C%20but%20can't%20figure%20it%20out%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315871%22%20slang%3D%22en-US%22%3E%3CP%3EGerry%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20explain%20in%20english%20it%20might%20help%3A%3C%2FP%3E%3CP%3EIf%20the%20waist%20circumference%20is%20greater%20than%20or%20equal%20to%2040%20and%20it's%20a%20male%2C%20then%20they%20score%200%3C%2FP%3E%3CP%3EIf%20it's%20between%2037%20and%2039%20and%20it's%20male%20the%20score%20is%205%20and%20if%20it's%20less%20than%2037%20the%20score%20is%2010%3C%2FP%3E%3CP%3EIf%20however%20it's%20a%20female%2C%20if%20the%20WC%20is%20greater%20than%20or%20equal%20to%2035%20the%20score%20is%200%3C%2FP%3E%3CP%3EIf%20it's%20between%2031.5%20and%2034%20it's%20a%20score%20of%205%20and%20under%2031.5%20it's%20a%20score%20of%2010%3C%2FP%3E%3CP%3EAlso%20if%20the%20BMI%20is%20under%2025%20the%20score%20in%20both%20cases%20it%20is%20also%2010%2C%20over%2025%20it's%20a%205%20and%20over%2030%20it's%20a%20zero%20score.%3C%2FP%3E%3CP%3ESo%20it's%20an%20other%20or%20-%20if%20either%20of%20the%20conditions%20are%20met%20-WC%20or%20BMI.%3C%2FP%3E%3CP%3EDoes%20that%20make%20sense%20to%20you%3F%3C%2FP%3E%3CP%3EThanks%20for%20helping%3C%2FP%3E%3CP%3EAlison%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-315759%22%20slang%3D%22en-US%22%3ERe%3A%20Something%20wrong%20with%20this%20formula%2C%20but%20can't%20figure%20it%20out%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315759%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Gerry%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20very%20good%20to%20respond%2C%20I'm%20afraid%20it's%20not%20working%20still.%20%26nbsp%3BI%20have%207%20brackets%20at%20the%20end%20-%20is%20that%20the%20right%20amount%20or%20would%20that%20make%20a%20difference%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%2030%20in%20line%205%20is%20highlighting%20as%20if%20this%20is%20the%20problem%2C%20but%20I%20don't%20see%20how%2C%20any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20tell%20me%20why%20I%20would%20take%20out%20the%2010%3F%20I%20need%20to%20return%20a%20score%20of%2010%20if%20it's%20under%2037%20and%20Male%20or%20a%20score%20of%2010%20if%20it's%20under%2035%20and%20Female.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20million%20for%20your%20help.%3C%2FP%3E%3CP%3EAlison%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-315399%22%20slang%3D%22en-US%22%3ERe%3A%20Something%20wrong%20with%20this%20formula%2C%20but%20can't%20figure%20it%20out%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315399%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Alison%2C%3C%2FP%3E%3CP%3EIn%20the%20formula%20you%20have%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(AND(AT34%26gt%3B%3D40%2CH34%3D%22M%22)%2C0%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIF(AND(AT34%26gt%3B%3D37%2CH34%3D%22M%22)%2C5%2C%3CSTRIKE%3E10%2C%3C%2FSTRIKE%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIF(AND(AT34%26gt%3B%3D35%2CH34%3D%22F%22)%2C0%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIF(AND(AT34%26gt%3B%3D31.5%2CH34%3D%22F%22)%2C5%2C%3CSTRIKE%3E10%2C%3C%2FSTRIKE%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIF(AP34%26gt%3B%3D30)%2C0%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIF(AP34%26gt%3B%3D25)5%2C0)))))))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20extra%2010%2C%20on%20line%202%20and%204%20should%20be%20removed%2C%20and%20the%20%2C%20before%20the%205%20on%20the%20last%20line%20is%20missing.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Alison Flynn
Contributor

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

7 Replies

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.

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

 

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 

 

Solution

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

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

Hi again Sergei,

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

Alison

Hi Alison.

Good, glad to help

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies