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

[Lance]

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 !!

 

[Lance]

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 !!

.