Forum Discussion

Hussein_Mohamed's avatar
Hussein_Mohamed
Brass Contributor
Dec 14, 2023

Formula assist

Dears,

I hope this email finds you well. I am writing to seek your assistance in modifying a formula to calculate payment amounts based on specific criteria outlined in the "Assum" sheet.

The formula in question is used to calculate payments for a project, and I need to incorporate the following criteria:

  1. Down payment: It should be calculated as three months before the given date in the "C-Prive" sheet column C.
  2. First payment: It should coincide with the date provided in the "C-Prive" sheet column C.
  3. Bulk1 and Bulk2 payments: They should correspond to the dates specified above them in the "Assum" sheet. F1&G1
  4. Equal payments: Starting from the first payment date, the subsequent payments should be of equal amounts until the end of the payment schedule.

Thanks in advance

Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Dec 17, 2023

    Hussein_Mohamed 

    If you are working with Excel tables, let us assume you have a table named YourTable with a date column named YourDateColumn. Adjust the table and column names accordingly.

    Here is a formula that you can use in a new column of your table:

    =IF(ISBLANK([@YourDateColumn]), "",

   IF([@YourDateColumn]-TODAY()<=-90, 'Assum'!C1,

      IF([@YourDateColumn]=TODAY(), [@YourDateColumn],

         IF(AND('Assum'!F1<>[@YourDateColumn], 'Assum'!G1<>[@YourDateColumn]),

            IF('Assum'!F1>[@YourDateColumn], 'Assum'!F1, 'Assum'!G1),

            IF([@YourDateColumn]<'Assum'!F1, 'Assum'!F1,

               IF([@YourDateColumn]<'Assum'!G1, 'Assum'!G1,

                  [@YourDateColumn] + (ROW()-ROW([@YourFirstRow]))*'Assum'!H1

               )

            )

         )

      )

   )

)

     

    Replace YourDateColumn with the actual name of the date column in your table, and YourFirstRow with the actual name of the first row in your table.

    This formula checks the conditions you specified and calculates payments accordingly for each row in your table. Adjust the references based on your actual table structure.

    NOTE: For personal security reasons the file was not opened. No one has responded to this for at least a day or more, even though over 80 people have already seen it, so I entered your question into various AIs. The text and steps are the result of putting together different AIs. Maybe it will help you with your project, if not, please just ignore it.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

    • Hussein_Mohamed's avatar
      Hussein_Mohamed
      Brass Contributor
      Dec 17, 2023

      NikolinoDE 

      Dear Nikolino,

      Thank you for your assist, i have attached two screen shoots from what i looking for, i hope it will explain more.

       

       

Share

Resources