Search code examples
excel-formulagoogle-sheetsarray-formulas

Convert long formula into an ARRAYFORMULA

Document: https://docs.google.com/spreadsheets/d/1N4cGw5eUq_3gCJh1w39qVatX9KV1_Hr-AqRHj_nbckA/edit#gid=1770960621

Question

How can I convert the following simple formulas at Schedule!C20:I29 into a single, simple ARRAYFORMULA at Schedule!C20?

=Count(Filter(Students!$B$5:$B, Find(C6, Filter(Students!$J$5:$O,Students!$J$4:$O$4 = 'Current Class'!$B$3))))
.

NOTE:

  • The above code is only a partial solution. I will substitute the ARRAYFORMULA version of the code into the correct part of the code at Current Class!L6

  • The C6 reference above can take on any cell between Schedule!C6:I15. I have named that range Timetable_Code. I thought I could do the following, but I was wrong...

    =Arrayformula(Count(Filter(Students!$B$5:$B, Find(Timetable_Code, Filter(Students!$J$5:$O,Students!$J$4:$O$4 = 'Current Class'!$B$3)))))

Background

Originally, I created a table that now resides at 1st Version - Current Class!L6. This tab is only for your reference and will be deleted soon. Each cell has a formula with a slight modification. This formula works correctly; however, it is a behemoth and would be hard to modify...

=if(COUNTIF(Meta!$B$5:$B, CONCATENATE("=",if(L$5 = "THURSDAY", "TH", if(L$5 = "SUNDAY", "SU", left(L$5,1))), if(left($K6, 2) = "12", 0, left($K6, 1)))), CONCATENATE(if(L$5 = "THURSDAY", "TH", if(L$5 = "SUNDAY", "SU", left(L$5,1))), if(left($K6, 2) = "12", 0, left($K6, 1)), "     ( ", Count(Filter(Students!$B$5:$B, Find(CONCATENATE(if(L$5 = "THURSDAY", "TH", if(L$5 = "SUNDAY", "SU", left(L$5,1))), if(left($K6, 2) = "12", 0, left($K6, 1))), Filter(Students!$J$5:$O,Students!$J$4:$O$4 = $B$3)))), " )") ,"")
.

Pros

  • I don't have to create any helper data.
  • All calculations are "in-memory"

Cons

  • Too large
  • Hard to modify

I like the output, but I don't like the cons, so I started to create a more edit-friendly version of the code that I am mostly OK with. This code is located at Current Class!L6 (and a secondary copy at Schedule!C33 - it will be deleted.) It has a single formula at Current Class!L6...

=arrayformula(if(COUNTIF(Meta!$B$5:$B, ("=" & Timetable_Code)), (Timetable_Code & "     ( " & Timetable_StudentCount & " )") ,""))
.

Pros

  • Very easy to understand
  • Very easy to modify
  • No need to copy formula over to other cells

Cons

  • Two ( 2 ) helper tables were created ( one of which I think is unneeded)

Again, I like the output, but I really don't like the second helper table (Schedule!C20). I feel like this table can be eliminated, but I have not been able to figure out how.

Solution

  • After getting a good answer from @Sangbok Lee, I decided to break apart each part of the function he gave to me. While doing that I found a highly unlikely connection to some work I did in the Google Sheets last week. A helper column I had in another tab kind of did what Sangbok Lee was trying to do. All I had to do was split that helper column into two columns (1 for the previous final calculation, 1 for) and calculate an additional count column

    After reworking both of our formulas, and testing the result, I found a solution that I am even more satisfied with!

    =arrayformula(if(countif(Meta!$B$5:$B, (Timetable_Code)), (Timetable_Code & "     ( " & vlookup(Timetable_Code, StudentCount_Lookup, 2, false) & " )") ,""))
.

    Check out the differences in the Google Sheet

    • Look at 1st Version - Current Class!L6 tab for the 1st version
    • Look at Current Class!L6 for the 2nd version

    • Look at Current Class!U6 for the 3rd and final version

    • Also look at tab Meta and Schedule for the differences.

    Note: Green is old data, Red is new data