Debt payer sheet

I’m having an error in this expression:

Can you help me?

Best Regards,

What are the error messages shown when you select the cells with red triangles?


The error:

Formula parse error.

That error is usually the result of an incorrectly-entered formula. If you’ve changed any of the formulas, the first thing I would check for is missing or misplaced argument separators. In the formula shown in your screenshot, it looks as if some of the backslashes inside the curly brackets should be semicolons, such as between "ranked" and BYROW. Try the formula with backslashes ONLY before each IF statement, before ARRAYFORMULA, and before A16. Change the other backslashes to semicolons.

Hi there,

like this:

=iferror(transpose(query({$B$16:$B$40\if(lower($D$4)=“ranked”;BYROW(F16:F40;lambda(rank\if(isblank(rank);1000;rank)))\if(lower($D$4)=“snowball”;H16:H40;D16:D40))\arrayformula(H16:H40>0);A16:A40};“SELECT Col1 WHERE Col1<>‘’ AND Col4 = TRUE ORDER BY Col3, Col2”&if(lower($D$4)=“avalanche”;" DESC";“”))))

When I change my locale (File → Settings → Locale) to one that uses semicolons instead of commas as argument separators (and backslashes instead of commas as horizontal array separators), the original formula looks like this:

=iferror(transpose(query({$B$16:$B$40\if(lower($D$4)="ranked";BYROW(F16:F40;lambda(rank;if(isblank(rank);1000;rank)));if(lower($D$4)="snowball";H16:H40;D16:D40))\arrayformula(H16:H40>0)\A16:A40};"SELECT Col1 WHERE Col1<>'' AND Col4 = TRUE ORDER BY Col3, Col2"&if(lower($D$4)="avalanche";" DESC";""))))

Were you trying to change part of the the formula?

1 Like

Thank you. It works.