Adding formatting to formulas

[Guest]

I'm trying to add formatting to a formula im using.
The first one looks like this:

="Operating Cashflows: "&"$"&VALUE(C73)&"; "&"Investing Cashflows:
"&"$"&TEXT(VALUE(C74),(0))&"; "&"Financing Cashflows: "&VALUE(C75)&". "

It links to a spreadsheet which pulls these numbers, sometimes in negative
values. As it is, it looks like:

Operating Cashflows: $50; Investing Cashflows: $-20; Financing Cashflows: $-40

I want it to get rid of the $- and replace it with <$ -
For instance: Financing Cashflows: <$40>.

The second problem I'm having is with ratio values. My formula is as follows:

="NP Margins "&IF(C55>G55, "increased ", "decreased ")&"to
"&TEXT(VALUE(C55),0.1%)&" "&"in FYE "&VALUE(C9)&" "&"from
"&TEXT(VALUE(G55),0.1%)&" "&"in FYE "&VALUE(G9)&"."

As you can see, I have to put 0.1% for the formatting on the ratios - which
adds in more than the real ratio. I do this because, for some reason, when I
enter 0.00%, 0.0%, or just 0%, I will never get any decimals behind the
number, which I need.

If anyone can help me with either of these two issues, it would be
appreciated.

Thanks,
Jonathan

 

[Harald Staff]

Hi Jonathan

A formula can do one single thing: Return a value to its own cell. Not
return a format, not start a macro, and not push a value to anywhere else.
But look into Conditional formatting in the Format menu and see if it helps
you on this.

HTH. Best wishes Harald

 

[Guest]

Jon,

On your first question...

I have a1=50, b1=-40(as text) and c1=20. This formula produces your result:
="Operating Cashflows: $"&VALUE(A1)&"; "&"Investing Cashflows:
"&TEXT(VALUE(B1),"$0;($0)")&"; "&"Financing Cashflows: "&VALUE(C1)&". "

It evaluates to:
Operating Cashflows: $50; Investing Cashflows: ($40); Financing Cashflows:
20.

I only had to make a small change:
"$0;($0)"