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.  

0
nastech (103)
11/3/2005 5:07:02 AM
excel.misc 78881 articles. 5 followers. Follow

16 Replies
195 Views

Similar Articles

[PageSpeed] 7

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?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=481765

0
11/3/2005 7:55:03 AM
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
0
ronrosenfeld (3122)
11/3/2005 11:55:44 AM
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?action=getinfo&userid=21059
> View this thread: http://www.excelforum.com/showthread.php?threadid=481765
> 
> 
0
nastech (103)
11/3/2005 7:20:01 PM
"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


0
nastech (103)
11/3/2005 7:43:03 PM
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:), 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
0
ronrosenfeld (3122)
11/3/2005 8:32:08 PM

"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:), 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



0
nastech (103)
11/4/2005 12:07:10 AM
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?
0
nastech (103)
11/4/2005 12:19:11 AM
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
0
ronrosenfeld (3122)
11/4/2005 1:00:42 AM
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
0
nastech (103)
11/4/2005 1:43:02 AM
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.  
0
nastech (103)
11/4/2005 2:36:04 AM
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
0
ronrosenfeld (3122)
11/4/2005 4:00:18 AM

"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

0
nastech (103)
11/4/2005 5:03:03 AM
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
0
nastech (103)
11/4/2005 5:15:01 AM
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 :)  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
0
ronrosenfeld (3122)
11/4/2005 12:22:54 PM
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"
> <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 :)  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
> 
0
nastech (103)
11/4/2005 2:00:04 PM
On Fri, 4 Nov 2005 06:00:04 -0800, "nastech"
<nastech@discussions.microsoft.com> 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
0
ronrosenfeld (3122)
11/4/2005 2:25:40 PM
Reply:

Similar Artilces:

Excel 2007 Forgets Formatting
My employer upgraded our computers to Excel 2007 a couple of months ago. Since that time, I have had several occasions in which Excel has "forgotten" some of the formatting in a workbook. This has happened when I have opened a workbook, done some work in it, and then saved and closed the workbook. When I reopen the workbook later, some of the cells are no longer formatted the way they were when I saved the workbook. This has occurred both for files saved in "compatibility mode" (that is, using the Excel 95-2003 file format) and in the new XTML format used by Excel...

Array formula: returning non-zero for negative search
I have an array formula: =SUM(IF(C2:C9="Apples",IF(I2:I9>=0,I2:I9,0),0)) where C2:C9 is product, H2:H9 is delivery date and I2:I9 is period since delivery. This works well where there are apples in C2:C9, but delivers zero when there are none, which causes problems in the subsequent formula. How do I get "24" as a result when there are no apples in C2:C9 I am a newbie, hope you can help David I think in row 2 for example Q2 write : "=if(exact (C2;"apples");I2;0) and Enter .Then when your mouse is in the right bottom of cell Q2 a + mark appears .drag it ...

Lately, I've been reveiving a lot of dreaded SPAM with the following format in the Subject line
I have been recieving a huge amount of SPAM from the a-holes who do this with the following pattern in the subject line: Re: %RND_UC_CHAR[2-8], said hed come Does anyone know what this is all about? Is this some new technique these creeps have begun using to get around SPAM blockers? (It does get around Cloudmark) Did you check with Cloudmark support on this? Are you using the latest (subscription) version? billym wrote: > I have been recieving a huge amount of SPAM from the a-holes who do > this with the following pattern in the subject line: > > Re: %RND_UC_CHAR[2-8], said ...

Formulas don't calculate until SAVE (excel 2000)
I have a client who insists they have a spreadsheet in which calculations of certain formulas do not occur until they SAVE the spreadsheet. They say it used to always auto-calculate upon entering in data into CELLS but changed to NOW only calculating when they press the SAVE speedbutton. I asked them if they started doing anything new or different? They said they just recently started pasting new columns from anything spreadsheet (provided by another company). It is an inconvenience to keep having to press SAVE. Any Ideas? --Rick It soulds as though Calculate has become manual....

