Forum Discussion

Cheyanna_Ward's avatar
Cheyanna_Ward
Copper Contributor
May 02, 2024

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 indivi...
Excel
Formulas and Functions
  • MAngosto's avatar
    MAngosto
    May 02, 2024

    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.

SnowMan55's avatar
SnowMan55
Bronze Contributor
May 02, 2024

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.