I am trying to build the below dashboard from the data given in the below format in the google sheet..
I can calculate the running balance but it is not helping me to figure out the missing fee months.
Any help will be highly appreciated.
Sample sheet:
update 1:
=ARRAYFORMULA({REGEXREPLACE(TRIM({
FLATTEN(QUERY(TRANSPOSE(IF(F4:H6=0, F3:H3&",", )),,9^9)),
FLATTEN(QUERY(TRANSPOSE(IF(F4:H6<>0, F3:H3&",", )),,9^9))}), ",$", ), LEN(REGEXREPLACE({
FLATTEN(QUERY(TRANSPOSE(IF(F4:H6<>0, ",", )),,9^9)),
FLATTEN(QUERY(TRANSPOSE(IF(F4:H6=0, ",", )),,9^9))}, "\s", ))*E4:E6, I4:I6})
update 2:
=ARRAYFORMULA({REGEXREPLACE(TRIM({
FLATTEN(QUERY(TRANSPOSE(IF(F4:H6=0, F3:H3&",", )),,9^9)),
FLATTEN(QUERY(TRANSPOSE(IF(F4:H6<>0, F3:H3&",", )),,9^9))}), ",$", ),
MMULT(F4:H6*1, SEQUENCE(COLUMNS(F3:H3))^0), I4:I6-
MMULT(F4:H6*1, SEQUENCE(COLUMNS(F3:H3))^0), I4:I6})
update 3:
=ARRAYFORMULA({REGEXREPLACE(TRIM({
FLATTEN(QUERY(TRANSPOSE(IF((F4:H6=0)*(REGEXMATCH(F3:H3, "Feb|Apr|May")), F3:H3&",", )),,9^9)),
FLATTEN(QUERY(TRANSPOSE(IF((F4:H6<>0)*(REGEXMATCH(F3:H3, "Feb|Apr|May")), F3:H3&",", )),,9^9))}), ",$", ),
MMULT((F4:H6)*(REGEXMATCH(F3:H3, "Feb|Apr|May")), SEQUENCE(COLUMNS(F3:H3))^0), I4:I6-
MMULT((F4:H6)*(REGEXMATCH(F3:H3, "Feb|Apr|May")), SEQUENCE(COLUMNS(F3:H3))^0), I4:I6})