Aging according to balance of account and invoices constitute the balance for all customers

[askall1000]

Hello to All,

I have customers statements derived from accounting software. I want to formulate the balance of each customer according to date of invoices had been issued. And I will use this formula frequently by changing the date at C1 cell and there is more than 50 customers. In reality there are also payments received but purpose of mine is find the invoices that constitute balance of account according to day groups mention in M3:R3. If account has no balance it should be zero. If the sum of invoices exceed balance, it should not take into consideration whole amount of invoice just the needed balance. The difference between S8 and balance (K8) must be zero.

Thank you very much in advance who spare their valuable time to help me.

Aging Balance 2023.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Date	01.04.2023
2
3	Current Code	Company Name	Date	Voucher Num	Invoice Num	Description	Currency Code	FX Rate	Debit	Credit	Balance	Days	0-30	31-60	61-90	91-120	121-150	150+	Total	Difference
4	CU03	Customer2	01.11.2022				USD		25.378,78		25.378,78	151,00
5	CU03	Customer2	01.02.2023				USD		10.000,00		35.378,78	59,00
6	CU03	Customer2	14.03.2023				USD		25.849,09		61.227,87	18,00
7	CU03	Customer2	14.03.2023				USD		3.320,85		64.548,72	18,00
8	CU03	Customer2	17.03.2023				USD			23.706,00	40.842,72	15,00	29.169,94	10.000,00	0	0,00	0,00	1.672,78	40.842,72	0,00
9	….	…..
10	….	…..									0,00
11	….	…..
12	….	…..
13	….	…..
Sheet1

Cell Formulas
Range	Formula
K4	K4	=+I4-J4
L4:L8	L4	=+$C$1-C4
K5:K8	K5	=+K4+I5-J5
M8	M8	=+SUM(I6:I7)
N8	N8	=+I5
S8	S8	=+SUM(M8:R8)
T8	T8	=+K8-S8
K10	K10	=+K8-S8

 

[excel_learnerz]

Based on the information you provided, it seems like you need a formula that will calculate the balance of each customer based on the date of the invoices that were issued, taking into account payments received and grouping the invoices by day. Here is a possible solution using the data and formulas you provided:

1. First, create a table with the following columns: Customer Name, Invoice Date, Invoice Amount, Payment Amount, Balance. Make sure the table includes all invoices and payments for all customers.
2. Next, insert a row above the table and merge cells M2:R2. In cell M2, enter the following formula: =IF($C$1="",0,IFERROR(DATEVALUE($C$1),"Invalid Date")). This formula checks if the date in cell C1 is blank, and if not, converts it to a date value. If there is an error in the date value, it will display "Invalid Date".
3. In cells M3:R3, enter the day groupings you want to use. For example, you could enter "1-10", "11-20", "21-31" if you want to group invoices by the day of the month they were issued.
4. In cell S2, enter the following formula: =SUMIF(B:B,"<="&M$2,C:C)-SUMIF(B:B,"<="&M$2,D:D). This formula calculates the balance for invoices issued on or before the date in cell M2. It does this by summing all invoice amounts in column C that are on or before the date in cell M2, and subtracting all payment amounts in column D that are on or before the date in cell M2.
5. Copy the formula in cell S2 to cells T2:Y2. These cells will calculate the balance for each day grouping in cells M3:R3.
6. In cell Z2, enter the following formula: =SUM(S2:Y2). This formula calculates the total balance for all customers based on the day groupings in cells M3:R3.
7. In cell AA2, enter the following formula: =IF(K8-Z2<0,K8,Z2). This formula checks if the total balance in cell Z2 exceeds the balance in cell K8. If it does, it displays the balance in cell K8. If not, it displays the total balance in cell Z2.
8. Finally, in cell AB2, enter the following formula: =K8-AA2. This formula calculates the difference between the balance in cell K8 and the total balance in cell AA2. If the difference is zero, then the formula has been successful.

You can then copy the formulas in cells AA2 and AB2 to all customers in your table, and the balance for each customer will be calculated based on the date of the invoices issued, taking into account payments received and grouping the invoices by day.

 

[askall1000]

Thank

Based on the information you provided, it seems like you need a formula that will calculate the balance of each customer based on the date of the invoices that were issued, taking into account payments received and grouping the invoices by day. Here is a possible solution using the data and formulas you provided:

1. First, create a table with the following columns: Customer Name, Invoice Date, Invoice Amount, Payment Amount, Balance. Make sure the table includes all invoices and payments for all customers.
2. Next, insert a row above the table and merge cells M2:R2. In cell M2, enter the following formula: =IF($C$1="",0,IFERROR(DATEVALUE($C$1),"Invalid Date")). This formula checks if the date in cell C1 is blank, and if not, converts it to a date value. If there is an error in the date value, it will display "Invalid Date".
3. In cells M3:R3, enter the day groupings you want to use. For example, you could enter "1-10", "11-20", "21-31" if you want to group invoices by the day of the month they were issued.
4. In cell S2, enter the following formula: =SUMIF(B:B,"<="&M$2,C:C)-SUMIF(B:B,"<="&M$2,D:D). This formula calculates the balance for invoices issued on or before the date in cell M2. It does this by summing all invoice amounts in column C that are on or before the date in cell M2, and subtracting all payment amounts in column D that are on or before the date in cell M2.
5. Copy the formula in cell S2 to cells T2:Y2. These cells will calculate the balance for each day grouping in cells M3:R3.
6. In cell Z2, enter the following formula: =SUM(S2:Y2). This formula calculates the total balance for all customers based on the day groupings in cells M3:R3.
7. In cell AA2, enter the following formula: =IF(K8-Z2<0,K8,Z2). This formula checks if the total balance in cell Z2 exceeds the balance in cell K8. If it does, it displays the balance in cell K8. If not, it displays the total balance in cell Z2.
8. Finally, in cell AB2, enter the following formula: =K8-AA2. This formula calculates the difference between the balance in cell K8 and the total balance in cell AA2. If the difference is zero, then the formula has been successful.

You can then copy the formulas in cells AA2 and AB2 to all customers in your table, and the balance for each customer will be calculated based on the date of the invoices issued, taking into account payments received and grouping the invoices by day.

Thank you very much for your detail explanation and help.