060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

custom format required is 0"B"

this converts 1.51 to 2 (eg no decimal places) and the B is added on
the end


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=536678

On Wed, 26 Apr 2006 20:18:01 -0700, nastech
wrote:

060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

Custom formats only work for numeric values. Since 1.511B is not a numeric
value, but rather a text value, you will not be able to do what you want with a
custom format.


--ron

thankyou

"robert111" wrote:


custom format required is 0"B"

this converts 1.51 to 2 (eg no decimal places) and the B is added on
the end


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=536678

thankyou

"Ron Rosenfeld" wrote:

On Wed, 26 Apr 2006 20:18:01 -0700, nastech
wrote:

060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

Custom formats only work for numeric values. Since 1.511B is not a numeric
value, but rather a text value, you will not be able to do what you want with a
custom format.


--ron

thankyou again, is there any work around? maybe not critical then, but should
I post as a script request question, or do you think that might be wasted
time? (resources etc.)

"Ron Rosenfeld" wrote:

On Wed, 26 Apr 2006 20:18:01 -0700, nastech
wrote:

060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

Custom formats only work for numeric values. Since 1.511B is not a numeric
value, but rather a text value, you will not be able to do what you want with a
custom format.


--ron

Sorry I didnt appreciate 1.51B was a text string

say it is in cell A1

len(A1) returns 5
search(A1,".") returns 2
5-2=3
3-2=1
left(a1,1)=1
2+1=3
mid(A1,3,(5-2-1)) returns 51
len(51)=2
10 to the power 2 returns 100
100/2 returns 50
if 51 is bigger than 50 then add 1 to "the 1 returned in the LEFT
function otherwise add 0 to the 1 returned in the left function"

you now have your desired answer of 2 (it may be text so you need to
use the value function to turn it into a number)

FINALLY format it as in my previous text.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=536678

060427 Need Custom format, not a formula
Hi, you beat me back. thought I might have something of a work around...
have to say I can rip things apart, put back together.. maybe not that good
though.
can you give some context what I can do with those items?

