software

Hi, I am looking for way to custom format cell to read 1.511B or 1.511M
as: 1B 1M (also would like example for 1.5B or 1.5M). thanks

already have cell formatted as Number, works with other work column with e.g.:
=IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)=quot;bquot;,10^9,1)/CE138*$BV$4))

Just looking for custom formatting such as: ##0.0*nastech

In your formula does the LEFT and RIGHT Functions refer to the B in 1.511B?
If so then formatting will not work with it because the B are not real
letters that can be tested for. For example a custom format of quot;0.00Bquot;
(with out the quotes) and with 123 entered in the cell will show as 123.00B
Testing it with =RIGHT(A1,1) will return 3 because the .00B are not real.
The only thing in the cell is the 123 you entered in the first place.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;nastechquot; gt; wrote in message
...
gt; Hi, I am looking for way to custom format cell to read 1.511B or 1.511M
gt; as: 1B 1M (also would like example for 1.5B or 1.5M). thanks
gt;
gt; already have cell formatted as Number, works with other work column with
gt; e.g.:
gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)=quot;bquot;,10^9,1)/CE138*$BV$4))
gt;
gt; Just looking for custom formatting such as: ##0.0*
gt;

Hi, I think I get what you're saying, but not sure what answer is.. right
now.. anyways 1st looking for result of view 1B, 2nd choice of 1.5B (or M).
Formula does what you asked, negates right most character.
Sounds like I cannot do what needing to do.
Might repost sometime to see if any ideas, but looks like I am in a narrow
area for something that might not have been thought of. Thanks.

quot;Sandy Mannquot; wrote:

gt; nastech
gt;
gt; In your formula does the LEFT and RIGHT Functions refer to the B in 1.511B?
gt; If so then formatting will not work with it because the B are not real
gt; letters that can be tested for. For example a custom format of quot;0.00Bquot;
gt; (with out the quotes) and with 123 entered in the cell will show as 123.00B
gt; Testing it with =RIGHT(A1,1) will return 3 because the .00B are not real.
gt; The only thing in the cell is the 123 you entered in the first place.
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt; In Perth, the ancient capital of Scotland
gt;
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;nastechquot; gt; wrote in message
gt; ...
gt; gt; Hi, I am looking for way to custom format cell to read 1.511B or 1.511M
gt; gt; as: 1B 1M (also would like example for 1.5B or 1.5M). thanks
gt; gt;
gt; gt; already have cell formatted as Number, works with other work column with
gt; gt; e.g.:
gt; gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)=quot;bquot;,10^9,1)/CE138*$BV$4))
gt; gt;
gt; gt; Just looking for custom formatting such as: ##0.0*
gt; gt;
gt;
gt;
gt;
gt;

alternate (possible) formatting (e.g.) might have been: #,##0? ?
after looking a little more.

quot;Sandy Mannquot; wrote:

gt; nastech
gt;
gt; In your formula does the LEFT and RIGHT Functions refer to the B in 1.511B?
gt; If so then formatting will not work with it because the B are not real
gt; letters that can be tested for. For example a custom format of quot;0.00Bquot;
gt; (with out the quotes) and with 123 entered in the cell will show as 123.00B
gt; Testing it with =RIGHT(A1,1) will return 3 because the .00B are not real.
gt; The only thing in the cell is the 123 you entered in the first place.
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt; In Perth, the ancient capital of Scotland
gt;
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;nastechquot; gt; wrote in message
gt; ...
gt; gt; Hi, I am looking for way to custom format cell to read 1.511B or 1.511M
gt; gt; as: 1B 1M (also would like example for 1.5B or 1.5M). thanks
gt; gt;
gt; gt; already have cell formatted as Number, works with other work column with
gt; gt; e.g.:
gt; gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)=quot;bquot;,10^9,1)/CE138*$BV$4))
gt; gt;
gt; gt; Just looking for custom formatting such as: ##0.0*
gt; gt;
gt;
gt;
gt;
gt;

what found so far:
Hi, am looking for way to custom format cell to read 1.511B or 1.511M
as: 1B 1M (both B for billion amp; M.. present in column, thanks)

