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)

 

[JamesCanale]

I'm not sure that I have what you are looking for, and there was not a solution for the numbers you have - as I understood them. But here is what I prepared.

I've got it spread out to make it understandable, but it could be boiled down to one or two actual formulas without all the intermediate steps.

MrExcelPlayground7.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	AE	AF	AG	AH	AI	AJ	AK
1	$4,715,615.76		$4,715,615.76	0	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0	27934	-1	1	1	1	1	-1	-1	-1	1	-1	1	1	-1	1	1
2	$348,000.00		$348,000.00	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0	27958	-1	1	1	-1	1	1	-1	-1	1	-1	1	1	-1	1	1
3	$74,600.00		$74,600.00	2	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0	27982	-1	1	1	1	-1	-1	1	-1	1	-1	1	1	-1	1	1
4	$1,830,480.00		$1,830,480.00	3	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0	28006	-1	1	1	-1	-1	1	1	-1	1	-1	1	1	-1	1	1
5	$267,960.00		$267,960.00	4	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
6	$1,830,480.00		$1,830,480.00	5	1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
7	$267,960.00		$267,960.00	6	-1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
8	$375,000.00		$375,000.00	7	1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
9	$500.00		$500.00	8	-1	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
10	-$401,542.85		$401,542.85	9	1	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
11	-$9,358,656.00		$9,358,656.00	10	-1	1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
12	-$46,766.00		$46,766.00	11	1	1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
13	-$2,075,878.92		$2,075,878.92	12	-1	-1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
14	$4,527,542.01		$4,527,542.01	13	1	-1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
15	-$2,923,365.18		$2,923,365.18	14	-1	1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
16				15	1	1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
17				16	-1	-1	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
18			$9,711,391.66	17	1	-1	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
19				18	-1	1	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
20			2029112.92	19	1	1	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
Sheet21

Cell Formulas
Range	Formula
C1:C15	C1	=ABS(A1:A15)
D1:D32768	D1	=SEQUENCE(2^15)-1
E1:S32768,W1:AK4	E1	=SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(15,1,0,1))),D1#)-0.5)
U1:U32768	U1	=(--(MMULT(E1#,C1#)=C18))*D1#
V1:V4	V1	=FILTER(U1#,U1#<>0)
Dynamic array formulas.

 

[sleek12]

No, @JamesCanale actually you're supposed to start with absolute values, then from thereon you determine which amounts can be made negative to arrive at final total. (its like Goal seek but with multiple amounts)
Start with opening balance:-
2597184.1
then from the list of absolute values , determine which of the set to make negative so as to reach 2,029,112.92.
These was the solution see below:- the ones that became RED (negative), the trick is there a macro or PQ or formula to change absolute to negative so i arrive at my predetermined total 2,029,112.92

Tenant (Debtor) Transactions1.xlsx
	I
5	4,715,615.76
6	348,000.00
7	74,600.00
8	1,830,480.00
9	267,960.00
10	1,830,480.00
11	267,960.00
12	375,000.00
13	500.00
14	-401,542.85
15	-9,358,656.00
16	-46,766.00
17	-2,075,878.92
18	4,527,542.01
19	-2,923,365.18
solutionrequired

 

[JamesCanale]

I think that I have what you want. I don't do a goal seek - I have excel do every possible combination of +/- for 15 numbers. I just don't get this set to add up to the number you are looking for.

The way you have it shown here it doesn't add up to 2.5M, it's -568,071.18.

 

[sleek12]

I think that I have what you want. I don't do a goal seek - I have excel do every possible combination of +/- for 15 numbers. I just don't get this set to add up to the number you are looking for.

The way you have it shown here it doesn't add up to 2.5M, it's -568,071.18.

But I informed you of opening balance of 2597184.1 in my thread @JamesCanale add that to _568071.18

 

[JamesCanale]

I see now - and there is the one solution that you had. So here it is with my calculations:

MrExcelPlayground7.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	AE	AF	AG	AH	AI	AJ	AK
1	$4,715,615.76	1	$4,715,615.76	0	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0	8703	1	1	1	1	1	1	1	1	1	-1	-1	-1	-1	1	-1
2	$348,000.00	1	$348,000.00	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
3	$74,600.00	1	$74,600.00	2	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
4	$1,830,480.00	1	$1,830,480.00	3	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
5	$267,960.00	1	$267,960.00	4	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
6	$1,830,480.00	1	$1,830,480.00	5	1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
7	$267,960.00	1	$267,960.00	6	-1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
8	$375,000.00	1	$375,000.00	7	1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
9	$500.00	1	$500.00	8	-1	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
10	-$401,542.85	-1	$401,542.85	9	1	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
11	-$9,358,656.00	-1	$9,358,656.00	10	-1	1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
12	-$46,766.00	-1	$46,766.00	11	1	1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
13	-$2,075,878.92	-1	$2,075,878.92	12	-1	-1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
14	$4,527,542.01	1	$4,527,542.01	13	1	-1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
15	-$2,923,365.18	-1	$2,923,365.18	14	-1	1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
16				15	1	1	1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
17				16	-1	-1	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
18			-568071.18	17	1	-1	-1	-1	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1		0
Sheet21

Cell Formulas
Range	Formula
B1:B15	B1	=TRANSPOSE(W1#)
C1:C15	C1	=ABS(A1:A15)
D1:D32768	D1	=SEQUENCE(2^15)-1
E1:S32768,W1:AK1	E1	=SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(15,1,0,1))),D1#)-0.5)
U1:U32768	U1	=(--(MMULT(E1#,C1#)=C18))*D1#
V1	V1	=FILTER(U1#,U1#<>0)
C18	C18	=2029112.92-2597184.1
Dynamic array formulas.

Or boiled down:

MrExcelPlayground7.xlsx
	A	B	C	D
1			Coefficients	Values
2	Start	$2,597,184.10	1	$4,715,615.76
3	Finish	$2,029,112.92	1	$348,000.00
4	Change	-$568,071.18	1	$74,600.00
5			1	$1,830,480.00
6			1	$267,960.00
7			1	$1,830,480.00
8			1	$267,960.00
9			1	$375,000.00
10			1	$500.00
11			-1	$401,542.85
12			-1	$9,358,656.00
13			-1	$46,766.00
14			-1	$2,075,878.92
15			1	$4,527,542.01
16			-1	$2,923,365.18
Sheet21 (2)

Cell Formulas
Range	Formula
C2:C16	C2	=TRANSPOSE(SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(ROWS(D2:D16),1,0,1))),FILTER((--(MMULT(SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(ROWS(D2:D16),1,0,1))),(SEQUENCE(2^ROWS(D2:D16))-1))-0.5),(D2:D16))=B4))*(SEQUENCE(2^ROWS(D2:D16))-1),(--(MMULT(SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(ROWS(D2:D16),1,0,1))),(SEQUENCE(2^ROWS(D2:D16))-1))-0.5),(D2:D16))=B4))*(SEQUENCE(2^ROWS(D2:D16))-1)<>0))-0.5))
B4	B4	=B3-B2
Dynamic array formulas.

 

