TRUNC, another tough challenge....

D

Dervish

Hello,

This newsgroup has been extremely helpful to me the last few days and
I really appreciate all the response. I now have another challenge for
you guys and it is a bit hard to explain but I'll try...

I need to convert raw data generated from a color computer to some
usable format for my co-workers to dispense colorants in paint. If you
look at the example below in cell A1 with the value 2.9765625 - it
means 2 fluid ounces plus .9765625 of /64 of one ounce - I need a
formula that can convert the value of this A1 cell to the something
like the value in B1 cell which is 2Y 62.5 ( 2ounces plus 62.5/64 of
one ounce) the Y is just our symbol of ounce unit. Cell A2 value is
0.125 and so the result in B2 should be 8 (0.125 x 64) and so on ....



A1: 2.9765625
A2: 0.125
A3: 1.4609375
A4: 0.59375
A5: 0.109375
A6: 0.0546875
A7: 0.5
A8: 1.625
A9: 0.0078125
etc...

to:

B1: 2Y 62.5
B2: 8
B3: 1Y 29.5
B4: 38
B5: 7
B6: 3.5
B7: 32
B8: 1Y 40
B9: 0.5

Thanks in advance.

Dervish
 
M

Myrna Larson

If you have the analysis tool pack installed, you can do it this way

=DOLLARFR(A1,64)

That will give you the result 2.625 -- with a decimal point instead of
the "Y ".
 
R

Ron Rosenfeld

Hello,

This newsgroup has been extremely helpful to me the last few days and
I really appreciate all the response. I now have another challenge for
you guys and it is a bit hard to explain but I'll try...

I need to convert raw data generated from a color computer to some
usable format for my co-workers to dispense colorants in paint. If you
look at the example below in cell A1 with the value 2.9765625 - it
means 2 fluid ounces plus .9765625 of /64 of one ounce - I need a
formula that can convert the value of this A1 cell to the something
like the value in B1 cell which is 2Y 62.5 ( 2ounces plus 62.5/64 of
one ounce) the Y is just our symbol of ounce unit. Cell A2 value is
0.125 and so the result in B2 should be 8 (0.125 x 64) and so on ....



A1: 2.9765625
A2: 0.125
A3: 1.4609375
A4: 0.59375
A5: 0.109375
A6: 0.0546875
A7: 0.5
A8: 1.625
A9: 0.0078125
etc...

to:

B1: 2Y 62.5
B2: 8
B3: 1Y 29.5
B4: 38
B5: 7
B6: 3.5
B7: 32
B8: 1Y 40
B9: 0.5

Thanks in advance.

Dervish
Click to expand...


=TEXT(INT(A1),"0""Y "";; ")& MOD(A1,1)*64

If you want the numbers to line up as you have in column B, use a fixed width
font like Courier New.


--ron
 
D

Dervish

Thanks! I'm going to try all these formulas today. I don't have direct
access to newsgroup from work and Google is quite slow for posting...
I'll let you know as soon as I can.

Again, I just want to say thank you to all.

Dervish
 
Top