This was something I tried to do manually before, I managed to get the function working for 2 installments plans and got so busy to expand on others, I have decided to use AI since it's been many months since my last attempt.
They all failed spectacularly (errors nd whatnot), but at least deepseek was able to realize that I have a table and that it should reference to columns not cells, all the other AIs referred to absolute cells like A1 etc. which was something I insisted on and gave an example for, I will paste below my prompt, funny thing, it took Deepseek 355 (around 6 mints)seconds to make the 1st attempt, then 256 seconds.
GPT Was 5, Gemini was 2.5 pro, Copilot was on GPT 5, Deepseek was on Deepthink, all free versions.
I have the payment plans below and I have a google spreadsheet with a table called (Student Fees AY.25.26) with many columns but these are the columns that interest us (Payment Plan) this the chosen payment plan by student, it dictates the instalments (Total (Invoice Amount)) the total amount of the invoice to pay (Balance Due (This Year)) the remaining to pay I want a function that populates this column (Due amount According to Pay. Plan) this column should automatically detect the due amount based on the payment plan For example If a student (Total (Invoice Amount)) is 59000 and his (Payment Plan) is "2" then IF today is before 30 September it should be "4000" since nothing is due but registration which is always "4000", but if any day after 30 September it should write "27500" which is half of the "59000 minus 4000, that is 55000", and once it's 17th feb or after it should show the other "27500" but any day between 30th September and 17th September, if the sum paid is above 27500 it should show "0" since nothing is due that day Very important : This function should be variable not limited to the above numbers those are absolute results, it should work regardless of the amount and uses % only! Correctly refer to colmuns dynamically (this is how you refer to (Student_Fees_AY.25.26[Payment Plan]) not (Student Fees AY.25.26) correct on all function) I want the function to refer to column names in Google sheets (NOT EXCEL) instead of referng to cells Bachelor payment plan 1 InstallmentRegistrationUpon Registration 7% Tuition (-5%)30th September 93% 2 InstallmentsRegistrationUpon Registration 7% Tuition30th September 47% Tuition17th February 47% 3 InstallmentsRegistrationUpon Registration 7% Tuition30th September 31% Tuition16th January 31% Tuition30th April 31% 4 InstallmentsRegistrationUpon Registration 7% Tuition30th September 23% Tuition12th December 23% Tuition17th February 23% Tuition30th April 23% [link] [comments]