Home > Blockchain >  Google query or formula to find missing monthly fee
Google query or formula to find missing monthly fee

Time:11-03

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.

enter image description here

Sample sheet:

enter image description here


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})

enter image description here


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})

enter image description here

  • Related