Text Function - Custom Format ( Re-posted from Misc still looking for answer )
 

I don't know if this helps, but I took your example
and ran a few variations to actual. the results were like

-16% /-17%
01% /00%
12% /11%

I filled the whole column green and selected the column
and conditional formatted

condition 1
formula is
=FIND("-",G44,1)
color = red

condition 1
formula is
=FIND("00",G44,1)
color = none

all decreases were shown as red, increases green and no
changes as white.

Lance

-----Original Message-----
Folks

I posted this in Misc - had a response thanks Richard -
but it hasn't really answered my question - could Tom or
John or Chip or Deb or Myrna or one of you many other
gurus give me an idea if I am throwing good after bad or
is this possible.


Is it possible to do a custom number format into the
text function which
will allow me to colour the text similar to conditional
formatting. The
problem i have is in one of the reports I have the staff
have used one cell
to try and put 2 entries in with a slash in between. Say
this is a target
value / agreed value and then at the end of the month I
will get an actual
value in another cell which I will want to compare
against each and give a
percentage increase or decrease. I have managed to write
a formula to
seperate the values and give a percentage back using the
text function but
now I want to highlight the increase ( good as green )
and decrease ( bad as
red ) within the formula if I can. Is this possible eg..


this is in cell E51

21,571 / 21,334

this is the actual in F51
20,462


The Formula I have is
=TEXT((TRIM(LEFT(E51,FIND("/",E51,1)-1))-F51)/(TRIM(LEFT
(E51,FIND("/",E51,1)
-1)))%,"00")&"%"&" /
"&TEXT((TRIM(RIGHT(E51,FIND("/",E51,1)-1))-F51)/(TRIM
(RIGHT(E51,FIND("/",E51
,1)-1)))%,"00")&"%"

What I would like is instead of "00" as the format to
now apply the rules I
mentioned earlier as a custom format so that if there is
an increase or
decrease the format will be acknowledged sort of like
[Red}-00

Is this possible or can anyone suggest a better way of
doing this without
increasing number of cells ???

Thanks in advance

Gav !!

Text Function - Custom Format ( Re-posted from Misc still looking for answer )
 

sorry the =FIND("00",G44,1) should be in condition 2

condition 2
formula is
=FIND("00",G44,1)
color = none


-----Original Message-----
I don't know if this helps, but I took your example
and ran a few variations to actual. the results were
like

-16% /-17%
01% /00%
12% /11%

I filled the whole column green and selected the column
and conditional formatted

condition 1
formula is
=FIND("-",G44,1)
color = red

condition 1
formula is
=FIND("00",G44,1)
color = none

all decreases were shown as red, increases green and no
changes as white.

Lance

-----Original Message-----
Folks

I posted this in Misc - had a response thanks Richard -
but it hasn't really answered my question - could Tom or
John or Chip or Deb or Myrna or one of you many other
gurus give me an idea if I am throwing good after bad or
is this possible.


Is it possible to do a custom number format into the
text function which
will allow me to colour the text similar to conditional
formatting. The
problem i have is in one of the reports I have the
staff
have used one cell
to try and put 2 entries in with a slash in between.
Say
this is a target
value / agreed value and then at the end of the month I
will get an actual
value in another cell which I will want to compare
against each and give a
percentage increase or decrease. I have managed to
write
a formula to
seperate the values and give a percentage back using
the
text function but
now I want to highlight the increase ( good as green )
and decrease ( bad as
red ) within the formula if I can. Is this possible eg..


this is in cell E51

21,571 / 21,334

this is the actual in F51
20,462


The Formula I have is
=TEXT((TRIM(LEFT(E51,FIND("/",E51,1)-1))-F51)/(TRIM(LEFT
(E51,FIND("/",E51,1)
-1)))%,"00")&"%"&" /
"&TEXT((TRIM(RIGHT(E51,FIND("/",E51,1)-1))-F51)/(TRIM
(RIGHT(E51,FIND("/",E51
,1)-1)))%,"00")&"%"

What I would like is instead of "00" as the format to
now apply the rules I
mentioned earlier as a custom format so that if there
is
an increase or
decrease the format will be acknowledged sort of like
[Red}-00

Is this possible or can anyone suggest a better way of
doing this without
increasing number of cells ???

Thanks in advance

Gav !!


.