[sleek12]

Thanks, it works perfectly @JamesCanale , I can discern what you did , First you changed everything to absolute. Then =SEQUENCE(2^15)-1 and =SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(15,1,0,1))),D2#)-0.5)to find out no. of combinations of 1 and -1, ==(--(MMULT(E1#,C1#)=C18))*D1# for difference in cell c18,The row in which the optimum combination is found =FILTER(U1#,U1#<>0), and of course to place it in columnar form =SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(15,1,0,1))),V1#)-0.5) and =TRANSPOSE(W1#) were the steps.
However when i used the same in a different scenario , i got #VALUE error

STETATEMENT JULY.xlsx
	E	F
2	Start	794,345.52
3		-100,000.00
4		-100,000.00
5		-100,000.00
6		479,213.54
7		-100,000.00
8		-100,000.00
9		-200,000.00
10		479,213.54
11		-87,000.00
12		-87,000.00
13		-87,000.00
14		-87,000.00
15		-83,000.00
16		-83,000.00
17		-83,000.00
18		-87,000.00
19		-87,000.00
20		-87,000.00
21		479,213.54
22		-87,000.00
23		-87,000.00
24		-20,772.00
25	Finish	479,214.14
Sheet1

Cell Formulas
Range	Formula
F25	F25	=SUM(F2:F24)

Wherein am i going wrong ? I've reviewed the formula several times ??

STETATEMENT JULY.xlsx
	A	B	C	D
2	Start	794,345.52	#VALUE!	100,000.00
3	Finish	479,214.14	#VALUE!	100,000.00
4	Change	-315,131.38	#VALUE!	100,000.00
5			#VALUE!	479,213.54
6			#VALUE!	100,000.00
7			#VALUE!	100,000.00
8			#VALUE!	200,000.00
9			#VALUE!	479,213.54
10			#VALUE!	87,000.00
11			#VALUE!	87,000.00
12			#VALUE!	87,000.00
13			#VALUE!	87,000.00
14			#VALUE!	83,000.00
15			#VALUE!	83,000.00
16			#VALUE!	83,000.00
17			#VALUE!	87,000.00
18			#VALUE!	87,000.00
19			#VALUE!	87,000.00
20			#VALUE!	479,213.54
21			#VALUE!	87,000.00
22			#VALUE!	87,000.00
23			#VALUE!	20,772.00
Sheet1

