Messy Formula

[NrthnChrs]

Hello,

Context; I'm very much an amateur when it comes to excel but managed to put the below 2 formulas together from a few forums but its causing my s/s to run incredibly slow. The s/s is also web linked to an intranet form which could be causing some of the delay also.

The form is a questionnaire with Yes / No and a comments section. Some questions have multiple parts ie 1a, 1b, 1c and the idea if each time a "Yes" is selected comments need to be provided. AJ2, AK2 and AL2 are declarations validating the questionnaire hence they are "No" and no comments. The declarations need to be selected "Yes" to pass.

If you would be able to shorten the below formulas that would be great.

Formula 1
=IF(OR(AND(H2="Yes", I2=""), AND(J2="Yes", N2=""), AND(K2="Yes", N2=""), AND(L2="Yes", N2=""), AND(M2="Yes", N2=""), AND(O2="Yes", Q2=""), AND(P2="Yes", Q2=""), AND(R2="Yes", S2=""), AND(T2="Yes", U2=""), AND(V2="Yes", W2=""), AND(X2="Yes", Z2=""), AND(Y2="Yes", Z2=""), AND(AA2="Yes", AC2=""), AND(AB2="Yes", AC2=""), AND(AD2="Yes", AE2=""), AND(AF2="Yes", AG2=""), AND(AH2="Yes", AI2=""), AND(AJ2="No"), AND(AK2="No"), AND(AL2="No")), "Yes", "No")

Formula 2
=IF(OR(AND(H2="Yes"), AND(J2="Yes"), AND(K2="Yes"), AND(L2="Yes"), AND(M2="Yes"), AND(O2="Yes"), AND(P2="Yes"), AND(R2="Yes"), AND(T2="Yes"), AND(V2="Yes"), AND(X2="Yes"), AND(Y2="Yes"), AND(AA2="Yes"), AND(AB2="Yes"), AND(AD2="Yes"), AND(AF2="Yes"), AND(AH2="Yes")), "Yes", "No")


Formula 1
Checks that if a dropdown is "Yes" then there needs to be a corresponding comment. If not it is marked Yes for further information needed.

Formula 2
Just checks that if a dropdown is "Yes" then the comment for that question needs to be reviewed.

Any help would be greatly appreciated.

Thanks,
Chris

 

[Eric W]

Welcome to the MrExcel forum!

For the second formula, you can remove all the ANDs from it, i.e. AND(AH2="Yes") can be replaced with simply AH2="Yes". No need to have an AND function with a single condition in it. And if the gaps between your cells will never have a "Yes" in them (so I2, N2, Q2, S2, U2, W2, Z2, AC2, AE2, AG2) you can replace the whole formula with:

=IF(COUNTIF(H2:AH2,"Yes"),"Yes","No")

The first formula is a bit trickier. Same question for it - what is between the specific cells in the formula?

 

[Joe4]

Unless you have thousands of formulas like that, my guess is that the link to the intranet is having more of an impact in the delay you are seeing than the formula is.