Can anybody correct my below if condition ? I need that asap

Khushboo Parab

=IF(OR(AND([UI UX LEVEL OF EFFORT]10 = "X-SMALL (less than 5 hours)",[DEVELOPMENT LEVEL OF EFFORT]10 = "X-SMALL (less than 5 hours)"), "FILLING"), IF(AND([UI UX LEVEL OF EFFORT]10 = "X-SMALL (less than 5 hours)", [DEVELOPMENT LEVEL OF EFFORT]10 = "X-SMALL (less than 5 hours)",OR([Priority]10 = "HIGH","MEDIUM")), "QUICK WIN"),IF(AND(OR([UI UX LEVEL OF EFFORT]10="X-SMALL (less than 5 hours)","SMALL (less than 20 hours)","MEDIUM (40 to 80 hours)"),OR([DEVELOPMENT LEVEL OF EFFORT]10="X-SMALL (less than 5 hours)","SMALL (less than 20 hours)","MEDIUM (40 to 80 hours)"),[Priority]10="HIGH"),"QUICK WIN"),IF(AND(OR([UI UX LEVEL OF EFFORT]10="LARGER (80 to 120 hours)","X-LARGE (more than 120 hours)"),OR([DEVELOPMENT LEVEL OF EFFORT]10="LARGER (80 to 120 hours)","X-LARGE (more than 120 hours)"),OR([Priority]10="HIGH","MEDIUM")),"MAJOR PROJECT"),IF(AND(OR([UI UX LEVEL OF EFFORT]10="LARGER (80 to 120 hours)","X-LARGE (more than 120 hours)"),OR([DEVELOPMENT LEVEL OF EFFORT]10="LARGER (80 to 120 hours)","X-LARGE (more than 120 hours)"),[Priority]10="LOW"),"THANKLESS EFFORT")))

if UX UI level of Effort is X-SMALL and Development LEVEL OF Effort is X-SMALL then regardless what total value score ( priority) is just mark ACTION PRIORITY as FILLIN

UX UI level of Effort is X-SMALL and Development LEVEL OF Effort is X-SMALL and total value score ( priority) is HIGH or MEDUIM then mark ACTION PRIORITY as QUICK WIN

UX UI level of Effort is X-SMALL OR SMALL OR MEDIUM and Development LEVEL OF Effort is X-SMALL OR SMALL OR MEDIUM and total value score (priority)is HIGH then mark ACTION PRIORITY as QUICK WIN

UX UI level of Effort is LARGE or X-LARGE and Development level of Effort is LARGE or X-LARGE and total value score ( priority) is HIGH or MEDUIM then mark ACTION PRIORITY as MAJOR PROJECT

UX UI level of Effort is LARGE or X-LARGE and Development level of Effort is LARGE or X-LARGE and total value score ( priority) is low then mark ACTION PRIORITY as thankless effort 

David Joyeuse

You already have a problem on your first condition:

OR(AND([UI UX LEVEL OF EFFORT]10 = "X-SMALL (less than 5 hours)",[DEVELOPMENT LEVEL OF EFFORT]10 = "X-SMALL (less than 5 hours)"), "FILLING")

Since "FILLING" is supposedly the result of the first condition, the ")" after it is definately misplaced as it should be placed prior to ",". But in this case I really wonder why do you need an OR, as your conditions UI UX level of effort AND Development LEVEL OF Effort are required to be X-SMALL. Get rid of it.

Then you run onto another problem with the second AND/OR statement:

AND([UI UX LEVEL OF EFFORT]10 = "X-SMALL (less than 5 hours)", [DEVELOPMENT LEVEL OF EFFORT]10 = "X-SMALL (less than 5 hours)",OR([Priority]10 = "HIGH","MEDIUM"))

It should be OR([Priority]10="HIGH", [Priority]10="MEDIUM"). And as a whole, all your OR conditions are wrong here. So that doesn't help.

Now I also don't get what you want to get out of this:

if UX UI level of Effort is X-SMALL and Development LEVEL OF Effort is X-SMALL then regardless what total value score ( priority) is just mark ACTION PRIORITY as FILLIN

UX UI level of Effort is X-SMALL and Development LEVEL OF Effort is X-SMALL and total value score ( priority) is HIGH or MEDUIM then mark ACTION PRIORITY as QUICK WIN

Those are your first two conditions, and if I'm reading them well, you'll never reach the "QUICK WIN" option, as the first value will get you to "FILLIN".

In the end, there's also lots of possibilities you aren't checking with your conditions. Stuff like: UX UI loE is X-SMALL and Development LOE is X-LARGE. Making it harder to use conditions like NOT.

e.g.:

Instead of:

AND(OR([UI UX LEVEL OF EFFORT]10="X-SMALL (less than 5 hours)","SMALL (less than 20 hours)","MEDIUM (40 to 80 hours)"),OR([DEVELOPMENT LEVEL OF EFFORT]10="X-SMALL (less than 5 hours)","SMALL (less than 20 hours)","MEDIUM (40 to 80 hours)"),[Priority]10="HIGH")