Cell Formulas
Range	Formula
C2:C23	C2	=TRANSPOSE(SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(ROWS(D2:D23),1,0,1))),FILTER((--(MMULT(SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(ROWS(D2:D23),1,0,1))),(SEQUENCE(2^ROWS(D2:D23))-1))-0.5),(D2:D23))=B4))*(SEQUENCE(2^ROWS(D2:D23))-1),(--(MMULT(SIGN(BITAND(TRANSPOSE(2^(SEQUENCE(ROWS(D2:D23),1,0,1))),(SEQUENCE(2^ROWS(D2:D23))-1))-0.5),(D2:D23))=B4))*(SEQUENCE(2^ROWS(D2:D23))-1)<>0))-0.5))
B4	B4	=B3-B2
Dynamic array formulas.

 

[JamesCanale]

I'm thinking that there was multiple solutions after the FILTER. You'r data looks like that would be certain. And the formula got itself into trouble and wouldn't pick just one correctly. I'll have to see about changing it to take the first row of the filter. Or maybe you would like to know when there are multiple solutions.

 

[sleek12]

Anyway, someone from the other forum provided me too with a solution:-
A bit more concise than yours , see below, so let me see how i fare:-

Tenant (Debtor) Transactions1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2									currency	n ►	15
3
4	Balance as per tenant			Kshs					2,597,184.10		2,597,184.10
5
6	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
7		01/11/2021	b776	Other Recoverable Expense	Reletting fees for tenant		348,000.00	0.00	348,000.00	1	348,000.00
8		01/11/2021	b776	Other Recoverable Expense	Legal fees for tenant		74,600.00	0.00	74,600.00	1	74,600.00
9		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
10		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
11		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
12		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
13	Add: payments made by ourselves not reflected in landlord						01/07/2021	3487	375,000.00	1	375,000.00
14	chq 3850 posted as 375500 instead of 375000								500.00	1	500.00
15	less: payments made by ourselves not updated in ERP						26/10/2021	1824	- 401,542.85	-1	401,542.85
16	less: deposit refund not posted in ERP						25/11/2021		- 9,358,656.00	-1	9,358,656.00
17	less credit note refund not posted by ourselves						15/03/2022		- 46,766.00	-1	46,766.00
18	Add: invoice posted in ERP not reflected in landlord, why ???								- 2,075,878.92	-1	2,075,878.92
19	opening bal adjustment							MO	4,527,542.01	1	4,527,542.01
20								tenant	- 2,923,365.18	-1	2,923,365.18
21	Balance as per landlord								- 568,071.18		29,044,346.72
Problemdefined

Cell Formulas
Range	Formula
K2	K2	=COUNT(K6:K20)
I6:I20	I6	=J6:J20*K6:K20
J6:J20	J6	=LET(b,1-2*MID(BASE(SEQUENCE(2^K2)-1,2,K2),SEQUENCE(,K2),1), TRANSPOSE(XLOOKUP(I24,ROUND(MMULT(b,K6:K20),2),b)))
I21	I21	=SUM(I6#)
K21	K21	=SUM(K6:K20)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
solver_adj	=Problemdefined!$J$6:$J$20	I6
solver_lhs1	=Problemdefined!$J$10	I6
solver_lhs10	=Problemdefined!$J$19	I6
solver_lhs11	=Problemdefined!$J$20	I6
solver_lhs12	=Problemdefined!$J$6	I6
solver_lhs13	=Problemdefined!$J$7	I6
solver_lhs14	=Problemdefined!$J$8	I6
solver_lhs15	=Problemdefined!$J$9	I6
solver_lhs2	=Problemdefined!$J$11	I6
solver_lhs3	=Problemdefined!$J$12	I6
solver_lhs4	=Problemdefined!$J$13	I6
solver_lhs5	=Problemdefined!$J$14	I6
solver_lhs6	=Problemdefined!$J$15	I6
solver_lhs7	=Problemdefined!$J$16	I6
solver_lhs8	=Problemdefined!$J$17	I6
solver_lhs9	=Problemdefined!$J$18	I6

 

[JamesCanale]

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.