may need to use worker column to get rid of unwanted digits?
separate work column might not work for me, but would be:
=TEXT(LEFT(BZ9,LEN(BZ9)-1),quot;#,##0quot;)amp;RIGHT(BZ9,1)

Just looking for custom formatting such as: #,##0? ??

Is this an area not possible for custom formatting in Excel?
XXXXXXXXXXXXXXXXXX

quot;Sandy Mannquot; wrote:

gt; nastech
gt;
gt; In your formula does the LEFT and RIGHT Functions refer to the B in 1.511B?
gt; If so then formatting will not work with it because the B are not real
gt; letters that can be tested for. For example a custom format of quot;0.00Bquot;
gt; (with out the quotes) and with 123 entered in the cell will show as 123.00B
gt; Testing it with =RIGHT(A1,1) will return 3 because the .00B are not real.
gt; The only thing in the cell is the 123 you entered in the first place.
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt; In Perth, the ancient capital of Scotland
gt;
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;nastechquot; gt; wrote in message
gt; ...
gt; gt; Hi, I am looking for way to custom format cell to read 1.511B or 1.511M
gt; gt; as: 1B 1M (also would like example for 1.5B or 1.5M). thanks
gt; gt;
gt; gt; already have cell formatted as Number, works with other work column with
gt; gt; e.g.:
gt; gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)=quot;bquot;,10^9,1)/CE138*$BV$4))
gt; gt;
gt; gt; Just looking for custom formatting such as: ##0.0*
gt; gt;
gt;
gt;
gt;
gt;

nastech,

Assuming that you mean American Billions not British Billions, the Custom
Format that you are searching for is:

[gt;=1000000000]#,,,quot;Bquot;;[gt;=1000000]#,,quot;Mquot;;General

However this will not do what you want. Try this:

Custom Format A1 with the above custom format, then enter 1000000002 You
should see 1B in the cell as you want.
Now in another cell enter the formula:

=RIGHT(A1,1)

Do you see 'B' ? No! you see the figure 2 because the 'B' does not exist,
it is simply formatting the same as if you format a cell as currency you see
the $ or £ sign in front of the numbers but if you test it with =Right(A1,1)
you get the first number not a dollar sign.

I don't really follow what you are trying to do in your formula but try
something like:

=IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,IF((LEN(INT(BZ138))gt;9)*ISNUMBER(BZ138),BZ1 38,1/CE138*$BV$4))

I assume that if BZ138 is a billion or more then you want to divide BZ138 by
CE138*$BV$4 otherwise return the reciprocal of CE138*$BV$4

If there is no chance that BZ138 will contain decimals then you can replace
the INT(BZ138) with just BZ138

--
HTH

Sandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;nastechquot; gt; wrote in message
...
gt; what found so far:
gt; Hi, am looking for way to custom format cell to read 1.511B or 1.511M
gt; as: 1B 1M (both B for billion amp; M.. present in column, thanks)
gt;
gt; may need to use worker column to get rid of unwanted digits?
gt; separate work column might not work for me, but would be:
gt; =TEXT(LEFT(BZ9,LEN(BZ9)-1),quot;#,##0quot;)amp;RIGHT(BZ9,1)
gt;
gt; Just looking for custom formatting such as: #,##0? ??
gt;
gt; Is this an area not possible for custom formatting in Excel?
gt; XXXXXXXXXXXXXXXXXX
gt;
gt; quot;Sandy Mannquot; wrote:
gt;
gt;gt; nastech
gt;gt;
gt;gt; In your formula does the LEFT and RIGHT Functions refer to the B in
gt;gt; 1.511B?
gt;gt; If so then formatting will not work with it because the B are not real
gt;gt; letters that can be tested for. For example a custom format of quot;0.00Bquot;
gt;gt; (with out the quotes) and with 123 entered in the cell will show as
gt;gt; 123.00B
gt;gt; Testing it with =RIGHT(A1,1) will return 3 because the .00B are not real.
gt;gt; The only thing in the cell is the 123 you entered in the first place.
gt;gt;
gt;gt; --
gt;gt; HTH
gt;gt;
gt;gt; Sandy
gt;gt; In Perth, the ancient capital of Scotland
gt;gt;
gt;gt;
gt;gt; with @tiscali.co.uk
gt;gt;
gt;gt;
gt;gt; quot;nastechquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hi, I am looking for way to custom format cell to read 1.511B or
gt;gt; gt; 1.511M
gt;gt; gt; as: 1B 1M (also would like example for 1.5B or 1.5M). thanks
gt;gt; gt;
gt;gt; gt; already have cell formatted as Number, works with other work column
gt;gt; gt; with
gt;gt; gt; e.g.:
gt;gt; gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)=quot;bquot;,10^9,1)/CE138*$BV$4))
gt;gt; gt;
gt;gt; gt; Just looking for custom formatting such as: ##0.0*
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
Hi, thankyou! think some of that is a little past me, but with explanations
I see should be able to work thru it. (I can tear things apart well, and
look like I know more than.. anyways) sorry if not include enough
information, if know what to include, what am doing:

