Conditional Formating

[aparvez007]

Conditional Formating not working on this answer. (175% ▲)

if greaterthan 100% Blue lessthan 100% red


[below is the formula which give me result (175% ▲)


=ROUND(B116/C116,2)*100 & "% " & IF(B116>C116,"▲", IF(B116=C116,"●",▼))]

 

[NARAYANK991]

Hi Parvez ,


If I assume the above formula is in A116 , then the following two rules will give you the CF :


=VALUE(LEFT(A116,FIND("%",A116)-1))<100 RED


=VALUE(LEFT(A116,FIND("%",A116)-1))>100 BLUE


Narayan

 

[aparvez007]

Thanks Narayan...


actual i have my data like this ...


A116 B116 C116

2,509 1,431 [=ROUND(A116/B116,2)*100 & "% " & IF(A116>B116,"▲", IF(A116=B116,"●",▼))]


I want CF in C116

 

[NARAYANK991]

Hi Parvez ,


Change all references to A116 to C116 in the posted formulae.


Narayan

 

[aparvez007]

Yuppyy...... Thanks dear... works awesome...


can you please explain me the formula if have time...


Thanks a ton...

 

[NARAYANK991]

Hi Parvez ,


Sure , no problem.


Your formula in C116 is going to return a text string ; for the values you have given , 2509 in A116 and 1431 in B116 , C116 will have the text string 168% ▲.


To format values greater than 100% in BLUE , and values less than 100% in RED , we need to first find out the value of the percentage ; this is done by looking for the "%" symbol in the text string , and taking all the digits to the left of this ; since this will still be a text string , we need to convert it to a number.


Converting a text string to a number is done by using the VALUE function ; thus =VALUE("168") will give the number 168.


Taking the digits to the left of a point is done by using the LEFT function ; thus LEFT("Excellent",5) will give the text string "Excel".


Looking for any character ( even the % symbol is just another character ) , is done by using the FIND function ; this function returns the position within a text string where the searched for character is found. Thus using =FIND("%","100 %") will return the value 5 ; using =FIND("%","100%") will return 4.


Is this explanation OK ?


Narayan

 

[aparvez007]

too much ok dear...


Thanks a trillion Narayan... u r awesome...