Change to positive /negative where applicable to arrive at final total

[sleek12]

cross-posted from another forum:-

Change to positive /negative where applicable to arrive at final total​

I managed to get all reconciliation items, but whether its negative/positive aspect sometimes is confusing or elusive.
This is the PROBLEM:- (I was trying to use SOLVER add-in here), however any solution with PQ, Macro is well as I use office 365.

Tenant (Debtor) Transactions1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1				Kshs					currency
2	Balance as per tenant								2,597,184.10
3	Add: invoices not posted by us	01/11/2021	b901	Deposit Charge	Deposit charge		4,715,615.76	0.00	4,715,615.76	1	4,715,615.76
4		01/11/2021	b776	Other Recoverable Expense	Reletting fees for tenant		348,000.00	0.00	348,000.00	1	348,000.00
5		01/11/2021	b776	Other Recoverable Expense	Legal fees for tenant		74,600.00	0.00	74,600.00	1	74,600.00
6		01/12/2021	i011	Rent		For period 15/12/2021 to 14/03/2022	1,578,000.00	252,480.00	1,830,480.00	1	1,830,480.00
7		01/12/2021	i014	Parking Rent		For period 15/12/2021 to 14/03/2022	231,000.00	36,960.00	267,960.00	1	267,960.00
8		01/03/2022	i011	Rent		For period 15/03/2022 to 14/06/2022	1,578,000.00	252,480.00	1,830,480.00	1	1,830,480.00
9		01/03/2022	i014	Parking Rent		For period 15/03/2022 to 14/06/2022	231,000.00	36,960.00	267,960.00	1	267,960.00
10	Add: payments made by ourselves not reflected in landlord						01/07/2021	3487	375,000.00	1	375,000.00
11	chq 3850 posted as 375500 instead of 375000								500.00	1	500.00
12	less: payments made by ourselves not updated in ERP						26/10/2021	1824	401,542.85	1	401,542.85
13	less: deposit refund not posted in ERP						25/11/2021		9,358,656.00	1	9,358,656.00
14	less credit note refund not posted by ourselves						15/03/2022		46,766.00	1	46,766.00
15	Add: invoice posted in ERP not reflected in landlord, why ???								2,075,878.92	1	2,075,878.92
16	opening bal adjustment							MO	4,527,542.01	1	4,527,542.01
17								tenant	2,923,365.18	1	2,923,365.18
18	Balance as per landlord								31,641,530.82	2,029,112.92
Problemdefined

Cell Formulas
Range	Formula
I3:I17	I3	=K3*J3
I18	I18	=SUM(I2:I17)
J18	J18	=solutionrequired!I20

Named Ranges
Name	Refers To	Cells
solver_adj	=Problemdefined!$J$3:$J$17	I3
solver_lhs1	=Problemdefined!$J$8	I8
solver_lhs10	=Problemdefined!$J$17	I17
solver_lhs11	=Problemdefined!$J$3	I3
solver_lhs12	=Problemdefined!$J$4	I4
solver_lhs13	=Problemdefined!$J$5	I5
solver_lhs14	=Problemdefined!$J$6	I6
solver_lhs15	=Problemdefined!$J$7	I7
solver_lhs2	=Problemdefined!$J$9	I9
solver_lhs3	=Problemdefined!$J$10	I10
solver_lhs4	=Problemdefined!$J$11	I11
solver_lhs5	=Problemdefined!$J$12	I12
solver_lhs6	=Problemdefined!$J$13	I13
solver_lhs7	=Problemdefined!$J$14	I14
solver_lhs8	=Problemdefined!$J$15	I15
solver_lhs9	=Problemdefined!$J$16	I16

Herewith is the SOLUTION:- what i wanted was is there any way i can turn some of the numbers into negative to total to 2,029,112.92 so my reconciliation can agree:-

Tenant (Debtor) Transactions1.xlsx
	A	B	C	D	E	F	G	H	I
