Splitting Amount into equal payments across several cells

Rosa Crisostomo · 02/04/21

Hello,

I am needing some help trying to figure out a formula that will take an amount due and split it across a determined number of months. I would also like it to return a "due date" based on a "start date".

Total Amount: $77,760.00

# of Months: 6

Start Date: 2/1/2021

I would like to have the amount due ($77,760) split between 6 payments. Each payment will populate in its corresponding month (payment cells) and return the due date. As you can see I did not get very far and all of the formulas I came across factored in interest, which we do not use. Thank you all in advance!!

Paul Newcome · 02/08/21

Are you able to copy/paste your exact formula directly from your sheet? It shouldn't be doing that as the first portion of the IF is comparing the number in the [# of months/ ...] column to how many columns you have moved over. If the number of months is greater than or equal to the number of columns we have moved to the right, then it should populate. If the number is less than how many columns we have moved over, then the output should be blank.

Paul Newcome · 02/05/21

Try inserting this into [Payment 1] and then dragfilling across the rest of the payment columns (make sure the column names are correct)...

=IF($[# of months/ ...]@row >= COUNTIFS($Balance@row:Balance@row, OR(@cell = "", @cell <> "")), $[Phase Allotted]@row / $[# of months/ ...]@row)

This can also be set as a column formula in each of the payment columns.

Rosa Crisostomo · 02/05/21

Hi Paul,

Thank you so much for your help! The formula worked, however, should it populate an amount past the indicated # of months? For instance, the payment for $77,760 should be invoiced over 6 months, but when I drag filled the formula across the rest of the payment columns, it populates an amount past payment 6. Can we get these columns to zero out once the # of months/payments indicated are met?

Paul Newcome · 02/08/21

Are you able to copy/paste your exact formula directly from your sheet? It shouldn't be doing that as the first portion of the IF is comparing the number in the [# of months/ ...] column to how many columns you have moved over. If the number of months is greater than or equal to the number of columns we have moved to the right, then it should populate. If the number is less than how many columns we have moved over, then the output should be blank.

Rosa Crisostomo · 02/09/21

Hi Paul,

I re-checked my formula, looks like I was missing the $ in front of [($Balance@row:Balance@row]. It works like a charm, now! Thank you so much!!

Best,

Rosa

Paul Newcome · 02/09/21

That would do it. Happy to help. 👍️

Rosa Crisostomo · 02/09/21

Hi Paul,

I have one more question regarding the same sheet. I would like for [P1 Paid] to be checked off automatically if the corresponding invoice # in [P1 Invoice] is referenced in [Base Contract Amount] AND [Paid] is checked. Here is the formula I have but it's obviously not working for me:

=IF(AND([Paid]:[Paid] = 1, [Base Contract Amount]:[Base Contract Amount] = [P1 Invoice]@row), 1, 0)

THANK YOU AGAIN!!!

Paul Newcome · 02/10/21

Try this...

=IF(COUNTIFS([Base Contract Amount]:[Base Contract Amount], [P1 Invoice]@row, Paid:Paid, 1) = 1, 1)

Rosa Crisostomo · 02/10/21

It worked! Thanks again, Paul! 😊

Paul Newcome · 02/10/21

Happy to help. 👍️

Formulas and Functions

Formulas and Functions

Splitting Amount into equal payments across several cells

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Counting occurrences of a string in a row

Rounding to nearest Thursday in the past or current day if it's Thursday

JOIN function in a Sheet Summary