FORMULA TO OFFSET NEGATIVE AND POSITVE NUMBERS

[Tonya1205]

Good morning 

I am looking for a formula where I can offset a worksheet with positive and negative numbers without manually finding the opposite number. For example if I have 100 I want to be able to find the corresponding -100 to offset Excel formulas and functions Excel formulas and functions 

[#2]

Hi Tonya1205, can you provide some context? Are you actually searching for this "opposite" in some range of cells?

The "opposite" of any number is just that number multiplied by -1.

So, you can use FIND, XLOOKUP, VLOOKUP, MATCH, XMATCH and so on by passing that into the search parameter of whichever function you choose.

If you upload an example, someone can help more easily.

[#3]

@flexyourdata this is something simple but I have a set of numbers both positive and negative and I am trying to create a formula where a can match the positive to the negative number so that it will net out to be zero. I will be able to separate the two to see what numbers don't have an offsetting number 

1304888	950061	9/8/2020	INVOICE	116.44
1313746	956297	10/5/2020	INVOICE	3,379.49
1314524	956273	10/8/2020	INVOICE	469.44
1316507	957971	10/12/2020	INVOICE	5,046.93
1316843	957949	10/13/2020	INVOICE	319.47
1316845	957948	10/13/2020	INVOICE	511.95
1319153	959680	10/19/2020	INVOICE	3,314.40
1319180	959680   ADD	10/19/2020	INVOICE	237.3
1319721	959664	10/20/2020	INVOICE	460.31
1319739	959670	10/20/2020	INVOICE	376.61
1321015	960672	10/26/2020	INVOICE	2,032.20
1321960	961296	10/26/2020	INVOICE	4,916.23
1322223	961267	10/27/2020	INVOICE	548.74
1322454	961266	10/27/2020	INVOICE	556.72
1304888	950061	9/8/2020	INVOICE	-116.44
1313746	956297	10/5/2020	INVOICE	-3,379.49
1314524	956273	10/8/2020	INVOICE	-0.46944
1316507	957971	10/12/2020	INVOICE	-5,046.93
1316843	957949	10/13/2020	INVOICE	-319.47
1316845	957948	10/13/2020	INVOICE	-511.95
1319153	959680	10/19/2020	INVOICE	-3,314.40
1319180	959680   ADD	10/19/2020	INVOICE	-237.3
1319721	959664	10/20/2020	INVOICE	-460.31
1319739	959670	10/20/2020	INVOICE	-376.61
1321015	960672	10/26/2020	INVOICE	-2,032.20
1321960	961296	10/26/2020	INVOICE	4,916.23
1322223	961267	10/27/2020	INVOICE	548.74
1322454	961266	10/27/2020	INVOICE	1304888	950061	9/8/2020	INVOICE	116.44
				1313746	956297	10/5/2020	INVOICE	3,379.49
				1314524	956273	10/8/2020	INVOICE	469.44
				1316507	957971	10/12/2020	INVOICE	5,046.93
				1316843	957949	10/13/2020	INVOICE	319.47
				1316845	957948	10/13/2020	INVOICE	511.95
				1319153	959680	10/19/2020	INVOICE	3,314.40
				1319180	959680   ADD	10/19/2020	INVOICE	237.3
				1319721	959664	10/20/2020	INVOICE	460.31
				1319739	959670	10/20/2020	INVOICE	376.61
				1321015	960672	10/26/2020	INVOICE	2,032.20
				1321960	961296	10/26/2020	INVOICE	4,916.23
				1322223	961267	10/27/2020	INVOICE	548.74
				1322454	961266	10/27/2020	INVOICE	556.72
				1304888	950061	9/8/2020	INVOICE	-116.44
				1313746	956297	10/5/2020	INVOICE	-3,379.49
				1314524	956273	10/8/2020	INVOICE	-0.46944
				1316507	957971	10/12/2020	INVOICE	-5,046.93
				1316843	957949	10/13/2020	INVOICE	-319.47
				1316845	957948	10/13/2020	INVOICE	-511.95
				1319153	959680	10/19/2020	INVOICE	-3,314.40
				1319180	959680   ADD	10/19/2020	INVOICE	-237.3
				1319721	959664	10/20/2020	INVOICE	-460.31
				1319739	959670	10/20/2020	INVOICE	-376.61
				1321015	960672	10/26/2020	INVOICE	-2,032.20
				1321960	961296	10/26/2020	INVOICE	4,916.23
				1322223	961267	10/27/2020	INVOICE	548.74
				1322454	961266	10/27/2020	INVOICE	556.72

[#4]

@Tonya1205 Perhaps a pivot table (see attached) is what you need. Not sure though what the data at the bottom is for. It just seems to be a duplication op the top part but shifted a few columns.

[#5]

I thought about a pivot table but it won't work for what I need. I will try to give a better example. I am trying to offset a balance sheet and need a formula so that I am not going line by line to find the offsetting transaction.

[#6]

@Tonya1205 Perhaps the attached file contains something you could use. It a small example I picked up a long time ago on myonlinetraininghub. Originally meant for bank reconciliations, you can use the same technique for for matching debits and credits in any transaction list. The helper column B in the table holds the formula that finds offsetting amounts.