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.
Perhaps a variation of:
=IF(A1>10000,TEXT(TRUNC(A1/1000,2),"0.00"),TEXT(A1,"0"))
will help you.
Originally Posted by nastech
On Wed, 2 Nov 2005 21:07:02 -0800, "nastech"
<nastech@discussions.microsoft.com> 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
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
>
>
"Ron Rosenfeld" wrote:
> On Wed, 2 Nov 2005 21:07:02 -0800, "nastech"
> <nastech@discussions.microsoft.com> 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
On Thu, 3 Nov 2005 11:43:03 -0800, "nastech"
<nastech@discussions.microsoft.com> wrote:
>
>"Ron Rosenfeld" wrote:
>
>> On Wed, 2 Nov 2005 21:07:02 -0800, "nastech"
>> <nastech@discussions.microsoft.com> 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
>
>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
"Ron Rosenfeld" wrote:
> On Thu, 3 Nov 2005 11:43:03 -0800, "nastech"
> <nastech@discussions.microsoft.com> wrote:
>
> >
> >"Ron Rosenfeld" wrote:
> >
> >> On Wed, 2 Nov 2005 21:07:02 -0800, "nastech"
> >> <nastech@discussions.microsoft.com> 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
> >
> >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??
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
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?
On Thu, 3 Nov 2005 16:19:11 -0800, "nastech"
<nastech@discussions.microsoft.com> 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
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
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.
On Thu, 3 Nov 2005 18:36:04 -0800, "nastech"
<nastech@discussions.microsoft.com> 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
"Ron Rosenfeld" wrote:
> On Thu, 3 Nov 2005 18:36:04 -0800, "nastech"
> <nastech@discussions.microsoft.com> 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
p.s. divide by 1000 might only work for every 10 power of 3, is that telling
the future, or what
works divsor to the power thing (3, 6, 9), stead of 1000000000000000000000
On Thu, 3 Nov 2005 21:15:01 -0800, "nastech"
<nastech@discussions.microsoft.com> wrote:
>p.s. divide by 1000 might only work for every 10 power of 3, is that telling
>the future, or what
>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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks