Need help simplifying my nested IF formula

[Cheyanna_Ward]

So I have been tracking my progress in transcribing classical music pieces by using nested IF statements to determine whether a multi-part piece is partly or completely done from inputs in the individual parts. For something like a 4 movement symphony or sonata, that nested IF statement looks like so:

=IF(E13="Partly","Partly",IF(E13="Yes",IF(E14="Yes",IF(E15="Yes",IF(E16="Yes","Yes","Partly"),"Partly"),"Partly"),"No"))

And the results:

All movements cells set to the value of No = No in full piece cell

Partly in first movement cell = Partly in full piece cell

Any movement cells from the first to the second to last set to Yes +/- last movement set to Partly = Partly in full piece cell

All movement cells set to Yes = Yes in full piece cell

Not too bad. But what if I'm dealing with pieces/piece sets that have many many parts to them? Like say a set of 25 folk songs? Well now the nested IF formula looks like this:

=IF(E663="Partly","Partly",IF(E663="Yes",IF(E664="Yes",IF(E665="Yes",IF(E666="Yes",IF(E667="Yes",IF(E668="Yes",IF(E669="Yes",IF(E670="Yes",IF(E671="Yes",IF(E672="Yes",IF(E673="Yes",IF(E674="Yes",IF(E675="Yes",IF(E676="Yes",IF(E677="Yes",IF(E678="Yes",IF(E679="Yes",IF(E680="Yes",IF(E681="Yes",IF(E682="Yes",IF(E683="Yes",IF(E684="Yes",IF(E685="Yes",IF(E686="Yes",IF(E687="Yes","Yes","Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),),"Partly"),"Partly"),"Partly"),"Partly"),"No"))

And with operas, it gets even worse cause I have multiple levels of these nested IF formulas like so:

Full Opera Level, Nested IF formula includes Overture and individual acts:

=IF(F2506="Partly","Partly",IF(F2506="Yes",IF(F2507="Yes",IF(F2532="Yes",IF(F2553="Yes",IF(F2580="Yes",IF(F2603="Yes","Yes","Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"No"))

Act Level, Nested IF formula includes individual scenes:

=IF(F2508="Partly","Partly",IF(F2508="Yes",IF(F2512="Yes",IF(F2515="Yes",IF(F2518="Yes",IF(F2521="Yes",IF(F2524="Yes",IF(F2528="Yes","Yes","Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"Partly"),"No"))

Scene Level, Nested IF formula includes individual sections within, very comparable to the symphony/sonata example I mentioned before.

Is there any way to simplify this down while still keeping the changes that result for each piece/piece set/opera level as I complete the individual movements/pieces/opera sections?

[#2 · Accepted solution]

@Cheyanna_Ward 

This may help you:

=IF(COUNTIF(H4:H7,"No")=4,"No",IF(COUNTIF(H4:H7,"Yes")=4,"Yes",IF(COUNTIF(H4:H7,"Partly")>=1,"Partly","")))

It basically counts how many "No", "Partly" and "Yes" are in a set range. If all of the cells in the range are "No", returns "No". If there is at least one "Partly", returns "Partly" and if these are all "Yes, returns "Yes". I don't know if that is exactly the case you needed or you need to locate "Partly" exactly at the last position in the range in order to count the final result as "Partly"... In that case, the formulation will change.

If this suits you, you will only need to change the ranges in each case and also the "4" on the formulation depending on how many movements are in each piece.

[#3]

@Cheyanna_Ward 

If you are using Excel 2021 or a later version, a solution is possible where the formula automatically determines the number of parts to a musical piece. But some marker is needed to easily determine the end of the parts for a piece. I used the section symbol (§) in the attached workbook, but you can choose some other marker (single or multiple characters, even a space).

I wrote the formula to include the number of fully transcribed parts and the total number of parts, but of course you need not include that information. The formula is written to handle up to 100 parts per piece, and that number can readily be increased if needed.

See the _Info worksheet for documentation.