- download Millions / Billions figures, in form of 1.5 or 1.511 M or B for
US Millions/Billions
- mass copy-paste effort (till get rich / hire programmer, nk) is in midst
of other data
- view of 1.5M or 1.5B type data hurts. (right now trying to align
decimals points) with:
- using: format cells, alignment: distrib. distrib. gets rid of view M's
/ B's
- using cond. format for color B / M separately
- other work columns (off this column work well already with ~ LEFT(LEN)-1
stuff

e.g.: (do not need formula's, thanks, what have works), except will look at
prev.
=IF(OR(CE9=0,BZ9={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ9,LEN(BZ9)-1)*IF(RIGHT(BZ9,1)=quot;bquot;,10^9,1)/CE9*$BV$4))

(left(len)-1 * if(B,1000000, else 1 / last price * (fixed cell) 0.3 of
market cap: estimated max avail. shares.

summary: custom format is for view only, not in an equation.

quot;Sandy Mannquot; wrote:

gt; nastech,
gt;
gt; Assuming that you mean American Billions not British Billions, the Custom
gt; Format that you are searching for is:
gt;
gt; [gt;=1000000000]#,,,quot;Bquot;;[gt;=1000000]#,,quot;Mquot;;General
gt;
gt; However this will not do what you want. Try this:
gt;
gt; Custom Format A1 with the above custom format, then enter 1000000002 You
gt; should see 1B in the cell as you want.
gt; Now in another cell enter the formula:
gt;
gt; =RIGHT(A1,1)
gt;
gt; Do you see 'B' ? No! you see the figure 2 because the 'B' does not exist,
gt; it is simply formatting the same as if you format a cell as currency you see
gt; the $ or £ sign in front of the numbers but if you test it with =Right(A1,1)
gt; you get the first number not a dollar sign.
gt;
gt; I don't really follow what you are trying to do in your formula but try
gt; something like:
gt;
gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,IF((LEN(INT(BZ138))gt;9)*ISNUMBER(BZ138),BZ1 38,1/CE138*$BV$4))
gt;
gt; I assume that if BZ138 is a billion or more then you want to divide BZ138 by
gt; CE138*$BV$4 otherwise return the reciprocal of CE138*$BV$4
gt;
gt; If there is no chance that BZ138 will contain decimals then you can replace
gt; the INT(BZ138) with just BZ138
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt; In Perth, the ancient capital of Scotland
gt;
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;nastechquot; gt; wrote in message
gt; ...
gt; gt; what found so far:
gt; gt; Hi, am looking for way to custom format cell to read 1.511B or 1.511M
gt; gt; as: 1B 1M (both B for billion amp; M.. present in column, thanks)
gt; gt;
gt; gt; may need to use worker column to get rid of unwanted digits?
gt; gt; separate work column might not work for me, but would be:
gt; gt; =TEXT(LEFT(BZ9,LEN(BZ9)-1),quot;#,##0quot;)amp;RIGHT(BZ9,1)
gt; gt;
gt; gt; Just looking for custom formatting such as: #,##0? ??
gt; gt;
gt; gt; Is this an area not possible for custom formatting in Excel?
gt; gt; XXXXXXXXXXXXXXXXXX
gt; gt;
gt; gt; quot;Sandy Mannquot; wrote:
gt; gt;
gt; gt;gt; nastech
gt; gt;gt;
gt; gt;gt; In your formula does the LEFT and RIGHT Functions refer to the B in
gt; gt;gt; 1.511B?
gt; gt;gt; If so then formatting will not work with it because the B are not real
gt; gt;gt; letters that can be tested for. For example a custom format of quot;0.00Bquot;
gt; gt;gt; (with out the quotes) and with 123 entered in the cell will show as
gt; gt;gt; 123.00B
gt; gt;gt; Testing it with =RIGHT(A1,1) will return 3 because the .00B are not real.
gt; gt;gt; The only thing in the cell is the 123 you entered in the first place.
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; HTH
gt; gt;gt;
gt; gt;gt; Sandy
gt; gt;gt; In Perth, the ancient capital of Scotland
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; with @tiscali.co.uk
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;nastechquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; Hi, I am looking for way to custom format cell to read 1.511B or
gt; gt;gt; gt; 1.511M
gt; gt;gt; gt; as: 1B 1M (also would like example for 1.5B or 1.5M). thanks
gt; gt;gt; gt;
gt; gt;gt; gt; already have cell formatted as Number, works with other work column
gt; gt;gt; gt; with
gt; gt;gt; gt; e.g.:
gt; gt;gt; gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)=quot;bquot;,10^9,1)/CE138*$BV$4))
gt; gt;gt; gt;
gt; gt;gt; gt; Just looking for custom formatting such as: ##0.0*
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

