Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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(AT111000,TRUNC($AT$7/AG9,2),($AT$5/AG9))) will / need to see number: e.g. 1085, to 1.08; (no rounding up) If function exists, is it possible to modify decimal places from a single/ absolute cell. thanks in advance. |
#2
|
|||
|
|||
Formula for: Format Decimal places?
Perhaps a variation of: =IF(A110000,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(AT111000,TRUNC($AT$7/AG9,2),($AT$5/AG9))) will / need to see number: e.g. 1085, to 1.08; (no rounding up) If function exists, is it possible to modify decimal places from a single/ absolute cell. thanks in advance. -- 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 |
#3
|
|||
|
|||
Formula for: Format Decimal places?
On Wed, 2 Nov 2005 21:07:02 -0800, "nastech"
wrote: Hi, I am trying to find a way to vary the format for a column of numbers, that depend on the size of the input. Would like to change from zero decimal places, to 2 decimal places if 1000, (am using alternate input if 1000: i.e. Input/1000, then NEED 2 decimal places). Is there a way to modify the number of decimal places viewed, inside an equation. I am using: =IF(AG9=0,"",IF(AT111000,TRUNC($AT$7/AG9,2),($AT$5/AG9))) will / need to see number: e.g. 1085, to 1.08; (no rounding up) If function exists, is it possible to modify decimal places from a single/ absolute cell. thanks in advance. 1. Formatting cannot do what you want as formatting will round and not truncate. 2. You can certainly modify the number of decimals viewed, within a formula, by using the TEXT function. e.g. =TEXT(num,"0.00") or =TEXT(num,"0") depending on your condition. 3. In your text, you indicate you want to display a particular number differently depending on the Input. Since you have four different cell references in your equation, it is not clear which is Input, what your logic is in deciding which formula in your IF statement to display. HTH --ron |
#4
|
|||
|
|||
Formula for: Format Decimal places?
Thankyou very much, will check it out
"Bryan Hessey" wrote: Perhaps a variation of: =IF(A110000,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(AT111000,TRUNC($AT$7/AG9,2),($AT$5/AG9))) will / need to see number: e.g. 1085, to 1.08; (no rounding up) If function exists, is it possible to modify decimal places from a single/ absolute cell. thanks in advance. -- 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
|
|||
|
|||
Formula for: Format Decimal places?
"Ron Rosenfeld" wrote: On Wed, 2 Nov 2005 21:07:02 -0800, "nastech" wrote: Hi, I am trying to find a way to vary the format for a column of numbers, that depend on the size of the input. Would like to change from zero decimal places, to 2 decimal places if 1000, (am using alternate input if 1000: i.e. Input/1000, then NEED 2 decimal places). Is there a way to modify the number of decimal places viewed, inside an equation. I am using: =IF(AG9=0,"",IF(AT111000,TRUNC($AT$7/AG9,2),($AT$5/AG9))) will / need to see number: e.g. 1085, to 1.08; (no rounding up) If function exists, is it possible to modify decimal places from a single/ absolute cell. thanks in advance. 1. Formatting cannot do what you want as formatting will round and not truncate. 2. You can certainly modify the number of decimals viewed, within a formula, by using the TEXT function. e.g. =TEXT(num,"0.00") or =TEXT(num,"0") depending on your condition. 3. In your text, you indicate you want to display a particular number differently depending on the Input. Since you have four different cell references in your equation, it is not clear which is Input, what your logic is in deciding which formula in your IF statement to display. HTH --ron Hi!, thanks for your reply, um:), Sorry for the lack of detail. I "might" be intermedieat.. I have been playing with conditional formatting alot, but guesse you mean formatting in cell for? but sorry did not label variables: AG9 running records: LAST price AT11, sorry should read $AT$7, new/temp cell for: $IN/$AT$6 $AT$6: variable divisor: 1 or 10,000 etc purpose is to shrink $IN to fit in cell, when $IN rises over x decimals. will modify / fix if(at71000 to work later.. 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
|
|||
|
|||
Formula for: Format Decimal places?
On Thu, 3 Nov 2005 11:43:03 -0800, "nastech"
wrote: "Ron Rosenfeld" wrote: On Wed, 2 Nov 2005 21:07:02 -0800, "nastech" wrote: Hi, I am trying to find a way to vary the format for a column of numbers, that depend on the size of the input. Would like to change from zero decimal places, to 2 decimal places if 1000, (am using alternate input if 1000: i.e. Input/1000, then NEED 2 decimal places). Is there a way to modify the number of decimal places viewed, inside an equation. I am using: =IF(AG9=0,"",IF(AT111000,TRUNC($AT$7/AG9,2),($AT$5/AG9))) will / need to see number: e.g. 1085, to 1.08; (no rounding up) If function exists, is it possible to modify decimal places from a single/ absolute cell. thanks in advance. 1. Formatting cannot do what you want as formatting will round and not truncate. 2. You can certainly modify the number of decimals viewed, within a formula, by using the TEXT function. e.g. =TEXT(num,"0.00") or =TEXT(num,"0") depending on your condition. 3. In your text, you indicate you want to display a particular number differently depending on the Input. Since you have four different cell references in your equation, it is not clear which is Input, what your logic is in deciding which formula in your IF statement to display. HTH --ron Hi!, thanks for your reply, um:), Sorry for the lack of detail. I "might" be intermedieat.. I have been playing with conditional formatting alot, but guesse you mean formatting in cell for? but In Excel, "format" and "conditional format" have specific meanings. Format is what you get if you click on the Format item in the top menu bar. "Cells" and "Conditional Formatting" are two of the options. Under "Conditional Formatting" there is no option to do what you describe. But it seems you may be using the term "conditional formatting" differently than does Excel. Since this is an Excel group, I have found that sort of thing frequently leads to confusion. sorry did not label variables: AG9 running records: LAST price AT11, sorry should read $AT$7, new/temp cell for: $IN/$AT$6 $AT$6: variable divisor: 1 or 10,000 etc purpose is to shrink $IN to fit in cell, when $IN rises over x decimals. will modify / fix if(at71000 to work later.. 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
|
|||
|
|||
Formula for: Format Decimal places?
"Ron Rosenfeld" wrote: On Thu, 3 Nov 2005 11:43:03 -0800, "nastech" wrote: "Ron Rosenfeld" wrote: On Wed, 2 Nov 2005 21:07:02 -0800, "nastech" wrote: Hi, I am trying to find a way to vary the format for a column of numbers, that depend on the size of the input. Would like to change from zero decimal places, to 2 decimal places if 1000, (am using alternate input if 1000: i.e. Input/1000, then NEED 2 decimal places). Is there a way to modify the number of decimal places viewed, inside an equation. I am using: =IF(AG9=0,"",IF(AT111000,TRUNC($AT$7/AG9,2),($AT$5/AG9))) will / need to see number: e.g. 1085, to 1.08; (no rounding up) If function exists, is it possible to modify decimal places from a single/ absolute cell. thanks in advance. 1. Formatting cannot do what you want as formatting will round and not truncate. 2. You can certainly modify the number of decimals viewed, within a formula, by using the TEXT function. e.g. =TEXT(num,"0.00") or =TEXT(num,"0") depending on your condition. 3. In your text, you indicate you want to display a particular number differently depending on the Input. Since you have four different cell references in your equation, it is not clear which is Input, what your logic is in deciding which formula in your IF statement to display. HTH --ron Hi!, thanks for your reply, um:), Sorry for the lack of detail. I "might" be intermedieat.. I have been playing with conditional formatting alot, but guesse you mean formatting in cell for? but In Excel, "format" and "conditional format" have specific meanings. Format is what you get if you click on the Format item in the top menu bar. "Cells" and "Conditional Formatting" are two of the options. Under "Conditional Formatting" there is no option to do what you describe. But it seems you may be using the term "conditional formatting" differently than does Excel. Since this is an Excel group, I have found that sort of thing frequently leads to confusion. sorry did not label variables: AG9 running records: LAST price AT11, sorry should read $AT$7, new/temp cell for: $IN/$AT$6 $AT$6: variable divisor: 1 or 10,000 etc purpose is to shrink $IN to fit in cell, when $IN rises over x decimals. will modify / fix if(at71000 to work later.. XXXXX How about this approach: In one cell display, for example, the first three significant digits of your # of shares; and in the adjacent cell display the multiplier. In other words, you are computing the divisor based on the number of shares, rather than entering it manually. To be consistent with what you wrote earlier, we will express the results as a number followed by two decimals. I'll use A1 for the "real number" of shares to buy; B1 for the first three significant digits / 100; and C1 for the multiplier. You can then adapt that to your layout. For example, you compute to purchase 999 shares; A1: 999 B1: 999 C1: 1 A1: 5048 B1: 5.04 C1: 1,000 A1: 21253 B1: 2.12 C1: 10,000 To do the above, you can use the formulas: B1: =TRUNC(A1,2-INT(LOG10(A1)))/10^INT(LOG10(A1)) C1: =10^INT(LOG10(A1)) Formats: B1: Format/Cells/Custom/Type: 0.00 C1: Format/Cells/Custom/Type: #,##0 XXXXXXXXXX XXXXXXXXXX Thankyou, I'm not as fast at it, really appreciate the help. Will learn more how to do by self, but from looking at it I have the intuition that (If your example means some are fixed cells, like what I was trying to do, not sure if I have to do), if not an extra column.. but does yours follow this logic: Header: $AT$3 fixed cell: $IN (as in Dollars IN, all cells in header 1 fixed cell) $AT$4 fixed: fee $AT$5 fixed: =($AT$3-$AT$4) result minus fee $AT$6 fixed: divisor (realized front-back like you said, just didn't/don't see how till I try what you are showing, but don't see jus yet cuz of new eq's/ purpose?..) $AT$7 fixed: =$AT$5/$AT$6 (you can help me what where.. but i get it) right now at7 conflict using 3 decimal places compared to when divisor is 1, too many digits here too. xxxxxxxxxx running data: LAST (ea line entered) BUY: (eq gets too large a number) Buy column eq: =IF(AG9=0,"",IF($AT$71000,TRUNC($AT$7/AG9,2),($AT$5/AG9))) If that chaged what you thought I was doing, else, since don't have any spare space for more columns in view (can put to right), does your example?? :) don't even know what to ask, does it keep the LAST & BUY columns? ALSO: If case, can you put $ signs in front of fixed/absolute cells you are modifying -or- is eq adjustment needed? Sorry if slow on some of it, working on that (documenting commands, sites, sites with commands... vb.., might be scary later) -later Thanks in advance... !! -Nastech XXXXXXXXXX XXXXXXXXXX |
#8
|
|||
|
|||
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
|
|||
|
|||
Formula for: Format Decimal places?
On Thu, 3 Nov 2005 16:19:11 -0800, "nastech"
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Formula for: Format Decimal places?
On Thu, 3 Nov 2005 18:36:04 -0800, "nastech"
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
|
|||
|
|||
Formula for: Format Decimal places?
"Ron Rosenfeld" wrote: On Thu, 3 Nov 2005 18:36:04 -0800, "nastech" 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$51000,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
|
|||
|
|||
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
|
|||
|
|||
Formula for: Format Decimal places?
On Thu, 3 Nov 2005 21:15:01 -0800, "nastech"
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
|
|||
|
|||
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" 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
|
|||
|
|||
Formula for: Format Decimal places?
On Fri, 4 Nov 2005 06:00:04 -0800, "nastech"
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel adds phantom decimal places: why? | Excel Discussion (Misc queries) | |||
decimal places | Excel Discussion (Misc queries) | |||
Moving Decimal Places Up | Excel Worksheet Functions | |||
Adding numbers in a column that have 3 decimal places | Excel Worksheet Functions | |||
ROUND DATA TO 2 DECIMAL PLACES | New Users to Excel |