If/then formula to show balance due

Tori Lapolla

Hi

I'm looking to create a formula calculating balance due. We have 12 possible payments to deduct from the total, and need to see the balance at any given time. The way we have the sheet set up is a "Total Gross" column and then for Payments 1-12 there is a column for "Payment 1 Amount" which is what is expected, and then a column for "Payment 1 Received" however that column records a date, not the amount of the payment. Then repeated for Payments 2-12. I am looking to create a column that would only deduct the "Payment Amount" if a date has been entered in "Payment Received"

I realize the easy fix would be to add another column for the actual amount received and just deduct those, however this sheet has approx 5k rows of data and unfortunately would be way too much to go back in and input potentially 60k payments. Therefore, configuring this formula is the route we have decided to go. I have tried numerous times, but keep getting errors and was hoping someone with more formula experience could help me sort it out

Thank you!

Carson Penticuff

If I understand you correctly, this should do it. This assumes the dates in your Payment X Received columns are correctly formatted as dates.

=[Total Gross]@row - IF(ISDATE([Payment 1 Received]@row), [Payment 1 Amount]@row, 0) - IF(ISDATE([Payment 2 Received]@row), [Payment 2 Amount]@row, 0) - IF(ISDATE([Payment 3 Received]@row), [Payment 3 Amount]@row, 0) - IF(ISDATE([Payment 4 Received]@row), [Payment 4 Amount]@row, 0) - IF(ISDATE([Payment 5 Received]@row), [Payment 5 Amount]@row, 0) - IF(ISDATE([Payment 6 Received]@row), [Payment 6 Amount]@row, 0) - IF(ISDATE([Payment 7 Received]@row), [Payment 7 Amount]@row, 0) - IF(ISDATE([Payment 8 Received]@row), [Payment 8 Amount]@row, 0) - IF(ISDATE([Payment 9 Received]@row), [Payment 9 Amount]@row, 0) - IF(ISDATE([Payment 10 Received]@row), [Payment 10 Amount]@row, 0) - IF(ISDATE([Payment 11 Received]@row), [Payment 11 Amount]@row, 0) - IF(ISDATE([Payment 12 Received]@row), [Payment 12 Amount]@row, 0)

Carson Penticuff

If I understand you correctly, this should do it. This assumes the dates in your Payment X Received columns are correctly formatted as dates.

=[Total Gross]@row - IF(ISDATE([Payment 1 Received]@row), [Payment 1 Amount]@row, 0) - IF(ISDATE([Payment 2 Received]@row), [Payment 2 Amount]@row, 0) - IF(ISDATE([Payment 3 Received]@row), [Payment 3 Amount]@row, 0) - IF(ISDATE([Payment 4 Received]@row), [Payment 4 Amount]@row, 0) - IF(ISDATE([Payment 5 Received]@row), [Payment 5 Amount]@row, 0) - IF(ISDATE([Payment 6 Received]@row), [Payment 6 Amount]@row, 0) - IF(ISDATE([Payment 7 Received]@row), [Payment 7 Amount]@row, 0) - IF(ISDATE([Payment 8 Received]@row), [Payment 8 Amount]@row, 0) - IF(ISDATE([Payment 9 Received]@row), [Payment 9 Amount]@row, 0) - IF(ISDATE([Payment 10 Received]@row), [Payment 10 Amount]@row, 0) - IF(ISDATE([Payment 11 Received]@row), [Payment 11 Amount]@row, 0) - IF(ISDATE([Payment 12 Received]@row), [Payment 12 Amount]@row, 0)

Tori Lapolla

Thank you very much! This seems to be working!