nastech,

First off my apologies, the format that I gave you is not what you are
looking for - I had forgotten the in the OP you quoted 1.511 - the format
that I gave you will only show the nearest Billion or Million.

I think that I understand what it is that you are doing better now - if the
result of your formula is:
1,511,000,000 or 1,500,000 or similar
then format the cell as:

[gt;=1000000000]#.###,,,quot;Bquot;;[gt;=1000000]#.###,,quot;Mquot;;General

--
HTH

Sandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;nastechquot; gt; wrote in message
...
gt; Hi, thankyou! think some of that is a little past me, but with
gt; explanations
gt; I see should be able to work thru it. (I can tear things apart well, and
gt; look like I know more than.. anyways) sorry if not include enough
gt; information, if know what to include, what am doing:
gt;
gt; - download Millions / Billions figures, in form of 1.5 or 1.511 M or B
gt; for
gt; US Millions/Billions
gt; - mass copy-paste effort (till get rich / hire programmer, nk) is in midst
gt; of other data
gt; - view of 1.5M or 1.5B type data hurts. (right now trying to align
gt; decimals points) with:
gt; - using: format cells, alignment: distrib. distrib. gets rid of view
gt; M's
gt; / B's
gt; - using cond. format for color B / M separately
gt; - other work columns (off this column work well already with ~
gt; LEFT(LEN)-1
gt; stuff
gt;
gt; e.g.: (do not need formula's, thanks, what have works), except will look
gt; at
gt; prev.
gt; =IF(OR(CE9=0,BZ9={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ9,LEN(BZ9)-1)*IF(RIGHT(BZ9,1)=quot;bquot;,10^9,1)/CE9*$BV$4))
gt;
gt; (left(len)-1 * if(B,1000000, else 1 / last price * (fixed cell) 0.3 of
gt; market cap: estimated max avail. shares.
gt;
gt; summary: custom format is for view only, not in an equation.
gt;
gt; quot;Sandy Mannquot; wrote:
gt;
gt;gt; nastech,
gt;gt;
gt;gt; Assuming that you mean American Billions not British Billions, the Custom
gt;gt; Format that you are searching for is:
gt;gt;
gt;gt; [gt;=1000000000]#,,,quot;Bquot;;[gt;=1000000]#,,quot;Mquot;;General
gt;gt;
gt;gt; However this will not do what you want. Try this:
gt;gt;
gt;gt; Custom Format A1 with the above custom format, then enter 1000000002 You
gt;gt; should see 1B in the cell as you want.
gt;gt; Now in another cell enter the formula:
gt;gt;
gt;gt; =RIGHT(A1,1)
gt;gt;
gt;gt; Do you see 'B' ? No! you see the figure 2 because the 'B' does not
gt;gt; exist,
gt;gt; it is simply formatting the same as if you format a cell as currency you
gt;gt; see
gt;gt; the $ or £ sign in front of the numbers but if you test it with
gt;gt; =Right(A1,1)
gt;gt; you get the first number not a dollar sign.
gt;gt;
gt;gt; I don't really follow what you are trying to do in your formula but try
gt;gt; something like:
gt;gt;
gt;gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,IF((LEN(INT(BZ138))gt;9)*ISNUMBER(BZ138),BZ1 38,1/CE138*$BV$4))
gt;gt;
gt;gt; I assume that if BZ138 is a billion or more then you want to divide BZ138
gt;gt; by
gt;gt; CE138*$BV$4 otherwise return the reciprocal of CE138*$BV$4
gt;gt;
gt;gt; If there is no chance that BZ138 will contain decimals then you can
gt;gt; replace
gt;gt; the INT(BZ138) with just BZ138
gt;gt;
gt;gt; --
gt;gt; HTH
gt;gt;
gt;gt; Sandy
gt;gt; In Perth, the ancient capital of Scotland
gt;gt;
gt;gt;
gt;gt; with @tiscali.co.uk
gt;gt;
gt;gt;
gt;gt; quot;nastechquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; what found so far:
gt;gt; gt; Hi, am looking for way to custom format cell to read 1.511B or
gt;gt; gt; 1.511M
gt;gt; gt; as: 1B 1M (both B for billion amp; M.. present in column, thanks)
gt;gt; gt;
gt;gt; gt; may need to use worker column to get rid of unwanted digits?
gt;gt; gt; separate work column might not work for me, but would be:
gt;gt; gt; =TEXT(LEFT(BZ9,LEN(BZ9)-1),quot;#,##0quot;)amp;RIGHT(BZ9,1)
gt;gt; gt;
gt;gt; gt; Just looking for custom formatting such as: #,##0? ??
gt;gt; gt;
gt;gt; gt; Is this an area not possible for custom formatting in Excel?
gt;gt; gt; XXXXXXXXXXXXXXXXXX
gt;gt; gt;
gt;gt; gt; quot;Sandy Mannquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; nastech
gt;gt; gt;gt;
gt;gt; gt;gt; In your formula does the LEFT and RIGHT Functions refer to the B in
gt;gt; gt;gt; 1.511B?
gt;gt; gt;gt; If so then formatting will not work with it because the B are not real
gt;gt; gt;gt; letters that can be tested for. For example a custom format of
gt;gt; gt;gt; quot;0.00Bquot;
gt;gt; gt;gt; (with out the quotes) and with 123 entered in the cell will show as
gt;gt; gt;gt; 123.00B
gt;gt; gt;gt; Testing it with =RIGHT(A1,1) will return 3 because the .00B are not
gt;gt; gt;gt; real.
gt;gt; gt;gt; The only thing in the cell is the 123 you entered in the first place.
gt;gt; gt;gt;
gt;gt; gt;gt; --
gt;gt; gt;gt; HTH
gt;gt; gt;gt;
gt;gt; gt;gt; Sandy
gt;gt; gt;gt; In Perth, the ancient capital of Scotland
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; with @tiscali.co.uk
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt; quot;nastechquot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt; Hi, I am looking for way to custom format cell to read 1.511B or
gt;gt; gt;gt; gt; 1.511M
gt;gt; gt;gt; gt; as: 1B 1M (also would like example for 1.5B or 1.5M).
gt;gt; gt;gt; gt; thanks
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; already have cell formatted as Number, works with other work column
gt;gt; gt;gt; gt; with
gt;gt; gt;gt; gt; e.g.:
gt;gt; gt;gt; gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)=quot;bquot;,10^9,1)/CE138*$BV$4))
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Just looking for custom formatting such as: ##0.0*
gt;gt; gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;

