Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Formula assist

Brass Contributor

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

2 Replies

@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.

@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.

Hussein_Mohamed_2-1702808994514.png

 

Hussein_Mohamed_0-1702808713417.png