WriteProcessingInstruction formatting problem
I have a problem with the XmlTextWriter.WriteProcessingInstruction method. This method takes two parameters: [C#] public override void WriteProcessingInstruction( string name, string text ); Remarks If text is either a null reference (Nothing in Visual Basic) or String.Empty, this method writes a ProcessingInstruction with no data content, for example <? name?>. So i tried to write a processing instruction with the parameter text set to null. What i got wasn't what i expected: <?name ?>. This format is not well formed. Is this a bug or do someone have an advice ...

Some Excel formatting functions taking a long time to work
Excel 2000 SP3 When I first use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to wok. Meantime Excel is locked up until it completes that formatting call. Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri Hi, I haven't used 2000 in a while but if my memory serves me your problem is the programs normal behavior. I suspect microsoft code these areas as separate items that need to be attached when they are first used in a session. This no longer is t...

Need a formula for multiple sums in a cloumn when rows vary
I am making a invoice I need a total for each truck, with a grand total at the end. The number of rows for each truck in the amount column will vary. Is it possible to have the text truck total to the left of the total? A small example below, there will be more entries under each truck and more trucks also. tr# date hrs rate amount 3 4/12/10 10 50 500 4/13/10 5 50 250 truck 3 total 750 4 4/10/10 1 50 50 truck 4 total 50 and so on ...

Conditional format shapes
I'm looking to use data in a table (ie state names and population data) and how to use data in the population column to change the color of polygon shapes I create (ie shape of state traced on a background image of country map.) Is it possible to use the conditional formatting option to change the color of a polygon instead of only the cell? It would be great to use Excel to do some GIS presentations. I've heard it can be done in VBA but I don't know the code at all. ...

Incrementing a formula by 20 automatically
I've searched, but the answers didn't seem to match what I was looking for. I have two sheets. On the sheet I'm pulling data into, I have the formula "=Results!B20" sitting in cell "B14". This works fine, but I want to increment it to "=Results!B40" in cell "B15" and so on. The Auto Fill handle doesn't work for this and I have MANY cells I need to fill. Any help is appreciated. Thanks! =INDIRECT("results!b"&(ROW()-13)*20) -- Kind regards, Niek Otten Microsoft MVP - Excel <Bowbender@gmail.com> wrote in messag...

Need a formula for a Marketing equipment log
I am trying to create an equipment log in excel that will automatically update what equipment is in stock based on an entry log worksheet in the same file. Currently I have a list of items and next to each item is a cell that holds a formula for calculating any check outs of equipment that are entered on the entry log. I do not want to have another page for returns, but instead to only have to enter the date that the item has been returned on the same row as the original check out. I want this to then it turn, return the checked out item to the running stock total. Any id...

Modifying formula of calculated field depending ypon the selection of pagefield
In a pivot table depending upon the selection of a pagefield value I have to populate the value of a calculated field in row. Suppose Page field can have selections Income1 , Income2 and All then my calculated field will use different formula for three selections. I have tried some thing like this but its not working. I will really appreciate a quick response. Private Sub Worksheet_Calculate() 'Call CalculateIncomeSegment Debug.Print ActiveSheet.PivotTables("PivotTable1").PivotFields("Income ").CurrentPage End Sub Private Sub CalculateIncomeSegment() If Ac...

Help with conditional formating #2
Hi, I'm running Excel 2003, Sp2. I'm using conditional formatting to change the background color on some worksheet cells: Cellvalue = Color ============ A = red B = green C = blue This works fine but due to changes in the workbook I now also need to be able to format a fourth cellvalue, D = yellow. The built-in function however only lets me set up conditional formatting for three values. Is there any way to achieve this? TIA Pete Pete, Use simple formatting to color the cell yellow. CF will take over for the first three values, and it will be yellow for D. If you have ...

Conditional Formatting #49
Is there a way of doing Conditional formatting, based on more than three criteria? I want a cell colour to change to one of 10 colours depending on the lookup value of another cell! (ie cell A2 will contain a name, and cells A3 will then contain the team, which it has looked up) Thanks Shane ...

Access 2000 File Format in Access 2003
Dear all, If I'm using Access 2003 to create Access 2000 format, is there any new feature in Access 2003 which I can't apply in Access 2000 file ? Thx. Vensia On Wed, 9 May 2007 14:20:23 +0700, "Vensia" <vensia2000_nospam@yahoo.com> wrote: >Dear all, > >If I'm using Access 2003 to create Access 2000 format, is there any new >feature in Access 2003 which I can't apply in Access 2000 file ? >Thx. there are many; however, are you deploying to users with Access 2000, or with Access 2003? The default format for your database is (normally) 200...

Can the data range in a chart contain a formula?
I want the data range in a chart to change depending on the value of a cell in a worksheet. For example... If A1=10, I want the data range to be B1:B10. If A1=20, I want the data range to be B1:B20. Does anyone know how I can achieve this? Thanks in advance, David. Thanks! It took me a while but I eventually got it working. The OFFSET function is the key, it provides the mechanism to specify a configurable range via a named formula. Getting the correct apostrophes in the right places was a bit tricky as well. Thanks, David. "Paul Corrado" <paulcorrado@optonline.net>...

Format a single digit like "007"?
In the query result, I need to see (for example) a single digit number (say that "7") as "007". What is the function for this? I use FormatNumber function for other purposes. Can you help me please? try the following: format(7,"000") 007 format(77,"000") 077 format(777,"000") 777 "mezzanine1974" wrote: > In the query result, I need to see (for example) a single digit number > (say that "7") as "007". > What is the function for this? I use FormatNumber function for other > purposes. > Can you he...

Multiply cells and format to currency in text format
Hi, I have 2 cells which I wish to multiply, and have the result be formatted in text with the 2 decimal places. 3 x 1.55 = 4.65 2 x 4 = 8.00 This is going to be used with a program which requires it to be numbers stored as text. Just basic cell formatting doesn't work. Can some one please help? Cheers -- Paul Wilson On Tue, 22 Dec 2009 16:43:02 -0800, Paul Wilson <PaulWilson@discussions.microsoft.com> wrote: >Hi, > >I have 2 cells which I wish to multiply, and have the result be formatted in >text with the 2 decimal places. > >3 x...

Excel "Insert Comment" Formatting
Hi, Windows XP Home Excel 2002 How can I format the "Insert Comment" .....(open Workbook > right click on a cell > left click > Insert Comment)......in my Excel worksheet? It opens now at Font Size 8. I would like it to open at a larger Font Size and also have the Insert Comment window saved at my chosen larger size (I know how to expand to a larger size but want to save it at that size for next time using Insert Comment in a different cell). Thank you, Larry Larry Start > settings > Control Panel > Display > Appearance Tab > Advanced. Select...

Formatting columns to show months, quarters, OR years
I deal with a lot of historical data. I usually use groups to be able to view this data by months, quarters, and years. So if I only want to see years I just click the highest level group. BUT...when I view the middle group (quarters) it also shows me the highest group. I have tried different ways of grouping but this is the best I can get. Is there another cool way to view data like this? Maybe by naming columns then have some sort of drop down box to choose what columns to view??? I am only an intermediate level, so please go easy... Thanks!!! Hi maybe a pivot table is what yo...

Problems with array formula
Hi i have a spread sheet which has data about different sectors. I want to find out the average of each sector. {=AVERAGE(IF($E$7:$E$307=IU85,J7:J307))} This is teh formula i am using. If column e which has details about the sector of each company matches the sector name in IU85 then give me the average of correspondingdata in column j. Now the issue is that some companies data is not present and they were blank. It is giving error while computing the average. Secondly i tried to change the blanks and added text in place of blanks. But it is still giving me those errors. Can anyone please...

Sequential numbers in a formula?
I have a few formulas (OFFSET & MATCH) which are the same (with relative references chanfing accordingly) and occupy cells B2:B500. In each formula one of the function arguments refers to a single cell in the adjacent column i.e A1:A500 which contains the numbers 1 to 500 (and the column is hidden as the numbers are needed only in the formulas and not to be displayed). The numbers are used to give the relative row number in the ranges used by OFFSET. So, the first formula uses 1 (via B2) and the second formula uses 2 (via B3) and so on. My question is: Can I somehow refer to a ...

Unrecognized db format
Hello everyone, I had this multi-user application installed on the common drive since May. It's Access 2003, and two days ago started getting the "unrecognized db format" mesage. I tried importing all into a blank db, got the same message and no import. The issue was reported by multiple users. Please let me know what you think. Thank you, Ra How was your db setup. You mention multiple users... so your db was split so the each user had their own front-end? What type of maintenance did you implement?Do you have a backup?! Have you compacted the db recently. Sadly, I&#...

date format 03-23-08
Hi, I have a date column where the date are like this dd/mm/yyyy for exemple 05/11/2003. I want that user chosses only the year and athe query returns the lines of that yaer. How to do ? Thanks. Big, In design view of the query, in the Field row of a blank column of the query design grid, enter a calculated field similar to this: MyYear: Year([NameOfYourDateField]) Then, in the Criteria of this column, enter whatever it is that relates to your "user chooses". For example, if the user is entering the required year in a textbox on a form, the Criteria would be like this: [For...

what is 'too many different cell formats' reply?
Tommy Read this KB article On Thu, 16 Dec 2004 19:29:01 -0800, "tommy" <tommy@discussions.microsoft.com> wrote: Check these out: http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 http://www.vbapro.com/xl_formats.htm tj "tommy" wrote: > I think Gord meant to include this article link: XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/?id=213904 Gord Dibben wrote: > Tommy > > Read this KB article > > > > On Thu, 16 Dec 2004 19:29:01 -0800, "tommy" <tommy@discussions...

Time Format to Text Output
Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 1...