Negative, my problem for not iterating that what am typing is exact.

Here we a (mega-detailed when can be) the following is what is quot;exactly
downloadedquot;

1.5B
1.5M
1.511M
200.0B

that is all combinations, my equation posted is hard to look at, but would
have be clue (hard because what does not happen is left out of equation,
hence shorter equation, whole nother discussion) format working with is as
above. thanks

quot;Sandy Mannquot; wrote:

gt; nastech,
gt;
gt; First off my apologies, the format that I gave you is not what you are
gt; looking for - I had forgotten the in the OP you quoted 1.511 - the format
gt; that I gave you will only show the nearest Billion or Million.
gt;
gt; I think that I understand what it is that you are doing better now - if the
gt; result of your formula is:
gt; 1,511,000,000 or 1,500,000 or similar
gt; then format the cell as:
gt;
gt; [gt;=1000000000]#.###,,,quot;Bquot;;[gt;=1000000]#.###,,quot;Mquot;;General
gt;
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt; In Perth, the ancient capital of Scotland
gt;
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;nastechquot; gt; wrote in message
gt; ...
gt; gt; Hi, thankyou! think some of that is a little past me, but with
gt; gt; explanations
gt; gt; I see should be able to work thru it. (I can tear things apart well, and
gt; gt; look like I know more than.. anyways) sorry if not include enough
gt; gt; information, if know what to include, what am doing:
gt; gt;
gt; gt; - download Millions / Billions figures, in form of 1.5 or 1.511 M or B
gt; gt; for
gt; gt; US Millions/Billions
gt; gt; - mass copy-paste effort (till get rich / hire programmer, nk) is in midst
gt; gt; of other data
gt; gt; - view of 1.5M or 1.5B type data hurts. (right now trying to align
gt; gt; decimals points) with:
gt; gt; - using: format cells, alignment: distrib. distrib. gets rid of view
gt; gt; M's
gt; gt; / B's
gt; gt; - using cond. format for color B / M separately
gt; gt; - other work columns (off this column work well already with ~
gt; gt; LEFT(LEN)-1
gt; gt; stuff
gt; gt;
gt; gt; e.g.: (do not need formula's, thanks, what have works), except will look
gt; gt; at
gt; gt; prev.
gt; gt; =IF(OR(CE9=0,BZ9={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ9,LEN(BZ9)-1)*IF(RIGHT(BZ9,1)=quot;bquot;,10^9,1)/CE9*$BV$4))
gt; gt;
gt; gt; (left(len)-1 * if(B,1000000, else 1 / last price * (fixed cell) 0.3 of
gt; gt; market cap: estimated max avail. shares.
gt; gt;
gt; gt; summary: custom format is for view only, not in an equation.
gt; gt;
gt; gt; quot;Sandy Mannquot; wrote:
gt; gt;
gt; gt;gt; nastech,
gt; gt;gt;
gt; gt;gt; Assuming that you mean American Billions not British Billions, the Custom
gt; gt;gt; Format that you are searching for is:
gt; gt;gt;
gt; gt;gt; [gt;=1000000000]#,,,quot;Bquot;;[gt;=1000000]#,,quot;Mquot;;General
gt; gt;gt;
gt; gt;gt; However this will not do what you want. Try this:
gt; gt;gt;
gt; gt;gt; Custom Format A1 with the above custom format, then enter 1000000002 You
gt; gt;gt; should see 1B in the cell as you want.
gt; gt;gt; Now in another cell enter the formula:
gt; gt;gt;
gt; gt;gt; =RIGHT(A1,1)
gt; gt;gt;
gt; gt;gt; Do you see 'B' ? No! you see the figure 2 because the 'B' does not
gt; gt;gt; exist,
gt; gt;gt; it is simply formatting the same as if you format a cell as currency you
gt; gt;gt; see
gt; gt;gt; the $ or £ sign in front of the numbers but if you test it with
gt; gt;gt; =Right(A1,1)
gt; gt;gt; you get the first number not a dollar sign.
gt; gt;gt;
gt; gt;gt; I don't really follow what you are trying to do in your formula but try
gt; gt;gt; something like:
gt; gt;gt;
gt; gt;gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,IF((LEN(INT(BZ138))gt;9)*ISNUMBER(BZ138),BZ1 38,1/CE138*$BV$4))
gt; gt;gt;
gt; gt;gt; I assume that if BZ138 is a billion or more then you want to divide BZ138
gt; gt;gt; by
gt; gt;gt; CE138*$BV$4 otherwise return the reciprocal of CE138*$BV$4
gt; gt;gt;
gt; gt;gt; If there is no chance that BZ138 will contain decimals then you can
gt; gt;gt; replace
gt; gt;gt; the INT(BZ138) with just BZ138
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; HTH
gt; gt;gt;
gt; gt;gt; Sandy
gt; gt;gt; In Perth, the ancient capital of Scotland
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; with @tiscali.co.uk
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;nastechquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; what found so far:
gt; gt;gt; gt; Hi, am looking for way to custom format cell to read 1.511B or
gt; gt;gt; gt; 1.511M
gt; gt;gt; gt; as: 1B 1M (both B for billion amp; M.. present in column, thanks)
gt; gt;gt; gt;
gt; gt;gt; gt; may need to use worker column to get rid of unwanted digits?
gt; gt;gt; gt; separate work column might not work for me, but would be:
gt; gt;gt; gt; =TEXT(LEFT(BZ9,LEN(BZ9)-1),quot;#,##0quot;)amp;RIGHT(BZ9,1)
gt; gt;gt; gt;
gt; gt;gt; gt; Just looking for custom formatting such as: #,##0? ??
gt; gt;gt; gt;
gt; gt;gt; gt; Is this an area not possible for custom formatting in Excel?
gt; gt;gt; gt; XXXXXXXXXXXXXXXXXX
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Sandy Mannquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; nastech
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; In your formula does the LEFT and RIGHT Functions refer to the B in
gt; gt;gt; gt;gt; 1.511B?
gt; gt;gt; gt;gt; If so then formatting will not work with it because the B are not real
gt; gt;gt; gt;gt; letters that can be tested for. For example a custom format of
gt; gt;gt; gt;gt; quot;0.00Bquot;
gt; gt;gt; gt;gt; (with out the quotes) and with 123 entered in the cell will show as
gt; gt;gt; gt;gt; 123.00B
gt; gt;gt; gt;gt; Testing it with =RIGHT(A1,1) will return 3 because the .00B are not
gt; gt;gt; gt;gt; real.
gt; gt;gt; gt;gt; The only thing in the cell is the 123 you entered in the first place.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; --
gt; gt;gt; gt;gt; HTH
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Sandy
gt; gt;gt; gt;gt; In Perth, the ancient capital of Scotland
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; with @tiscali.co.uk
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;nastechquot; gt; wrote in message
gt; gt;gt; gt;gt; ...
gt; gt;gt; gt;gt; gt; Hi, I am looking for way to custom format cell to read 1.511B or
gt; gt;gt; gt;gt; gt; 1.511M
gt; gt;gt; gt;gt; gt; as: 1B 1M (also would like example for 1.5B or 1.5M).
gt; gt;gt; gt;gt; gt; thanks
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; already have cell formatted as Number, works with other work column
gt; gt;gt; gt;gt; gt; with
gt; gt;gt; gt;gt; gt; e.g.:
gt; gt;gt; gt;gt; gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)=quot;bquot;,10^9,1)/CE138*$BV$4))
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; Just looking for custom formatting such as: ##0.0*
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
gt;

