- 551K Members
- 3,140 Online
- 661K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Something wrong with this formula, but can't figure it out?

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

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

Home

- Home
- :
- Excel
- :
- General Discussion
- :
- Something wrong with this formula, but can't figure it out?

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-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

Labels:

7 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-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.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-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

Best Response confirmed by
Alison Flynn (Contributor)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-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

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-16-2019 05:00 AM

Hi Alison.

Good, glad to help

Related Conversations

SharePoint 2013 LIst Filters (by latest date)

dmphil
in
SharePoint
on
02-27-2020
69
Views

0 Likes

0 Replies

Pivot Table StDev calculates different value then the STDEV formula

zsoltturkosi
in
Excel
on
10-21-2019
217
Views

0 Likes

4 Replies

need macro to find and copy range of data between specified cells.

spike3rd
in
Office 365
on
10-09-2019
167
Views

0 Likes

0 Replies

Populating a cell in ref to another cells specific contents (first/last blank cell in sequence)

kiara1992
in
Microsoft Teams
on
08-27-2019
120
Views

0 Likes

0 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft