+ Reply to Thread
Results 1 to 17 of 17

Formula for: Format Decimal places?

  1. #1
    nastech
    Guest

    Formula for: Format Decimal places?

    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.


  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Perhaps a variation of:

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

    will help you.


    Quote Originally Posted by 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.

  3. #3
    Ron Rosenfeld
    Guest

    Re: Formula for: Format Decimal places?

    On Wed, 2 Nov 2005 21:07:02 -0800, "nastech"
    <[email protected]> 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(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

  4. #4
    nastech
    Guest

    Re: Formula for: Format Decimal places?

    Thankyou very much, will check it out

    "Bryan Hessey" wrote:

    >
    > Perhaps a variation of:
    >
    > =IF(A1>10000,TEXT(TRUNC(A1/1000,2),"0.00"),TEXT(A1,"0"))
    >
    > will help you.
    >
    >
    > 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(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
    > ------------------------------------------------------------------------
    > Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
    > View this thread: http://www.excelforum.com/showthread...hreadid=481765
    >
    >


  5. #5
    nastech
    Guest

    Re: Formula for: Format Decimal places?


    "Ron Rosenfeld" wrote:

    > On Wed, 2 Nov 2005 21:07:02 -0800, "nastech"
    > <[email protected]> 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(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
    >


    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



  6. #6
    Ron Rosenfeld
    Guest

    Re: Formula for: Format Decimal places?

    On Thu, 3 Nov 2005 11:43:03 -0800, "nastech"
    <[email protected]> wrote:

    >
    >"Ron Rosenfeld" wrote:
    >
    >> On Wed, 2 Nov 2005 21:07:02 -0800, "nastech"
    >> <[email protected]> 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(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
    >>

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

  7. #7
    nastech
    Guest

    Re: Formula for: Format Decimal places?



    "Ron Rosenfeld" wrote:

    > On Thu, 3 Nov 2005 11:43:03 -0800, "nastech"
    > <[email protected]> wrote:
    >
    > >
    > >"Ron Rosenfeld" wrote:
    > >
    > >> On Wed, 2 Nov 2005 21:07:02 -0800, "nastech"
    > >> <[email protected]> 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(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
    > >>

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

    >


    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




  8. #8
    nastech
    Guest

    Re: Formula for: Format Decimal places?


    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?

  9. #9
    Ron Rosenfeld
    Guest

    Re: Formula for: Format Decimal places?

    On Thu, 3 Nov 2005 16:19:11 -0800, "nastech"
    <[email protected]> wrote:

    >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

  10. #10
    nastech
    Guest

    Re: Formula for: Format Decimal places?

    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

  11. #11
    nastech
    Guest

    Re: Formula for: Format Decimal places?

    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.

  12. #12
    Ron Rosenfeld
    Guest

    Re: Formula for: Format Decimal places?

    On Thu, 3 Nov 2005 18:36:04 -0800, "nastech"
    <[email protected]> wrote:

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


    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

  13. #13
    nastech
    Guest

    Re: Formula for: Format Decimal places?



    "Ron Rosenfeld" wrote:

    > On Thu, 3 Nov 2005 18:36:04 -0800, "nastech"
    > <[email protected]> wrote:
    >
    > >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.

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


    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


  14. #14
    nastech
    Guest

    Re: Formula for: Format Decimal places?

    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

  15. #15
    Ron Rosenfeld
    Guest

    Re: Formula for: Format Decimal places?

    On Thu, 3 Nov 2005 21:15:01 -0800, "nastech"
    <[email protected]> wrote:

    >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

  16. #16
    nastech
    Guest

    Re: Formula for: Format Decimal places?

    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" wrote:

    > On Thu, 3 Nov 2005 21:15:01 -0800, "nastech"
    > <[email protected]> wrote:
    >
    > >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
    >


  17. #17
    Ron Rosenfeld
    Guest

    Re: Formula for: Format Decimal places?

    On Fri, 4 Nov 2005 06:00:04 -0800, "nastech"
    <[email protected]> wrote:

    >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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1