need to see incomming: 1.5B or 1.511B look like 2B
and 1.5M or 1.511M look like 2M thanks

quot;Sandy Mannquot; wrote:

gt; nastech,
gt;
gt; First off my apologies, the format that I gave you is not what you are
gt; looking for - I had forgotten the in the OP you quoted 1.511 - the format
gt; that I gave you will only show the nearest Billion or Million.
gt;
gt; I think that I understand what it is that you are doing better now - if the
gt; result of your formula is:
gt; 1,511,000,000 or 1,500,000 or similar
gt; then format the cell as:
gt;
gt; [gt;=1000000000]#.###,,,quot;Bquot;;[gt;=1000000]#.###,,quot;Mquot;;General
gt;
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt; In Perth, the ancient capital of Scotland
gt;
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;nastechquot; gt; wrote in message
gt; ...
gt; gt; Hi, thankyou! think some of that is a little past me, but with
gt; gt; explanations
gt; gt; I see should be able to work thru it. (I can tear things apart well, and
gt; gt; look like I know more than.. anyways) sorry if not include enough
gt; gt; information, if know what to include, what am doing:
gt; gt;
gt; gt; - download Millions / Billions figures, in form of 1.5 or 1.511 M or B
gt; gt; for
gt; gt; US Millions/Billions
gt; gt; - mass copy-paste effort (till get rich / hire programmer, nk) is in midst
gt; gt; of other data
gt; gt; - view of 1.5M or 1.5B type data hurts. (right now trying to align
gt; gt; decimals points) with:
gt; gt; - using: format cells, alignment: distrib. distrib. gets rid of view
gt; gt; M's
gt; gt; / B's
gt; gt; - using cond. format for color B / M separately
gt; gt; - other work columns (off this column work well already with ~
gt; gt; LEFT(LEN)-1
gt; gt; stuff
gt; gt;
gt; gt; e.g.: (do not need formula's, thanks, what have works), except will look
gt; gt; at
gt; gt; prev.
gt; gt; =IF(OR(CE9=0,BZ9={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ9,LEN(BZ9)-1)*IF(RIGHT(BZ9,1)=quot;bquot;,10^9,1)/CE9*$BV$4))
gt; gt;
gt; gt; (left(len)-1 * if(B,1000000, else 1 / last price * (fixed cell) 0.3 of
gt; gt; market cap: estimated max avail. shares.
gt; gt;
gt; gt; summary: custom format is for view only, not in an equation.
gt; gt;
gt; gt; quot;Sandy Mannquot; wrote:
gt; gt;
gt; gt;gt; nastech,
gt; gt;gt;
gt; gt;gt; Assuming that you mean American Billions not British Billions, the Custom
gt; gt;gt; Format that you are searching for is:
gt; gt;gt;
gt; gt;gt; [gt;=1000000000]#,,,quot;Bquot;;[gt;=1000000]#,,quot;Mquot;;General
gt; gt;gt;
gt; gt;gt; However this will not do what you want. Try this:
gt; gt;gt;
gt; gt;gt; Custom Format A1 with the above custom format, then enter 1000000002 You
gt; gt;gt; should see 1B in the cell as you want.
gt; gt;gt; Now in another cell enter the formula:
gt; gt;gt;
gt; gt;gt; =RIGHT(A1,1)
gt; gt;gt;
gt; gt;gt; Do you see 'B' ? No! you see the figure 2 because the 'B' does not
gt; gt;gt; exist,
gt; gt;gt; it is simply formatting the same as if you format a cell as currency you
gt; gt;gt; see
gt; gt;gt; the $ or £ sign in front of the numbers but if you test it with
gt; gt;gt; =Right(A1,1)
gt; gt;gt; you get the first number not a dollar sign.
gt; gt;gt;
gt; gt;gt; I don't really follow what you are trying to do in your formula but try
gt; gt;gt; something like:
gt; gt;gt;
gt; gt;gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,IF((LEN(INT(BZ138))gt;9)*ISNUMBER(BZ138),BZ1 38,1/CE138*$BV$4))
gt; gt;gt;
gt; gt;gt; I assume that if BZ138 is a billion or more then you want to divide BZ138
gt; gt;gt; by
gt; gt;gt; CE138*$BV$4 otherwise return the reciprocal of CE138*$BV$4
gt; gt;gt;
gt; gt;gt; If there is no chance that BZ138 will contain decimals then you can
gt; gt;gt; replace
gt; gt;gt; the INT(BZ138) with just BZ138
gt; gt;gt;
gt; gt;gt; --
gt; gt;gt; HTH
gt; gt;gt;
gt; gt;gt; Sandy
gt; gt;gt; In Perth, the ancient capital of Scotland
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; with @tiscali.co.uk
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; quot;nastechquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt; gt; what found so far:
gt; gt;gt; gt; Hi, am looking for way to custom format cell to read 1.511B or
gt; gt;gt; gt; 1.511M
gt; gt;gt; gt; as: 1B 1M (both B for billion amp; M.. present in column, thanks)
gt; gt;gt; gt;
gt; gt;gt; gt; may need to use worker column to get rid of unwanted digits?
gt; gt;gt; gt; separate work column might not work for me, but would be:
gt; gt;gt; gt; =TEXT(LEFT(BZ9,LEN(BZ9)-1),quot;#,##0quot;)amp;RIGHT(BZ9,1)
gt; gt;gt; gt;
gt; gt;gt; gt; Just looking for custom formatting such as: #,##0? ??
gt; gt;gt; gt;
gt; gt;gt; gt; Is this an area not possible for custom formatting in Excel?
gt; gt;gt; gt; XXXXXXXXXXXXXXXXXX
gt; gt;gt; gt;
gt; gt;gt; gt; quot;Sandy Mannquot; wrote:
gt; gt;gt; gt;
gt; gt;gt; gt;gt; nastech
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; In your formula does the LEFT and RIGHT Functions refer to the B in
gt; gt;gt; gt;gt; 1.511B?
gt; gt;gt; gt;gt; If so then formatting will not work with it because the B are not real
gt; gt;gt; gt;gt; letters that can be tested for. For example a custom format of
gt; gt;gt; gt;gt; quot;0.00Bquot;
gt; gt;gt; gt;gt; (with out the quotes) and with 123 entered in the cell will show as
gt; gt;gt; gt;gt; 123.00B
gt; gt;gt; gt;gt; Testing it with =RIGHT(A1,1) will return 3 because the .00B are not
gt; gt;gt; gt;gt; real.
gt; gt;gt; gt;gt; The only thing in the cell is the 123 you entered in the first place.
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; --
gt; gt;gt; gt;gt; HTH
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; Sandy
gt; gt;gt; gt;gt; In Perth, the ancient capital of Scotland
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; with @tiscali.co.uk
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt; quot;nastechquot; gt; wrote in message
gt; gt;gt; gt;gt; ...
gt; gt;gt; gt;gt; gt; Hi, I am looking for way to custom format cell to read 1.511B or
gt; gt;gt; gt;gt; gt; 1.511M
gt; gt;gt; gt;gt; gt; as: 1B 1M (also would like example for 1.5B or 1.5M).
gt; gt;gt; gt;gt; gt; thanks
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; already have cell formatted as Number, works with other work column
gt; gt;gt; gt;gt; gt; with
gt; gt;gt; gt;gt; gt; e.g.:
gt; gt;gt; gt;gt; gt; =IF(OR(CE138=0,BZ138={0,quot;n/aquot;}),quot;quot;,(LEFT(BZ138,LEN(BZ138)-1)*IF(RIGHT(BZ138,1)=quot;bquot;,10^9,1)/CE138*$BV$4))
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt; gt; Just looking for custom formatting such as: ##0.0*
gt; gt;gt; gt;gt; gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;
gt;