Need to add +,-

[sksanjeev786]

Hi Team,

I have pulled the data with the formula
and I would like to add + or - if we have a positive number (+), negative (-) in the below formula

and for 0 no symbole. (data are in decimals )

=IF(INDEX(INDIRECT("'"&$A5&$B5&"'!A"&$C5&":IV"&$D5&""),MATCH($E5,INDIRECT("'"&$A5&$B5&"'!A"&$C5&":A"&$D5&""),0),H$2)*100>1&"+",INDEX(INDIRECT("'"&$A5&$B5&"'!A"&$C5&":IV"&$D5&""),MATCH($E5,INDIRECT("'"&$A5&$B5&"'!A"&$C5&":A"&$D5&""),0),H$2)*100)

 

[etaf]

can you give some examples of the result that you get

will the numbers returned be negative
so if you need a + in front of positive numbers you can format

custom format

+0;-0;0

not sure if XL2BB works for formatting
but here it is

Book6
	A
1	+1
2	+2
3	+3
4	+4
5	0
6	0
7	-1
8	-2
9	-3
Sheet1

 

[sksanjeev786]

can you give some examples of the result that you get

will the numbers returned be negative
so if you need a + in front of positive numbers you can format

custom format

+0;-0;0

not sure if XL2BB works for formatting
but here it is

Book6
	A
1	+1
2	+2
3	+3
4	+4
5	0
6	0
7	-1
8	-2
9	-3
Sheet1

HI Etaf,

Thanks for your time on this

Here is my data and in H5 i am getting -0 due to decimal score in cell

FS.xlsm
	B	C	D	E	F	G	H
5	Brand Metrics	1	16	Unaided Brand Awareness	9	8	-0
6	Brand Metrics	1	16	Aided Brand Awareness	93	96	+2
7	Brand Metrics	1	16	Message Association	17	20	+3
8	Brand Metrics	1	16	Consideration Intent	45	59	+14
9	Brand Metrics	1	16	Aided Brand Awareness	93	96	+2
10	Brand Metrics	1	16	Total Ad Recall	45	59	+13
Sheet1

Cell Formulas
Range	Formula
F5:H10	F5	=INDEX(INDIRECT("'"&$A5&$B5&"'!A"&$C5&":IV"&$D5&""),MATCH($E5,INDIRECT("'"&$A5&$B5&"'!A"&$C5&":A"&$D5&""),0),F$2)*100
D5:D10	D5	=MATCH($A$1,INDIRECT("'"&$A5&$B5&"'!A:A"),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H5:H15	Expression	=I5="p"	text	NO
H5:H15	Expression	=I5="q"	text	NO
H5:H15	Expression	=I5="r"	text	NO

 

[etaf]

what + - range would you consider as zero ?
you could add another part to the IF
do all the numbers have a decimal and are you just displaying without decimals ?
you could use TRUNC() , INT() , Round() ,Roundup() , Rounddown()

Just depends on how that impacts all the numbers for you and if using elsewhere

 

[sksanjeev786]

what + - range would you consider as zero ?
you could add another part to the IF
do all the numbers have a decimal and are you just displaying without decimals ?
you could use TRUNC() , INT() , Round() ,Roundup() , Rounddown()

Just depends on how that impacts all the numbers for you and if using elsewhere

Thanks for the clarification sure will use the above function

here is the Zero data I have in excel

-0.403298031010706%

 

[etaf]

round() will change that - BUT also all your other numbers

Round() will round down decimals upto 0.5 and up after

Example Question-ETAF.xlsx
	A	B
1	0.1	0
2	0.15	0
3	0.2	0
4	0.25	0
5	0.3	0
6	0.35	0
7	0.4	0
8	0.45	0
9	0.5	1
10	0.55	1
11	0.6	1
12	0.65	1
13	0.7	1
14	0.75	1
15	0.8	1
16	0.85	1
17	0.9	1
18	0.95	1
Sheet3

Cell Formulas
Range	Formula
B1:B18	B1	=ROUND(A1,0)
A2:A18	A2	=A1+0.05

 

[sksanjeev786]

round() will change that - BUT also all your other numbers

Round() will round down decimals upto 0.5 and up after

Example Question-ETAF.xlsx
	A	B
1	0.1	0
2	0.15	0
3	0.2	0
4	0.25	0
5	0.3	0
6	0.35	0
7	0.4	0
8	0.45	0
9	0.5	1
10	0.55	1
11	0.6	1
12	0.65	1
13	0.7	1
14	0.75	1
15	0.8	1
16	0.85	1
17	0.9	1
18	0.95	1
Sheet3

Cell Formulas
Range	Formula
B1:B18	B1	=ROUND(A1,0)
A2:A18	A2	=A1+0.05

thank you so much Etaf

for your help in this