Convert Positive to negative and vice versa

[DIPASGL]

Hi

Please help me with below - thanks heaps

1 H	I	J	K	L	M	N	O
2 Currency Code	Gen Posting Type	Gen Bus. P..	Gen. Prod.	GSTBus. P...	Branch Code	Amount	GST Amount
3	Sale	GEN	GEN	CUSTOMER	125	-14,427.68	-1,311.61
4	Sale	GEN	GEN	CUSTOMER	125
5					125	2,240.60	0
6					125	12,445.38	0
7	Sale	GEN	GEN	CUSTOMER	125	-3.71	-0.34
8
9 Till Variance	3.71		Tax	$1,311.27
Queries Below
If i9 is positive then n7 should show negative
If i9 is negative then n7 should show positive
If n7 is negative then n3 should be tax L9 less N7 and multiply with 11 , After this calculation N3 shd still show have negative sign
If n7 is positive then n3 should be tax L9 plus 3.71 and multiply with 11 , After this calculation N3 shd still show have negative sign

 

[Habtest]

I don't get the same result testing with the data and it's a bit difficult without a XL2BB table.

You may try ABS() or SIGN() function, which works regardless or depending on the positivity/negativity of the number.

 

[DIPASGL]

I don't get the same result testing with the data and it's a bit difficult without a XL2BB table.

You may try ABS() or SIGN() function, which works regardless or depending on the positivity/negativity of the number.

I tried but its not giving me correct result.

 

[Habtest]

Hi, see if this works

Book1
	H	I	J	K	L	M	N	O
1	1 H	I	J	K	L	M	N	O
2	2 Currency Code	Gen Posting Type	Gen Bus. P..	Gen. Prod.	GSTBus. P...	Branch Code	Amount	GST Amount
3	3	Sale	GEN	GEN	CUSTOMER	125	-14,427.68	-1,311.61
4	4	Sale	GEN	GEN	CUSTOMER	125
5	5					125	2,240.60	0
6	6					125	12,445.38	0
7	7	Sale	GEN	GEN	CUSTOMER	125	-3.71	-0.34
8	8
9	9 Till Variance	3.71		Tax	$1,311.27
Sheet1

Cell Formulas
Range	Formula
N3	N3	=-L9*11-ABS(N7)
N7	N7	=-I9

 

[DIPASGL]

Thanks for reply but my query was - My excel file recognise the cell is in positive and change to negative or vice versa. Currently I need to do myself this. Also if it is positive then this formula itself change from plus to minus or vice versa.Thanks

=-L9*11-ABS(N7)

 

[Sufiyan97]

Try

Book1
	G	H	I	J	K	L	M	N	O	P
1		1 H	I	J	K	L	M	N	O
2		2 Currency Code	Gen Posting Type	Gen Bus. P..	Gen. Prod.	GSTBus. P...	Branch Code	Amount	GST Amount
3		3	Sale	GEN	GEN	CUSTOMER	125	-14,464.78	-1,311.61
4		4	Sale	GEN	GEN	CUSTOMER	125
5		5					125	2,240.60	0
6		6					125	12,445.38	0
7		7	Sale	GEN	GEN	CUSTOMER	125	-3.71	-0.34
8		8
9		9 Till Variance	3.71		Tax	$1,311.27
10
11
12
13
14
15
Sheet3

Cell Formulas
Range	Formula
N3	N3	=-IF(N7<0,(L9-N7)*11,(L9+N7)*11)
N7	N7	=I9*-1

 

[DIPASGL]

Thanks @Sufiyan,

It worked N3 but still for n7 , I HAVE TO remember that if variance is positive then multiply * -1 and I variance is negative then multiply I9 *1.
I want if I9 is positive then N7 should show with negative. Similarly
I want if I9 is negative then N7 should show with positive.
Thanks

 

[Sufiyan97]

It worked N3 but still for n7 , I HAVE TO remember that if variance is positive then multiply * -1 and I variance is negative then multiply I9 *1.
I want if I9 is positive then N7 should show with negative. Similarly
I want if I9 is negative then N7 should show with positive.
Thanks

I think that is what it does unless you want something else.

Book1
	G	H	I	J	K	L	M	N	O	P
1		1 H	I	J	K	L	M	N	O
2		2 Currency Code	Gen Posting Type	Gen Bus. P..	Gen. Prod.	GSTBus. P...	Branch Code	Amount	GST Amount
3		3	Sale	GEN	GEN	CUSTOMER	125	-14,464.78	-1,311.61
4		4	Sale	GEN	GEN	CUSTOMER	125
5		5					125	2,240.60	0
6		6					125	12,445.38	0
7		7	Sale	GEN	GEN	CUSTOMER	125	-3.71	-0.34
8		8						3.71
9		9 Till Variance	3.71		Tax	$1,311.27
10			-3.71
11
12
13
14
15
Sheet3

Cell Formulas
Range	Formula
N3	N3	=-IF(N7<0,(L9-N7)*11,(L9+N7)*11)
N7:N8	N7	=I9*-1

 

[DIPASGL]

Sorry asking again but when N7 is negative then your formula works but when I get negative N7 , then I am still getting same result, When n7 is plus means its greater then zero so N3 should less o8 like (1311.27-0.34)*11 .
Thanks

 

[Sufiyan97]

Sorry asking again but when N7 is negative then your formula works but when I get negative N7 , then I am still getting same result, When n7 is plus means its greater then zero so N3 should less o8 like (1311.27-0.34)*11 .
Thanks

So what you want as result if N7 is positive

N3 should be negative or positive?

See if that is what you want

Book1
	G	H	I	J	K	L	M	N	O	P
1		1 H	I	J	K	L	M	N	O
2		2 Currency Code	Gen Posting Type	Gen Bus. P..	Gen. Prod.	GSTBus. P...	Branch Code	Amount	GST Amount
3		3	Sale	GEN	GEN	CUSTOMER	125	14,464.78	-1,311.61
4		4	Sale	GEN	GEN	CUSTOMER	125
5		5					125	2,240.60	0
6		6					125	12,445.38	0
7		7	Sale	GEN	GEN	CUSTOMER	125	-3.71	-0.34
8		8						3.71
9		9 Till Variance	3.71		Tax	$1,311.27
10			-3.71
11
12
13
14
Sheet3

Cell Formulas
Range	Formula
N3	N3	=SIGN(-N7)*IF(N7<0,(L9-N7)*11,(L9+N7)*11)
N7:N8	N7	=I9*-1