# Help!!

#### SarahBear

##### New Member
I've been looking at this all week but whatever I try seems to come up with an error. I asked a question around the same form a couple of weeks but in true management style they now want it to do something different!!

There are two sets of questions on the form. On one side I have a list of 15 questions, all of which are mandatory so if any of them are marked as a 'No' the overall score will be a fail.

On the other side I have a list of 4 questions. These are technical and soft skill questions. If one of the questions is marked as a 'No' but the mandatory side is a pass then the score would be 'Requires Attention'. If two are marked as a 'No' but the mandatory side is a pass then the score would be 'Requires Development'. If 3 or more are marked as a 'No' with the mandatory score as a pass then the overall score would be a fail. As mentioned if there are any questions marked as a 'No' on the mandatory side it would be a fail regardless of whether all the technical/soft skills were a pass.

I've got this on the mandatory side - =IF(F30=15,"Pass","Fail") and this on the technical/soft skill side - =IF(M17<2,"Fail",IF(M17=3,"Requires Attention",IF(M17=2,"Requires Development","Pass"))) but am completely stuck on how to bring them both together to get an overall result.

Any help greatly appreciated!

I've tried =IF(AND..... but just seem to get an error every single time and it's driving me mad as I know that there must be a way of doing it!

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Code:
``[COLOR=#333333]=IF(F30=15,"Pass","Fail")[/COLOR]``
This is in cell A1 for my example
Code:
``[COLOR=#333333]=IF(M17<2,"Fail",IF(M17=3,"Requires Attention",IF(M17=2,"Requires Development","Pass")))[/COLOR]``
This is in cell A2 for my example
Code:
``=IF(A1 = "Pass",A2, A1)``

Maybe, if F30 and M17 both count Yes answers, ...

=IF(F30<15, "Fail", LOOKUP(M17, {0,1,2,3,4}, {"Fail","Requires Development","Requires Attention","Pass"}))

Code:
``[COLOR=#333333]=IF(F30=15,"Pass","Fail")[/COLOR]``
This is in cell A1 for my example
Code:
``[COLOR=#333333]=IF(M17<2,"Fail",IF(M17=3,"Requires Attention",IF(M17=2,"Requires Development","Pass")))[/COLOR]``
This is in cell A2 for my example
Code:
``=IF(A1 = "Pass",A2, A1)``

Ooooh, this works perfectly, thank you! It didn't even occur to me to reference the cells that the other formulas were sitting in.

Thank you!

Maybe, if F30 and M17 both count Yes answers, ...

=IF(F30<15, "Fail", LOOKUP(M17, {0,1,2,3,4}, {"Fail","Requires Development","Requires Attention","Pass"}))

NeonRedSharpie's one works perfectly but I'll give this a try as well - always good to have options! Thank you!

You're welcome.

Replies
25
Views
2K
Replies
6
Views
216
Replies
0
Views
180
Replies
0
Views
155
Replies
0
Views
265

1,196,098
Messages
6,013,442
Members
441,766
Latest member
ixruiz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back