On Thu, 3 Nov 2005 11:43:03 -0800, "nastech"
wrote:


"Ron Rosenfeld" wrote:

On Wed, 2 Nov 2005 21:07:02 -0800, "nastech"
wrote:

Hi, I am trying to find a way to vary the format for a column of numbers,
that depend on the size of the input.
Would like to change from zero decimal places, to 2 decimal places if 1000,
(am using alternate input if 1000: i.e. Input/1000, then NEED 2 decimal
places).

Is there a way to modify the number of decimal places viewed, inside an
equation. I am using:

=IF(AG9=0,"",IF(AT111000,TRUNC($AT$7/AG9,2),($AT$5/AG9)))

will / need to see number: e.g. 1085, to 1.08; (no rounding up)

If function exists, is it possible to modify decimal places from a single/
absolute cell. thanks in advance.

1. Formatting cannot do what you want as formatting will round and not
truncate.

2. You can certainly modify the number of decimals viewed, within a formula, by
using the TEXT function. e.g. =TEXT(num,"0.00") or =TEXT(num,"0") depending
on your condition.

3. In your text, you indicate you want to display a particular number
differently depending on the Input. Since you have four different cell
references in your equation, it is not clear which is Input, what your logic is
in deciding which formula in your IF statement to display.

HTH
--ron


Hi!, thanks for your reply, um:), Sorry for the lack of detail.

I "might" be intermedieat.. I have been playing with conditional formatting
alot, but guesse you mean formatting in cell for? but

In Excel, "format" and "conditional format" have specific meanings. Format is
what you get if you click on the Format item in the top menu bar. "Cells" and
"Conditional Formatting" are two of the options. Under "Conditional
Formatting" there is no option to do what you describe.

But it seems you may be using the term "conditional formatting" differently
than does Excel. Since this is an Excel group, I have found that sort of thing
frequently leads to confusion.



sorry did not label variables:
AG9 running records: LAST price
AT11, sorry should read $AT$7, new/temp cell for: $IN/$AT$6
$AT$6: variable divisor: 1 or 10,000 etc

purpose is to shrink $IN to fit in cell, when $IN rises over x decimals.
will modify / fix if(at71000 to work later..

How about this approach: In one cell display, for example, the first three
significant digits of your # of shares; and in the adjacent cell display the
multiplier. In other words, you are computing the divisor based on the number
of shares, rather than entering it manually.

To be consistent with what you wrote earlier, we will express the results as a
number followed by two decimals.

I'll use A1 for the "real number" of shares to buy; B1 for the first three
significant digits / 100; and C1 for the multiplier. You can then adapt that
to your layout.

For example, you compute to purchase 999 shares;

A1: 999
B1: 999
C1: 1

A1: 5048
B1: 5.04
C1: 1,000

A1: 21253
B1: 2.12
C1: 10,000

To do the above, you can use the formulas:

B1: =TRUNC(A1,2-INT(LOG10(A1)))/10^INT(LOG10(A1))
C1: =10^INT(LOG10(A1))

Formats:

B1: Format/Cells/Custom/Type: 0.00
C1: Format/Cells/Custom/Type: #,##0





Result is for # of shares to BUY.
I have looked very long trying to fix myself, got this far.

Also: driving me crazy: Hyperlinks do not move relative cell when lines
added/deleted. Is there an answer for that. I know about:
-Rightclick add hyperlink, and just figured out
=HYPERLINK "in" sheet, e.g.: =HYPERLINK("[file.xls]sheet!A138","top")

i.e. the A138 stays absolute
In Help many examples, figured out you need file extension to make that
work, but my 10 or 20 locations going to, keep floating around as records are
moved... ahhhh
Thanks, -Nastech


--ron