You could use:

AND(NOT(OR([UI UX LEVEL OF EFFORT]10="LARGE",[UI UX LEVEL OF EFFORT]10="X-LARGE")),NOT(OR([DEVELOPMENT LEVEL OF EFFORT]10="LARGE",[DEVELOPMENT LEVEL OF EFFORT]10="X-LARGE"),[Priority]10="HIGH")

Finally, you tend to put an ")" after each true condition of your previous IF statement. That parenthesis close the IF Function, and thus, the formula does not goes beyond that. So you'll have to correct that as well.

To ease your pain I would also suggest you to review the way you're weighting your conditions, from what you've written, the most important thing is the Priority value. Start by checking this value first, then evaluate the other cells.

Which could end up with something like:

=IF(OR([Priority]10="HIGH",[Priority]10="MEDIUM"),IF(AND(FIND("LARGE",[UI UX LEVEL OF EFFORT]10)>0,FIND("LARGE",[DEVELOPMENT LEVEL OF EFFORT]10)>0),"MAJOR PROJECT",IF(AND([UI UX LEVEL OF EFFORT]10="X-SMALL",[DEVELOPMENT LEVEL OF EFFORT]10="X-SMALL"),"FILLIN","QUICK WIN")),"THANKLESS EFFORT")

It does not do exactly what you wanted to, but it covers all possibilities (and there's 75 of them).

IF a project is HIGH or MEDIUM, it is a MAJOR PROJECT as long as it's LARGE or X-LARGE. Otherwise, it's a quick win unless it is an X-SMALL level of effort, in which case it is a FILLIN.

And if the project is low, no matter what, it's a THANKLESS EFFORT.

Not sure that's exactly what you're looking for, but I hope it can help a bit :)

Khushboo Parab

I want one of the condition from this to work that's why I used OR :

if UX UI level of Effort is X-SMALL and Development LEVEL OF Effort is X-SMALL then regardless what total value score ( priority) is just mark ACTION PRIORITY as FILLIN

OR

UX UI level of Effort is X-SMALL and Development LEVEL OF Effort is X-SMALL and total value score ( priority) is HIGH or MEDUIM then mark ACTION PRIORITY as QUICK WIN

OR

UX UI level of Effort is X-SMALL OR SMALL OR MEDIUM and Development LEVEL OF Effort is X-SMALL OR SMALL OR MEDIUM and total value score (priority)is HIGH then mark ACTION PRIORITY as QUICK WIN

OR

UX UI level of Effort is LARGE or X-LARGE and Development level of Effort is LARGE or X-LARGE and total value score ( priority) is HIGH or MEDUIM then mark ACTION PRIORITY as MAJOR PROJECT

OR

UX UI level of Effort is LARGE or X-LARGE and Development level of Effort is LARGE or X-LARGE and total value score ( priority) is low then mark ACTION PRIORITY as thankless effort 

Sorry , If I am asking you stupid questions but I am new to this.

Thanks,

Khushboo Parab

And Also When Development Effort is Large or X-Large , and Priority High Or Medium , then regardless of Any UI UX efforts it should select Action as Major Effort

David Joyeuse

OK, so I misunderstood your "and" conditions then.

It's quite even easier and should do the trick then:

=IF(OR([Priority]10="HIGH",[Priority]10="MEDIUM"),IF(FIND("LARGE",[DEVELOPMENT LEVEL OF EFFORT]10)>0,IF(FIND("LARGE",[UI UX LEVEL OF EFFORT]10)>0,"MAJOR PROJECT","MAJOR EFFORT"),IF(OR(NOT([UI UX LEVEL OF EFFORT]10="X-SMALL"),NOT([DEVELOPMENT LEVEL OF EFFORT]10="X-SMALL")),"QUICK WIN","FILLIN"),IF(OR(FIND("LARGE",[DEVELOPMENT LEVEL OF EFFORT]10)>0,FIND("LARGE",[UI UX LEVEL OF EFFORT]10)>0),"THANKLESS EFFORT","FILLIN"))

So here we go:

If Priority is High or Medium is True:

==> If Development LOE is Large or X-Large and whatever UX UI LOE: MAJOR EFFORT

==> UX UI LOE LArge or X-Large: MAJOR PROJECT

==> If Dev LOE or UX UI LOE are not X-SMALL (ie SMALL or MEDIUM): QUICK WIN

==> If either is: FILLIN

If Priority is High or Medium is False:

==> If Dev LOE or UX UI LOE are Large or X-Large: THANKLESS EFFORT

==> If not: FILLIN

That should cover it all.

Test it, and watchout for " " " and ";". I'm using a french version so I may replace , with ; from times to times :P

Let me know how this goes :)