1				Kshs					currency
2	Balance as per tenant								2,597,184.10
3	Add: invoices not posted by
4	ourselves
5		01/11/2021	b901	Deposit Charge	Deposit charge		4,715,615.76	0.00	4,715,615.76
6		01/11/2021	b776	Other Recoverable Expense	Reletting fees for tenant		348,000.00	0.00	348,000.00
7		01/11/2021	b776	Other Recoverable Expense	Legal fees for tenant		74,600.00	0.00	74,600.00
8		01/12/2021	i011	Rent		For period 15/12/2021 to 14/03/2022	1,578,000.00	252,480.00	1,830,480.00
9		01/12/2021	i014	Parking Rent		For period 15/12/2021 to 14/03/2022	231,000.00	36,960.00	267,960.00
10		01/03/2022	i011	Rent		For period 15/03/2022 to 14/06/2022	1,578,000.00	252,480.00	1,830,480.00
11		01/03/2022	i014	Parking Rent		For period 15/03/2022 to 14/06/2022	231,000.00	36,960.00	267,960.00
12	Add: payments made by ourselves not reflected in landlord						01/07/2021	3487	375,000.00
13	chq 3850 posted as 375500 instead of 375000								500.00
14	less: payments made by ourselves not updated in ERP						26/10/2021	1824	-401,542.85
15	less: deposit refund not posted in ERP						25/11/2021		-9,358,656.00
16	less credit note refund not posted by ourselves						15/03/2022		-46,766.00
17	Add: invoice posted in ERP not reflected in landlord, why ???								-2,075,878.92
18	opening bal adjustment							MO	4,527,542.01
19								tenant	-2,923,365.18
20	Balance as per landlord								2,029,112.92
solutionrequired

Cell Formulas
Range	Formula
I20	I20	=SUM(I2:I19)

 

[sleek12]

LET! It's a great function for making better formulas. My excel at work doesn't have it. Our IT department is slow to rollout changes.

The reason for this also could be The limit of this method is 20 values (2^20 = 1048576, amount of rows in Excel). If you want more values, maybe PowerQuery or VBA is required.

 

[sleek12]

As you spoke of multiple solutions, as i was trying this out, it had 2 results:-
I just modified your workings to suit my needs. @JamesCanale

AMZN-Operating-Activities.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	11,478.00	11,478.00	1.00	1.00	11,478.00	0	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1			0	115	1	1	-1	-1	1	1	1	-1	-1	-1
2	4,215.00	4,215.00	1.00	1.00	4,215.00	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1			0	903	1	1	1	-1	-1	-1	-1	1	1	1
3	-202.00	202.00	-1.00	1.00	202.00	2	-1	1	-1	-1	-1	-1	-1	-1	-1	-1			0
4	-292.00	-292.00	-1.00	-1.00	292.00	3	1	1	-1	-1	-1	-1	-1	-1	-1	-1			0
5	29.00	-29.00	1.00	-1.00	29.00	4	-1	-1	1	-1	-1	-1	-1	-1	-1	-1			0
6	3,583.00	-3,583.00	1.00	-1.00	3,583.00	5	1	-1	1	-1	-1	-1	-1	-1	-1	-1			0
7	4,786.00	-4,786.00	1.00	-1.00	4,786.00	6	-1	1	1	-1	-1	-1	-1	-1	-1	-1			0
8	-7,175.00	7,175.00	-1.00	1.00	7,175.00	7	1	1	1	-1	-1	-1	-1	-1	-1	-1			0
9	-283.00	283.00	-1.00	1.00	283.00	8	-1	-1	-1	1	-1	-1	-1	-1	-1	-1			0
10	-738.00	738.00	-1.00	1.00	738.00	9	1	-1	-1	1	-1	-1	-1	-1	-1	-1			0
11	15,401.00	15,401.00				10	-1	1	-1	1	-1	-1	-1	-1	-1	-1			0
12						11	1	1	-1	1	-1	-1	-1	-1	-1	-1			0
13						12	-1	-1	1	1	-1	-1	-1	-1	-1	-1			0
14						13	1	-1	1	1	-1	-1	-1	-1	-1	-1			0
15						14	-1	1	1	1	-1	-1	-1	-1	-1	-1			0
16						15	1	1	1	1	-1	-1	-1	-1	-1	-1			0
17						16	-1	-1	-1	-1	1	-1	-1	-1	-1	-1			0
18					15,401.00	17	1	-1	-1	-1	1	-1	-1	-1	-1	-1			0
19					10	18	-1	1	-1	-1	1	-1	-1	-1	-1	-1			0
fromothersite

Cell Formulas
Range	Formula
A1:A10	A1	=E1*C1
B1:B10	B1	=E1*D1
C1:D10	C1	=TRANSPOSE(U1#)
F1:F1024	F1	=SEQUENCE(2^E19)-1
G1:P1024	G1	=SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(E19,1,0,1))),F1#)-0.5)
S1:S1024	S1	=(--(MMULT(G1#,E1:E10)=$E$18))*F1#
T1:T2	T1	=FILTER(S1#,S1#<>0)
U1:AD2	U1	=SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(E19,1,0,1))),T1#)-0.5)
A11:B11	A11	=SUM(A1:A10)
E18	E18	='Table 1'!I13-'Table 1'!I1
E19	E19	=COUNT(E1:E10)
Dynamic array formulas.