Formula for: Format Decimal places?

[nastech]

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(AT11>1000,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.

 

[Bryan Hessey]

Perhaps a variation of:

=IF(A1>10000,TEXT(TRUNC(A1/1000,2),"0.00"),TEXT(A1,"0"))

will help you.

 

[Ron Rosenfeld]

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(AT11>1000,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

 

[nastech]

Thankyou very much, will check it out

Perhaps a variation of:

=IF(A1>10000,TEXT(TRUNC(A1/1000,2),"0.00"),TEXT(A1,"0"))

will help you.

 

[nastech]

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
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(at7>1000 to work later..

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 Rosenfeld]

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(at7>1000 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

 

[nastech]

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.

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$7>1000,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

 

[nastech]

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.

Think I am getting it more by looking at it.. but wonder if can combine the
eq's.. if greater than, etc.. but would need to see if there is an eq for
entering format in cell, for B1: C1: Format/Cells/Custom/Type above
does that exist?

 

[Ron Rosenfeld]

Think I am getting it more by looking at it.. but wonder if can combine the
eq's.. if greater than, etc.. but would need to see if there is an eq for
entering format in cell, for B1: C1: Format/Cells/Custom/Type above
does that exist?

Please don't use too many abbreviations. I'm not always certain what you mean
by some of them.

I am assuming eq's means equations and not equal signs, for example.

In order to format a cell, let me explain the shorthand I used:

Format/Cells/Custom/Type: 0.00

That really should have read:

Format/Cells/Number/Custom/Type: 0.00

(Sorry about that).

That means to select Format from the top menu bar. Then from that drop down
select Cells; then from the dialog box that opens select the Number tab; then
from the options you see select Custom. You will then see an entry area
labeled: Type and that is where you type in the 0.00.

So far as the "greater than" stuff, if you try what I suggested, you will see
there is no need for it so far as dividing the numbers of shares appropriately;
that's why I wrote the equations the way I did.


--ron

 

[nastech]

ok, sorry, thanks. did mean eq(uation). got the format part. just not used
to the eq's., this should help alot. thans again. -Nastech

 

[nastech]

Hi, I like the formula's, and if I guesse right, can see use for having data
on one line, especially in future if / when expand to be able to tabulate
running totals? (is that right?) Sorry, I'm trying to make sense.. reaching
here, but:

I have a fixed $IN (or dollars in); have to know how many shares to buy,
quick, when I need them; not picking shares 1st, hope I didn't spent too
much later.
Maybe I am slow, if knowing how to "adapt" that to my layout. But,

Result is for # of shares to BUY, I know it seems backwards.

Maybe I am the one who is backwards, don't know. How do I use the multplier?
Thanks.

 

[Ron Rosenfeld]

Hi, I like the formula's, and if I guesse right, can see use for having data
on one line, especially in future if / when expand to be able to tabulate
running totals? (is that right?) Sorry, I'm trying to make sense.. reaching
here, but:

I have a fixed $IN (or dollars in); have to know how many shares to buy,
quick, when I need them; not picking shares 1st, hope I didn't spent too
much later.
Maybe I am slow, if knowing how to "adapt" that to my layout. But,

Maybe I am the one who is backwards, don't know. How do I use the multplier?
Thanks.

I thought you indicated you wanted to express your result as a digit with two
decimals:

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

The formula I posted will always reduce a number to that format, and also (in
the second equation) give you the divisor used to obtain that result. So in
the above, if you entered 1085, the formulas would show:

1.08 1,000

That is the same as I posted a few messages ago.

If that is not something you want, then I don't understand what it is that you
do want.

==========================

If you want to enter some number of dollars, and compute how many shares you
can buy with that, that's simple:

A1: Dollars available
A2: Stock price (per share)
A3: =INT(A1/A2)


--ron

 

[nastech]

I thought you indicated you wanted to express your result as a digit with two
decimals:


The formula I posted will always reduce a number to that format, and also (in
the second equation) give you the divisor used to obtain that result. So in
the above, if you entered 1085, the formulas would show:

1.08 1,000

That is the same as I posted a few messages ago.

If that is not something you want, then I don't understand what it is that you
do want.

==========================

If you want to enter some number of dollars, and compute how many shares you
can buy with that, that's simple:

A1: Dollars available
A2: Stock price (per share)
A3: =INT(A1/A2)


--ron

XXXXXXXXXX

HI!, I am better understanding what to say / ask for, maybe was complex.

Thanks again, I'm ok, just was not understanding your equation because don't
understand it yet. Since I don't exactly get where to put them for my
application, needs two inputs:

$IN (dollars-in) & Last Price. Don't see 2 inputs for your eq.
Must have: $IN/Price=shares, so I can find shares.

2 decimals yes, Divide by 1000 is used to simulate "thousands" separator,
with decimal point, to ruduce digits (by hopefully, having variable decimal
positions: 2 or later, 1 if higher $).

That may be the last problem still have, not sure if your eqaution would
have variation to all change decimal places from 2, to 1 spot. (relatively
speaking: if over 1000 2 spots, if over 50,000 1 spot, maybe). 1000 good
for now.

Will check int( further as well. saw the word multiplier somewhere i
guesse, that' all? anyways will figure it out.

2 decimal places was what looking for, right up to here/now, found variation
with what tried with other:

=IF(AG9="","",IF($AT$5>1000,TEXT(TRUNC(($AT$5/AG9)/1000,2),"0.00"),TEXT(($AT$5/AG9),"#,##0")))

AT5 IS $IN (fee adjusted)

AG9 IS LAST PRICE, THIS EQ goes in BUY column for every instance of LAST
PRICE, ~2k records. But if get to over ~$50k (with my column width), need to
change decimal from 2 spots to 1. At that level, rounding down to one spot
should be ok?

1st prob: if can change from 2 to 1 decimal place on 1 cell command? /
automatic?
2nd prob: if not automatic, see results (maybe from use of TEXT), numbers
are sneaking under column to left, and not going: ####. ouch, well under
buy I guesse.

Hope all I did was crack you up... Any fix for above equation / your
equation? Just don't know where to put yours for what I "have" to do.. .
Bit closer anyways.
50k not that big of a number... later

 

[nastech]

p.s. divide by 1000 might only work for every 10 power of 3, is that telling
the future, or what anyways, I maybe would utimately.. have cell that
works divsor to the power thing (3, 6, 9), stead of 1000000000000000000000

 

[Ron Rosenfeld]

p.s. divide by 1000 might only work for every 10 power of 3, is that telling
the future, or what anyways, I maybe would utimately.. have cell that
works divsor to the power thing (3, 6, 9), stead of 1000000000000000000000

In the equations I recommended, try substituting your number of shares, or your
equation to compute the number shares, for "A1"

See if that gives you what you want.

For example:


=TRUNC($IN/Price,2-INT(LOG10($IN/Price)))/10^INT(LOG10($IN/Price))

=10^INT(LOG10($IN/Price))


--ron

 

[nastech]

Many Thanks.... I have a better understanding of what goes where in that
equation, and without fully knowing how the equation works yet, sorry,
intuition is that it is what I need, &, is probably more dynamic than what I
could have done... Many Thanks -Nastech

XXXXXXXXXX

 

[Ron Rosenfeld]

Many Thanks.... I have a better understanding of what goes where in that
equation, and without fully knowing how the equation works yet, sorry,
intuition is that it is what I need, &, is probably more dynamic than what I
could have done... Many Thanks -Nastech

XXXXXXXXXX

You're welcome.

As to what the equation is doing:

The equation changes the value (A1 or $IN/Price) to three significant digits
divided by 100 -- so as to give you your 0.00 type of output. In order to do
that, it divides the original by some multiple of 10; that multiple is the
output of the second formula.

Hope it helps you.
--ron