"Ron Rosenfeld" wrote:
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..
XXXXX
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
XXXXXXXXXX XXXXXXXXXX
Thankyou, I'm not as fast at it, really appreciate the help. Will learn
more how to do by self, but from looking at it I have the intuition that (If
your example means some are fixed cells, like what I was trying to do, not
sure if I have to do), if not an extra column.. but does yours follow this
logic:
Header: $AT$3 fixed cell: $IN (as in Dollars IN, all cells in header 1
fixed cell)
$AT$4 fixed: fee
$AT$5 fixed: =($AT$3-$AT$4) result minus fee
$AT$6 fixed: divisor (realized front-back like you
said, just didn't/don't see how till I try what you are showing, but don't
see jus yet cuz of new eq's/ purpose?..)
$AT$7 fixed: =$AT$5/$AT$6 (you can help me what
where.. but i get it)
right
now at7 conflict using 3 decimal places compared to when divisor is 1, too
many digits here too.
xxxxxxxxxx
running data: LAST (ea line entered) BUY: (eq gets too large a number)
Buy column eq: =IF(AG9=0,"",IF($AT$71000,TRUNC($AT$7/AG9,2),($AT$5/AG9)))
If that chaged what you thought I was doing, else, since don't have any
spare space for more columns in view (can put to right), does your example??
:) don't even know what to ask, does it keep the LAST & BUY columns? ALSO:
If case, can you put $ signs in front of fixed/absolute cells you are
modifying -or- is eq adjustment needed?
Sorry if slow on some of it, working on that (documenting commands, sites,
sites with commands...
vb.., might be scary later) -later
Thanks in advance... !! -Nastech
XXXXXXXXXX XXXXXXXXXX