Need help simplifying my nested IF formula
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?
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 cellPartly in first movement cell = Partly in full piece cellAny movement cells from the first to the second to last set to Yes +/- last movement set to Partly = Partly in full piece cellAll movement cells set to Yes = Yes in full piece cellNot 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? Read More