and was thinking of items together for workaround:
a. make ms fix it :)
b. - use conditional format color for all over 1000 to denote Billions
(other work columns that work this column e.g.: work with ~ left(len)-1
stuff
- left justify (have to re-locate lining up decimal formatting, if
possible here?

again, with your stuff, is that for use in formula's? will keep / review,
but download of data I get is in form of mostly 1.5M or 1.5B some
1.511...

c. would use: delete all M's & assume in Millions, unless has a "B"
CAN i DO SOMETHING WITH THAT? Thanks.


"robert111" wrote:


Sorry I didnt appreciate 1.51B was a text string

say it is in cell A1

len(A1) returns 5
search(A1,".") returns 2
5-2=3
3-2=1
left(a1,1)=1
2+1=3
mid(A1,3,(5-2-1)) returns 51
len(51)=2
10 to the power 2 returns 100
100/2 returns 50
if 51 is bigger than 50 then add 1 to "the 1 returned in the LEFT
function otherwise add 0 to the 1 returned in the left function"

you now have your desired answer of 2 (it may be text so you need to
use the value function to turn it into a number)

FINALLY format it as in my previous text.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=536678

currently working: format cells, alignment (distributed, distributed) gets
rid of view of M & B, Looking to line up decimal point if "can-do". thanks

"robert111" wrote:


Sorry I didnt appreciate 1.51B was a text string

say it is in cell A1

len(A1) returns 5
search(A1,".") returns 2
5-2=3
3-2=1
left(a1,1)=1
2+1=3
mid(A1,3,(5-2-1)) returns 51
len(51)=2
10 to the power 2 returns 100
100/2 returns 50
if 51 is bigger than 50 then add 1 to "the 1 returned in the LEFT
function otherwise add 0 to the 1 returned in the left function"

you now have your desired answer of 2 (it may be text so you need to
use the value function to turn it into a number)

FINALLY format it as in my previous text.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=536678

On Thu, 27 Apr 2006 08:40:02 -0700, nastech
wrote:

thankyou again, is there any work around? maybe not critical then, but should
I post as a script request question, or do you think that might be wasted
time? (resources etc.)

"Ron Rosenfeld" wrote:

On Wed, 26 Apr 2006 20:18:01 -0700, nastech
wrote:

060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

Custom formats only work for numeric values. Since 1.511B is not a numeric
value, but rather a text value, you will not be able to do what you want with a
custom format.


--ron


If you can use a formula, it would be simple to translate your 1.511B; 1.511M;
etc.

Without knowing the range of values you might be using, it's a bit hard to come
up with a general formula.

Also, depending on whether you want the result to be a "text string" or a "real
number", the method would be different.

So if you can define your requirements more completely, a solution will be
forthcoming.
--ron

Hi, sorry, can be difficult to explain, kept getting formula's - needed
format.. but had to repost 4-6 times... losing track:

downloading data to column where I mostly need it (midst other down loaded
data, by copy-paste at this point: thats the rub/why)

don't have use for separate work column for that. other columns that work
off that data, e.g.: 1.5B some 1.511B in length, 1.5M.... have equations
that work fine using concept of: Left(LEN)-1 etc.

column data in very hard to read / too wide. (as above:
120.511B
1.0M
1.2B ...

think uderstand from someone here you can not format text string.
was using what can for work around, maybe looking for a center? justify
decimal point where left/right move out from center (read / saw that
yesterday morning)
for life of MS not searcable: center-justify on decimal point...?? saw it,
know its there, experimenting with:

- if haven't said, would have MS allow number/ text string format e.g.:
#.0? (? - single char), especially since that is how data is being sent,
proves the need.
- alignment, distributed, gets rid of viewing M B
- cond. format so 1B goes different color (M/B still there) other eq's work
- idea: can delete all M's, but danger, some figures will be <1M... and
adjust other formulas accordingly (but might do since minimum standard is set
at 1M..

can't think of anything else right now, just flying around left-right
justify, distributed, trying to find: center on decimal?..




"Ron Rosenfeld" wrote:

On Thu, 27 Apr 2006 08:40:02 -0700, nastech
wrote:

thankyou again, is there any work around? maybe not critical then, but should
I post as a script request question, or do you think that might be wasted
time? (resources etc.)

"Ron Rosenfeld" wrote:

On Wed, 26 Apr 2006 20:18:01 -0700, nastech
wrote:

060426-5 Need Custom format, not a formula
(Not looking for formula's).

Just need "Custom Format" for how column of e.g.: 1.511B will look like: 2B
That which you get by: Right-Click cell, Format Cells.., Number tab,
Custom:
then what do you type in place of #,##0?
(question mark included in this example)
may not be wording this right?

column has e.g.: 1.511M 1.511B

Custom formats only work for numeric values. Since 1.511B is not a numeric
value, but rather a text value, you will not be able to do what you want with a
custom format.


--ron


If you can use a formula, it would be simple to translate your 1.511B; 1.511M;
etc.

Without knowing the range of values you might be using, it's a bit hard to come
up with a general formula.

Also, depending on whether you want the result to be a "text string" or a "real
number", the method would be different.

So if you can define your requirements more completely, a solution will be
forthcoming.
--ron

"nastech" wrote in message
...
Hi, sorry, can be difficult to explain, kept getting formula's - needed
format.. but had to repost 4-6 times... losing track:


Your're not the only one <g

If it is still a format you are looking for then I posted one in one of your
other threads:


[=1000000000]#.###,,,"B";[=1000000]#.###,,"M";General


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk

Hi ! lost you lost too? hey, if that was an answer you gave before sorry.
(I am hi contrast, look like a rocket-scientist one minute... just put alot
of effort in sometimes looks good..)

there you a what do I do with that? will just try to past the whole
thing in the custom cell box & see what happens... min..
(shoud I/ could I use power to 9/6 ? 10^9, 10^6

pasted exactly what you have, including: general = no change/ don't see e.g.:
1B 1M tried:

[=1000000000]#.###,,,"B";[=1000000]#.###,,"M";General
[=1000000000]#,,,"B";[=1000000]#,,"M";General
[=1000000000]#.,,,"B";[=1000000]#.,,"M";General

no change, do I have some other setting that might interfere? Thanks !

"Sandy Mann" wrote:

"nastech" wrote in message
...
Hi, sorry, can be difficult to explain, kept getting formula's - needed
format.. but had to repost 4-6 times... losing track:


Your're not the only one <g

If it is still a format you are looking for then I posted one in one of your
other threads:


[=1000000000]#.###,,,"B";[=1000000]#.###,,"M";General


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk

The format I gave was intended for the cell containing your formula not the
incoming data. If your imcoming data is text then as far as I know there is
no formatting that can change the actual text of the cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"nastech" wrote in message
...
Hi ! lost you lost too? hey, if that was an answer you gave before
sorry.
(I am hi contrast, look like a rocket-scientist one minute... just put
alot
of effort in sometimes looks good..)

there you a what do I do with that? will just try to past the whole
thing in the custom cell box & see what happens... min..
(shoud I/ could I use power to 9/6 ? 10^9, 10^6

pasted exactly what you have, including: general = no change/ don't see
e.g.:
1B 1M tried:

[=1000000000]#.###,,,"B";[=1000000]#.###,,"M";General
[=1000000000]#,,,"B";[=1000000]#,,"M";General
[=1000000000]#.,,,"B";[=1000000]#.,,"M";General

no change, do I have some other setting that might interfere? Thanks !

"Sandy Mann" wrote:

"nastech" wrote in message
...
Hi, sorry, can be difficult to explain, kept getting formula's - needed
format.. but had to repost 4-6 times... losing track:


Your're not the only one <g

If it is still a format you are looking for then I posted one in one of
your
other threads:


[=1000000000]#.###,,,"B";[=1000000]#.###,,"M";General


--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk

On Thu, 27 Apr 2006 13:25:01 -0700, nastech
wrote:

Hi, sorry, can be difficult to explain, kept getting formula's - needed
format.. but had to repost 4-6 times... losing track:

downloading data to column where I mostly need it (midst other down loaded
data, by copy-paste at this point: thats the rub/why)

don't have use for separate work column for that. other columns that work
off that data, e.g.: 1.5B some 1.511B in length, 1.5M.... have equations
that work fine using concept of: Left(LEN)-1 etc.

column data in very hard to read / too wide. (as above:
120.511B
1.0M
1.2B ...


Let me ask some questions which can be answered simply, in order to try to
clarify the situation.

1. Does every entry end with a letter?
2. Are the letters always "B" or "M"?

3. If there are other letters that an entry might end with, what are they and
what do they stand for?


--ron

Hi, thanks for reply, had been working with user: sandy mann in different
window, not sure if closer to what want. He? thought I was abbreviating
(always trying to be literal). so he though I wanted to convert billion
figu 1,511,000,000 to ? (I need 2B) with his example (last word, he
hasn't gotten back since posted my idea below that):

his: (my numbers are in 1.5B or M format, not 1000000000 etc)
[=1000000000]#.###,,,"B";[=1000000]#.###,,"M";General

gross idea: (probably won't work, general means formatting?)
[#.#B]#,,,"B";[#.#M]#,,"M";General


in the following, have to leave column in place, leave numbers as is
(although not supposed to, are some less than 1M, with straight up numbers).
I need (as below): 810M 11B 115B 3B 60B 4B 890M....

actual figures are as stated. I did a direct copy-paste, here (with my tool):

810.1M 11.359B 114.7B 3.180B 60.489B 4.017B 890.4M 355.8B 183.7B 129.2B
32.538B 1.646B 1.780B 11.934B 10.949B 280.0B 4.972B 1.276B 1.066B 33.017B
75.059B 23.865B 67.961B 127.5B 7.5M 7.5M 36.9M 9.8M 22.0M 70.7M
93.7M 91.0M 3.3M 2.1M 18.5M 7.3M 11.0M 11.2M 11.3M 3.0M 5.4M 39.9M 14.0M
11.4M 177.9M 27.1M 105.9M 14.3M 46.2M 188.4M 32.4M 172.7M 33.3M 318.1M 178.9M
131.2M 4.508B 1.9M 34.5M 22.991B 126.6B 1.402B 3.071B 3.091B
43.523B 3.080B 1.962B 23.713B bta

"Ron Rosenfeld" wrote:

On Thu, 27 Apr 2006 13:25:01 -0700, nastech
wrote:

Hi, sorry, can be difficult to explain, kept getting formula's - needed
format.. but had to repost 4-6 times... losing track:

downloading data to column where I mostly need it (midst other down loaded
data, by copy-paste at this point: thats the rub/why)

don't have use for separate work column for that. other columns that work
off that data, e.g.: 1.5B some 1.511B in length, 1.5M.... have equations
that work fine using concept of: Left(LEN)-1 etc.

column data in very hard to read / too wide. (as above:
120.511B
1.0M
1.2B ...


Let me ask some questions which can be answered simply, in order to try to
clarify the situation.

1. Does every entry end with a letter?
2. Are the letters always "B" or "M"?

3. If there are other letters that an entry might end with, what are they and
what do they stand for?


--ron

On Thu, 27 Apr 2006 19:27:02 -0700, nastech
wrote:



in the following, have to leave column in place, leave numbers as is
(although not supposed to, are some less than 1M, with straight up numbers).
I need (as below): 810M 11B 115B 3B 60B 4B 890M....

actual figures are as stated. I did a direct copy-paste, here (with my tool):

810.1M 11.359B 114.7B 3.180B 60.489B 4.017B 890.4M 355.8B 183.7B 129.2B
32.538B 1.646B 1.780B 11.934B 10.949B 280.0B 4.972B 1.276B 1.066B 33.017B
75.059B 23.865B 67.961B 127.5B 7.5M 7.5M 36.9M 9.8M 22.0M 70.7M
93.7M 91.0M 3.3M 2.1M 18.5M 7.3M 11.0M 11.2M 11.3M 3.0M 5.4M 39.9M 14.0M
11.4M 177.9M 27.1M 105.9M 14.3M 46.2M 188.4M 32.4M 172.7M 33.3M 318.1M 178.9M
131.2M 4.508B 1.9M 34.5M 22.991B 126.6B 1.402B 3.071B 3.091B
43.523B 3.080B 1.962B 23.713B bta


Are all those values in a single cell? Or is there one entry per cell?

I do note that in your example, every "number" is followed by an M or a B.
Will that always be the case?

Assume:

1. One entry per cell.
2. Each entry always ends with a letter.

Formula:
=ROUND(LEFT(A1,LEN(A1)-1),0)&RIGHT(A1,1)

If your data is different, please state exactly how it differs.


--ron

hi... have equations to manipulate the data at will.
- each number is 99% of time followed by B or M, occasionally will have
straight up number less than 1M, e.g.: 832555
- each number is in its own cell.

What is need is Custom Format for "View" of those numbers, as previous
couple of posts, maybe something like:

WHAT NEED:
gross idea: (probably won't work, general means formatting?) FIX NEXT LINE.
[#.#B]#,,,"B";[#.#M]#,,"M";General

From SandyMann: (my numbers are in 1.5B or M format, "NOT" 1000000000 etc)
[=1000000000]#.###,,,"B";[=1000000]#.###,,"M";General

XXXXXXXXXXXXXXXXXXXXXXXX

"Ron Rosenfeld" wrote:



On Thu, 27 Apr 2006 19:27:02 -0700, nastech
wrote:



in the following, have to leave column in place, leave numbers as is
(although not supposed to, are some less than 1M, with straight up numbers).
I need (as below): 810M 11B 115B 3B 60B 4B 890M....

actual figures are as stated. I did a direct copy-paste, here (with my tool):

810.1M 11.359B 114.7B 3.180B 60.489B 4.017B 890.4M 355.8B 183.7B 129.2B
32.538B 1.646B 1.780B 11.934B 10.949B 280.0B 4.972B 1.276B 1.066B 33.017B
75.059B 23.865B 67.961B 127.5B 7.5M 7.5M 36.9M 9.8M 22.0M 70.7M
93.7M 91.0M 3.3M 2.1M 18.5M 7.3M 11.0M 11.2M 11.3M 3.0M 5.4M 39.9M 14.0M
11.4M 177.9M 27.1M 105.9M 14.3M 46.2M 188.4M 32.4M 172.7M 33.3M 318.1M 178.9M
131.2M 4.508B 1.9M 34.5M 22.991B 126.6B 1.402B 3.071B 3.091B
43.523B 3.080B 1.962B 23.713B bta


Are all those values in a single cell? Or is there one entry per cell?

I do note that in your example, every "number" is followed by an M or a B.
Will that always be the case?

Assume:

1. One entry per cell.
2. Each entry always ends with a letter.

Formula:
=ROUND(LEFT(A1,LEN(A1)-1),0)&RIGHT(A1,1)

If your data is different, please state exactly how it differs.


--ron

On Fri, 28 Apr 2006 11:35:02 -0700, nastech
wrote:

hi... have equations to manipulate the data at will.
- each number is 99% of time followed by B or M, occasionally will have
straight up number less than 1M, e.g.: 832555
- each number is in its own cell.

How do you want a value like 83255 to be displayed in the cell?


What is need is Custom Format for "View" of those numbers, as previous
couple of posts, maybe something like:

WHAT NEED:
gross idea: (probably won't work, general means formatting?) FIX NEXT LINE.
[#.#B]#,,,"B";[#.#M]#,,"M";General

As has been written a number of times by various contributors, formatting will
only affect numeric values. There is no way to have a cell contain a value of
1.5B and display 2B. You can do that in an adjacent cell with a formula, or
you can use a macro to actually change the value in the same cell, but then you
will lose your original value.

So you are going to have to decide what you want now that you know what is
possible.

Once you answer my question as to how you want 832555 to be displayed, then I
can modify the formula I gave you earlier to take that into account.
--ron

Hi, thanks for the patience. I guesse I've had a couple of precepts, but
wasn't sure what real answer was..

- as presented, have these that work:
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)

=IF(OR(CE9=0,BZ9={0,"n/a"}),"",
(LEFT(BZ9,LEN(BZ9)-1)*IF(RIGHT(BZ9,1)="b",10^9,1)/CE9*$BV$4))

- also figured would be a work around for just about anything/ can't beleive
this isn't possible for so simple a thing, would be?: #? as a format
- don't think its a math thing? just lack of programming
- told by more than one, can program a computer to do anything.. emc mass
data storage programmers... thanks again

"Ron Rosenfeld" wrote:

On Fri, 28 Apr 2006 11:35:02 -0700, nastech
wrote:

hi... have equations to manipulate the data at will.
- each number is 99% of time followed by B or M, occasionally will have
straight up number less than 1M, e.g.: 832555
- each number is in its own cell.

How do you want a value like 83255 to be displayed in the cell?


What is need is Custom Format for "View" of those numbers, as previous
couple of posts, maybe something like:

WHAT NEED:
gross idea: (probably won't work, general means formatting?) FIX NEXT LINE.
[#.#B]#,,,"B";[#.#M]#,,"M";General

As has been written a number of times by various contributors, formatting will
only affect numeric values. There is no way to have a cell contain a value of
1.5B and display 2B. You can do that in an adjacent cell with a formula, or
you can use a macro to actually change the value in the same cell, but then you
will lose your original value.

So you are going to have to decide what you want now that you know what is
possible.

Once you answer my question as to how you want 832555 to be displayed, then I
can modify the formula I gave you earlier to take that into account.
--ron

Your question appears to relate as much to the format input as the
format displayed. The Custom formats relate to how data is displayed,
not to how it is input/retrieved/held.

For your comment - told by more than one, can program a computer to do anything.. emc
mass
data storage programmers... thanks again
I'm sure MS would be able to achieve that, for the right price.

--

nastech Wrote:
Hi, thanks for the patience. I guesse I've had a couple of precepts,
but
wasn't sure what real answer was..

- as presented, have these that work:
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)

=IF(OR(CE9=0,BZ9={0,"n/a"}),"",
(LEFT(BZ9,LEN(BZ9)-1)*IF(RIGHT(BZ9,1)="b",10^9,1)/CE9*$BV$4))

- also figured would be a work around for just about anything/ can't
beleive
this isn't possible for so simple a thing, would be?: #? as a
format
- don't think its a math thing? just lack of programming
- told by more than one, can program a computer to do anything.. emc
mass
data storage programmers... thanks again

"Ron Rosenfeld" wrote:

On Fri, 28 Apr 2006 11:35:02 -0700, nastech

wrote:

hi... have equations to manipulate the data at will.
- each number is 99% of time followed by B or M, occasionally will
have
straight up number less than 1M, e.g.: 832555
- each number is in its own cell.

How do you want a value like 83255 to be displayed in the cell?


What is need is Custom Format for "View" of those numbers, as
previous
couple of posts, maybe something like:

WHAT NEED:
gross idea: (probably won't work, general means formatting?) FIX
NEXT LINE.
[#.#B]#,,,"B";[#.#M]#,,"M";General

As has been written a number of times by various contributors,
formatting will
only affect numeric values. There is no way to have a cell contain a
value of
1.5B and display 2B. You can do that in an adjacent cell with a
formula, or
you can use a macro to actually change the value in the same cell,
but then you
will lose your original value.

So you are going to have to decide what you want now that you know
what is
possible.

Once you answer my question as to how you want 832555 to be
displayed, then I
can modify the formula I gave you earlier to take that into account.
--ron



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=536678

On Fri, 28 Apr 2006 18:49:02 -0700, nastech
wrote:

Hi, thanks for the patience. I guesse I've had a couple of precepts, but
wasn't sure what real answer was..

- as presented, have these that work:
=TEXT(LEFT(BZ9,LEN(BZ9)-1),"#,##0")&RIGHT(BZ9,1)

=IF(OR(CE9=0,BZ9={0,"n/a"}),"",
(LEFT(BZ9,LEN(BZ9)-1)*IF(RIGHT(BZ9,1)="b",10^9,1)/CE9*$BV$4))

- also figured would be a work around for just about anything/ can't beleive
this isn't possible for so simple a thing, would be?: #? as a format
- don't think its a math thing? just lack of programming
- told by more than one, can program a computer to do anything.. emc mass
data storage programmers... thanks again

You're welcome. Glad you've got things working.


--ron