• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Replacing long/confusing/not-userfriendly calculation with something better!

E

exc4libur

Member
Hi all,


I am having trouble trying to figure out another way to calculate my total ($) sum of settlements which is triggered through every 1st payment.


I wish I could explain this more eloquently, so I built an example spreadsheet.


https://www.dropbox.com/s/xiq9zcnwdpedl4i/Schedule%20of%20Payments%20and%20Settlements.xls
 
Hi Hui,


Thank you for the reminder! Please find the link to the sample wkbook.

https://www.dropbox.com/s/xiq9zcnwdpedl4i/Schedule%20of%20Payments%20and%20Settlements.xls
 
Hi, exc4l1bur!


If the 1st issue of the manual addition of 2 columns is related to a date after column P and an amount after column X, it might be done with 2 buttons at top, one for increasing settlements and the other for decreasing. I assume that there's nothing to change in actual formulas.


Regarding your 2nd issue what do you mean by control and which would be the different settlements? How many, in what do they differ, is there a method to calculate in 3, 6 or 12 payments? (method and interest rates).


Regards!
 
Hi SirJB7! Please find what I am trying to achieve in the sample below!


https://www.dropbox.com/s/i7sgh7wbvcd2e42/Schedule%20of%20Payments%20and%20Settlements.xlsb


Regards.
 
Hi, exc4l1bur!


Sorry but I'm not loan, mortgage or something alike specialist so I don't know the answers to your yellow shaded cells at top of columns J:M and P:AE. It's you who should tell me how to do the calculations, and it's me who should try to tell Excel to be a good and obedient and compliant boy (which not always happens).

If you explain the method you'd use for manually calculate those values it'd be clearer for me or people who read this how to help you.


Regards!
 
Hi SirJB7,


Forgive me, but I sent you the wrong link! Please find the correct one below!!! Sorry!!!


https://www.dropbox.com/s/i7sgh7wbvcd2e42/Schedule%20of%20Payments%20and%20Settlements.xlsb


Best Rgds.
 
Hi, exc4l1bur!


What about a one column formula?

Try this in column O:

{=SI.ERROR(SUMAPRODUCTO((C$7:C$52)*0,8/8*(SI((AÑO(F9)-AÑO(D$7:D$52))*12+MES(F9)-MES(D$7:D$52)>0;SI((AÑO(F9)-AÑO(D$7:D$52))*12+MES(F9)-MES(D$7:D$52)<=8;SIGNO((AÑO(F9)-AÑO(D$7:D$52))*12+MES(F9)-MES(D$7:D$52));0);0)));"-")} -----> in english: {=IFERROR(SUMPRODUCT((C$7:C$52)*0.8/8*(IF((YEAR(F9)-YEAR(D$7:D$52))*12+MONTH(F9)-MONTH(D$7:D$52)>0,IF((YEAR(F9)-YEAR(D$7:D$52))*12+MONTH(F9)-MONTH(D$7:D$52)<=8,SIGN((YEAR(F9)-YEAR(D$7:D$52))*12+MONTH(F9)-MONTH(D$7:D$52)),0),0))),"-")}


I'd define dynamic named ranges for data ranges in columns C and D as:

=DESREF($C$7;;;CONTAR.SI($C:$C)-2;1) -----> in english: =OFFSET($C$7,,,COUNTIF($C:$C)-2,1)

and use them in the previous array formula.


Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.


Just advise if any issue.


Regards!


PS: You should rename the topic title to "Replacing long/confusing/not-userfriendly calculation with something... better?... well, short/not-confusing/NOT-userfriendly but better" :)
 
Hi, exc4l1bur!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: You can send a check payable in 2023 with the 0,01% for the KeyMaster.

PS2: Mine 9,99% please cash :P
 
You must log in or register to reply here.
Back
Top