Formula Conversion Problem

[JimS]

I'm doing some stuff with horseracing, and when I copy the files to
the spreadsheet it looks like this:

22.1 45.4
22.2 44.3
22.3 45.2
22.4 46.3

The fractions are in fifths. It reads 22 and 1/5 second, 22 and 2/5,
44 and 3/5 seconds, etc.

I need to subtract column a from column b, and I THINK I have to
convert the numbers to tenths to do that. So it would be:

22.2
22.4
22.6
etc.

Either there is a way to subtract them without converting to tenths or
I have to convert.

How can I convert these numbers? I drew a blank.

[Pete_UK]

Try this in C1:

=INT(B1)+MOD(B1,1)*2-INT(A1)-MOD(A1,1)*2

The answer will be in tenths. Copy down as required.

Hope this helps.

Pete

[JimS]

I wish I understood what the hell you did. You guys amaze me.

Thanks a lot.

[JimS]

Is there a way to subtract 3 1/2 from 6 1/2, or 2 1/2 from 3 1/2, etc?

I have 3 1/2 in a cell which is how it looks after I imported the
data.

On Fri, 1 Aug 2008 06:29:18 -0700 (PDT), Pete_UK
<pash...@auditel.net> wrote:

[Pete_UK]

You're welcome, Jim.

The INT function takes the whole number part of A1 and B1, whereas the
MOD function takes the decimal part - as this represents fifths in
your cells, each has to be multiplied by 2 to convert into tenths.
Then one number is taken away from the other - simple, really !!

Pete

> >> How can I convert these numbers?  I drew a blank.- Hide quoted text -
>
> - Show quoted text -

[Pete_UK]

If those cells are formatted as fractions (just click on Format |
Cells | Number tab to find out), then you will be able to subtract
them directly with:

=A1-B1

But, as you have imported them they may have come in as text values.
As long as you do not have any other non-visible characters in those
cells (which is quite common if you have imported from a web-site),
then you should still be able to subtract them directly. If not, then
post back and I can give you a formula which should get rid of the
extra characters.

Hope this helps.

Pete

[Mais qui est Paul]

Bonsour® JimS avec ferveur ;o))) vous nous disiez :

=1*SUBSTITUTE(A1&"/5" , "." , " " )
then apply number format :
# ?/5

HTH

--
--
@+
;o)))

[JimS]

3½ 1¼

This is how they look. I just copied and pasted them here. I don't
see any other characters, yet when I try to subtract I get the VALUE
error.

The appeared to be formatted as "general."

I tried reformatting them as "fractions" and I still got the value
error.

[wknehans]

Of course, to maintain consistency in the meaning of the decimal as a fifth
of a second rather than a tenth, you'd have to convert back by dividing the
MOD of the result by 2. Kinda ugly formula, but the end result is more
elegant:

=INT(INT(B1)+MOD(B1,1)*2-INT(A1)-MOD(A1,1)*2)+MOD(INT(B1)+MOD(B1,1)*2-INT(A1)-MOD(A1,1)*2)/2

Cheers,
BD

[Mais qui est Paul]

Bonsour® JimS avec ferveur ;o))) vous nous disiez :

> 3½ 1¼

>
> This is how they look. I just copied and pasted them here. I don't
> see any other characters, yet when I try to subtract I get the VALUE
> error.

in fact in this case the fractional part is not number but Unicode Character
though it's also a pity because all fractionnal parts are not coded with Unicode !!!

you may use an UDF but it will be not efficient in all cases

sample workbook :
http://cjoint.com/?icbROMcXyY

HTH

in standard module

Option Explicit
Function FracU(target)
'change fractionnal unicode in excel value
' 4¾ ==> 4.75 i.e. 4 3/4
Dim xx As String
xx = Left(target, Len(target) - 1)
Select Case Unicode(Right(target, 1))
Case 188
FracU = Evaluate(xx & " 1/4")
Case 189
FracU = Evaluate(xx & " 1/2")
Case 190
FracU = Evaluate(xx & " 3/4")
Case 8531
FracU = Evaluate(xx & " 1/3")
Case 8532
FracU = Evaluate(xx & " 2/3")
Case 8533
FracU = Evaluate(xx & " 1/5")
Case 8534
FracU = Evaluate(xx & " 2/5")
Case 8535
FracU = Evaluate(xx & " 3/5")
Case 8536
FracU = Evaluate(xx & " 4/5")
Case 8537
FracU = Evaluate(xx & " 1/6")
Case 8538
FracU = Evaluate(xx & " 5/6")
Case 8539
FracU = Evaluate(xx & " 1/8")
Case 8540
FracU = Evaluate(xx & " 3/8")
Case 8541
FracU = Evaluate(xx & " 5/8")
Case 8542
FracU = Evaluate(xx & " 7/8")
Case Else
FracU = xx & Right(target, 1)
End Select
End Function

Function Unicode(target)
' Give Unicode value
Unicode = AscW(target)
End Function
Function ChrU(target)
' give Unicode Character
ChrU = ChrW(target)
End Function

--
@+
;o)))

[Pete_UK]

Jim,

those values you have are text values - the characters ¼, ½ and even ¾
have codes of 188, 189 and 190 respectively. So if you have numbers
with those fractional values in columns A and B and you want to
subtract them, you will need to split out the integer and fractional
parts. Assume you have a series of such numbers starting in A2, then
put this formula in C2:

=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),
1*LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2,1))-187)/4,A2)-
IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),
1*LEFT(B2,LEN(B2)-1)+(CODE(RIGHT(B2,1))-187)/4,B2)

and then you can copy this down column C. Here is the result of some
test values I tried out:

6½ 1¼ 5.25
1¼ 3½ -2.25
3½ 1½ 2.00
3½ 2½ 1.00
3½ 1¼ 2.25
6½ 1¼ 5.25
6 3½ 2.50
6½ 4¾ 1.75
6½ 4 2.50
4¾ 3½ 1.25
4¾ 1¼ 3.50

Of course, this subtracts B from A, but if you want it the other way
round you can swap over the references to A2 and B2 (or a quicker way
would be to change the - to a + in the middle of the formula before
the second IF and put a - in front of the first IF).

You can see that this copes with B being larger than A (2nd example),
and it also copes with one (or both) numbers not having fractional
values. It also copes with one of the numbers being expressed as 4.8,
for example. As can be seen, it doesn't convert a result of .25, .50
or .75 back into the respective fraction - I've formatted column C as
a number with 2 dp.

Hope this helps.

Pete

[JimS]

This works. I'll have to play around with it. Thanks for taking the
time.

On Fri, 1 Aug 2008 17:06:34 -0700 (PDT), Pete_UK
<pash...@auditel.net> wrote:

>Jim,
>
>those values you have are text values - the characters ź, ˝ and even ž

>have codes of 188, 189 and 190 respectively. So if you have numbers
>with those fractional values in columns A and B and you want to
>subtract them, you will need to split out the integer and fractional
>parts. Assume you have a series of such numbers starting in A2, then
>put this formula in C2:
>
>=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),
>1*LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2,1))-187)/4,A2)-
>IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),
>1*LEFT(B2,LEN(B2)-1)+(CODE(RIGHT(B2,1))-187)/4,B2)
>
>and then you can copy this down column C. Here is the result of some
>test values I tried out:
>

>6˝ 1ź 5.25
>1ź 3˝ -2.25

>3˝ 1˝ 2.00
>3˝ 2˝ 1.00

>3˝ 1ź 2.25
>6˝ 1ź 5.25
>6 3˝ 2.50
>6˝ 4ž 1.75
>6˝ 4 2.50
>4ž 3˝ 1.25
>4ž 1ź 3.50

>
>Of course, this subtracts B from A, but if you want it the other way
>round you can swap over the references to A2 and B2 (or a quicker way
>would be to change the - to a + in the middle of the formula before
>the second IF and put a - in front of the first IF).
>
>You can see that this copes with B being larger than A (2nd example),
>and it also copes with one (or both) numbers not having fractional
>values. It also copes with one of the numbers being expressed as 4.8,
>for example. As can be seen, it doesn't convert a result of .25, .50
>or .75 back into the respective fraction - I've formatted column C as
>a number with 2 dp.
>
>Hope this helps.
>
>Pete
>
>
>On Aug 1, 10:54 pm, JimS <jim...@msn.com> wrote:

>> 3˝  1ź

[JimS]

Thank you. I'll try this out as well.

[JimS]

Hi, I tried this and I got the error that said I have enterered too
few arguments for this function.

It then highlighted the next to last 2 from the right in the formula
which I have changed from a 2 to "TWO."

=INT(INT(B1)+MOD(B1,1)*2-INT(A1)-MOD(A1,1)*2)+MOD(INT(B1)+MOD(B1,1)*2-INT(A1)-MOD(A1,1)*TWO)/2

On Fri, 1 Aug 2008 15:15:01 -0700, wknehans
<wkne...@discussions.microsoft.com> wrote:

[Pete_UK]

Glad to be of help, Jim.

Pete

On Aug 2, 11:40 am, JimS <jim...@msn.com> wrote:
> This works.  I'll have to play around with it.  Thanks for taking the
> time.
>
> On Fri, 1 Aug 2008 17:06:34 -0700 (PDT), Pete_UK
>
>
>
> <pashu...@auditel.net> wrote:
> >Jim,
>

> >those values you have are text values - the characters ¼, ½ and even ¾

> >have codes of 188, 189 and 190 respectively. So if you have numbers
> >with those fractional values in columns A and B and you want to
> >subtract them, you will need to split out the integer and fractional
> >parts. Assume you have a series of such numbers starting in A2, then
> >put this formula in C2:
>
> >=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),
> >1*LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2,1))-187)/4,A2)-
> >IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),
> >1*LEFT(B2,LEN(B2)-1)+(CODE(RIGHT(B2,1))-187)/4,B2)
>
> >and then you can copy this down column C. Here is the result of some
> >test values I tried out:
>

> >6½    1¼    5.25
> >1¼    3½    -2.25

> >3½    1½    2.00
> >3½    2½    1.00

> >3½    1¼    2.25
> >6½    1¼    5.25
> >6       3½    2.50

> >6½    4¾    1.75
> >6½    4       2.50
> >4¾    3½    1.25

> >4¾    1¼    3.50

>
> >Of course, this subtracts B from A, but if you want it the other way
> >round you can swap over the references to A2 and B2 (or a quicker way
> >would be to change the - to a + in the middle of the formula before
> >the second IF and put a - in front of the first IF).
>
> >You can see that this copes with B being larger than A (2nd example),
> >and it also copes with one (or both) numbers not having fractional
> >values. It also copes with one of the numbers being expressed as 4.8,
> >for example. As can be seen, it doesn't convert a result of .25, .50
> >or .75 back into the respective fraction - I've formatted column C as
> >a number with 2 dp.
>
> >Hope this helps.
>
> >Pete
>
> >On Aug 1, 10:54 pm, JimS <jim...@msn.com> wrote:

> >> 3½  1¼

>
> >> This is how they look.  I just copied and pasted them here.  I don't
> >> see any other characters, yet when I try to subtract I get the VALUE
> >> error.
>
> >> The appeared to be formatted as "general."
>
> >> I tried reformatting them as "fractions" and I still got the value
> >> error.
>

> >> On Fri, 1 Aug 2008 07:57:36 -0700 (PDT), Pete_UK- Hide quoted text -

[Rick Rothstein (MVP - VB)]

Below are two shorter formulas that I posted against the OP's later thread
in this same newsgroup (where he asked how to change the cell references in
the formula); I am posting the exact message here that I used in my response
to the later thread. I thought it best to repeat the formulas here for the
Google archives. Note that at the time I originally posted them against the
later thread, I wasn't aware this thread existed which should, in part,
explain the wording I used in my message. Here is that response...

If, as I suspect, the only non-digits that would appear in A2 and B2 are the
fractional symbols with ASCII/ANSI codes of 188, 189 or 190, then you can
use this much shorter formula to do what your posted formula does...

=IF(ISNUMBER(A2),A2,LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2))-187)/4)-IF(ISNUMBER(B2),B2,LEFT(B2,LEN(B2)-1)+(CODE(RIGHT(B2))-187)/4)

Of course this suffers from repeated use of the A2 and B2 references. The
following formula, while longer than the above formula but still much
shorter than yours, has the benefit that the A2 and B2 references are used
only once each...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(188),".25"),CHAR(189),".50"),CHAR(190),".75")-SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,CHAR(188),".25"),CHAR(189),".5"),CHAR(190),".75")

Rick

"Pete_UK" <pash...@auditel.net> wrote in message
news:06a4c72b-7739-49b9...@m3g2000hsc.googlegroups.com...
Jim,

those values you have are text values - the characters ź, ˝ and even ž

have codes of 188, 189 and 190 respectively. So if you have numbers
with those fractional values in columns A and B and you want to
subtract them, you will need to split out the integer and fractional
parts. Assume you have a series of such numbers starting in A2, then
put this formula in C2:

=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),
1*LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2,1))-187)/4,A2)-
IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),
1*LEFT(B2,LEN(B2)-1)+(CODE(RIGHT(B2,1))-187)/4,B2)

and then you can copy this down column C. Here is the result of some
test values I tried out:

6˝ 1ź 5.25
1ź 3˝ -2.25

3˝ 1˝ 2.00
3˝ 2˝ 1.00

3˝ 1ź 2.25
6˝ 1ź 5.25
6 3˝ 2.50

6˝ 4ž 1.75
6˝ 4 2.50
4ž 3˝ 1.25

4ž 1ź 3.50

Of course, this subtracts B from A, but if you want it the other way
round you can swap over the references to A2 and B2 (or a quicker way
would be to change the - to a + in the middle of the formula before
the second IF and put a - in front of the first IF).

You can see that this copes with B being larger than A (2nd example),
and it also copes with one (or both) numbers not having fractional
values. It also copes with one of the numbers being expressed as 4.8,
for example. As can be seen, it doesn't convert a result of .25, .50
or .75 back into the respective fraction - I've formatted column C as
a number with 2 dp.

Hope this helps.

Pete

On Aug 1, 10:54 pm, JimS <jim...@msn.com> wrote:

> 3˝ 1ź

[Mais qui est Paul]

Bonsour® Rick Rothstein (MVP - VB) avec ferveur ;o))) vous nous disiez :

> If, as I suspect, the only non-digits that would appear in A2 and B2
> are the fractional symbols with ASCII/ANSI codes of 188, 189 or 190,

;o)))
what about :
http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/cf24536326712338/d70b74aa4ef2506c?lnk=st&q=#d70b74aa4ef2506c

--
--
@+
;o)))

[Rick Rothstein (MVP - VB)]

I do not have access to a Unicode font possessing ASCII/ANSI codes as large
as your VB code indicates (8531+), so I cannot test the formula below out.
However, if the code numbers you posted in your VB code are correct and if
the worksheet CODE function will return ASCII/ANSI code values in the 8000s
for such characters, then this formula should work (to subtract two numbers
with or without fractional characters attached to their end)....

=IF(ISNUMBER(A2),A2,LEFT(A2,LEN(A2)-1)+IF(OR(CODE(RIGHT(A2))={188,189,190}),(CODE(RIGHT(A2))-187)/4,IF(OR(CODE(RIGHT(A2))={8531,8532}),(CODE(RIGHT(A2))-8530)/3,IF(OR(CODE(RIGHT(A2))={8533,8534,8535,8536}),(CODE(RIGHT(A2))-8532)/5,IF(OR(CODE(RIGHT(A2))={8537,8538}),(1+4*(CODE(RIGHT(A2))-8537))/6,IF(X88,(1+2*(CODE(RIGHT(A2))-8539))/8,""))))))-IF(ISNUMBER(B2),B2,LEFT(B2,LEN(B2)-1)+IF(OR(CODE(RIGHT(B2))={188,189,190}),(CODE(RIGHT(B2))-187)/4,IF(OR(CODE(RIGHT(B2))={8531,8532}),(CODE(RIGHT(B2))-8530)/3,IF(OR(CODE(RIGHT(B2))={8533,8534,8535,8536}),(CODE(RIGHT(B2))-8532)/5,IF(OR(CODE(RIGHT(B2))={8537,8538}),(1+4*(CODE(RIGHT(B2))-8537))/6,IF(X88,(1+2*(CODE(RIGHT(B2))-8539))/8,""))))))

Rick

"Mais qui est Paul" <Excel-...@mpfe.fr> wrote in message
news:%23PXYaKW...@TK2MSFTNGP03.phx.gbl...

[Pete_UK]

Hi Rick,

thanks for posting the alternative formulae.

Pete

On Aug 3, 10:03 am, "Rick Rothstein \(MVP - VB\)"

<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Below are two shorter formulas that I posted against the OP's later thread
> in this same newsgroup (where he asked how to change the cell references in
> the formula); I am posting the exact message here that I used in my response
> to the later thread. I thought it best to repeat the formulas here for the
> Google archives. Note that at the time I originally posted them against the
> later thread, I wasn't aware this thread existed which should, in part,
> explain the wording I used in my message. Here is that response...
>
> If, as I suspect, the only non-digits that would appear in A2 and B2 are the
> fractional symbols with ASCII/ANSI codes of 188, 189 or 190, then you can
> use this much shorter formula to do what your posted formula does...
>

> =IF(ISNUMBER(A2),A2,LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2))-187)/4)-IF(ISNUMBER­(B2),B2,LEFT(B2,LEN(B2)-1)+(CODE(RIGHT(B2))-187)/4)

>
> Of course this suffers from repeated use of the A2 and B2 references. The
> following formula, while longer than the above formula but still much
> shorter than yours, has the benefit that the A2 and B2 references are used
> only once each...
>

> =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(188),".25"),CHAR(189),".50"),CHAR­(190),".75")-SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,CHAR(188),".25"),CHAR(189)­,".5"),CHAR(190),".75")
>
> Rick
>
> "Pete_UK" <pashu...@auditel.net> wrote in message
>
> news:06a4c72b-7739-49b9...@m3g2000hsc.googlegroups.com...
> Jim,
>
> those values you have are text values - the characters ¼, ½ and even ¾

> have codes of 188, 189 and 190 respectively. So if you have numbers
> with those fractional values in columns A and B and you want to
> subtract them, you will need to split out the integer and fractional
> parts. Assume you have a series of such numbers starting in A2, then
> put this formula in C2:
>
> =IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),
> 1*LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2,1))-187)/4,A2)-
> IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),
> 1*LEFT(B2,LEN(B2)-1)+(CODE(RIGHT(B2,1))-187)/4,B2)
>
> and then you can copy this down column C. Here is the result of some
> test values I tried out:
>

> 6½    1¼    5.25
> 1¼    3½    -2.25

> 3½    1½    2.00
> 3½    2½    1.00

> 3½    1¼    2.25
> 6½    1¼    5.25
> 6       3½    2.50

> 6½    4¾    1.75
> 6½    4       2.50
> 4¾    3½    1.25

> 4¾    1¼    3.50

>
> Of course, this subtracts B from A, but if you want it the other way
> round you can swap over the references to A2 and B2 (or a quicker way
> would be to change the - to a + in the middle of the formula before
> the second IF and put a - in front of the first IF).
>
> You can see that this copes with B being larger than A (2nd example),
> and it also copes with one (or both) numbers not having fractional
> values. It also copes with one of the numbers being expressed as 4.8,
> for example. As can be seen, it doesn't convert a result of .25, .50
> or .75 back into the respective fraction - I've formatted column C as
> a number with 2 dp.
>
> Hope this helps.
>
> Pete
>
> On Aug 1, 10:54 pm, JimS <jim...@msn.com> wrote:
>
>
>

> > 3½ 1¼

>
> > This is how they look. I just copied and pasted them here. I don't
> > see any other characters, yet when I try to subtract I get the VALUE
> > error.
>
> > The appeared to be formatted as "general."
>
> > I tried reformatting them as "fractions" and I still got the value
> > error.
>

> > On Fri, 1 Aug 2008 07:57:36 -0700 (PDT), Pete_UK- Hide quoted text -

[Mais qui est Paul]

Bonsour® Rick Rothstein (MVP - VB) avec ferveur ;o))) vous nous disiez :

> I do not have access to a Unicode font possessing ASCII/ANSI codes as

> large as your VB code indicates (8531+), so I cannot test the formula
> below out.

The police ARIAL (Windows XP) possesses these UNICODE properties ,
this is not the case for all TrueType fonts.

> However, if the code numbers you posted in your VB code
> are correct and if the worksheet CODE function will return ASCII/ANSI
> code values in the 8000s for such characters,

Unfortunately CODE function works only for character value lower to 256.

We have to use VBA-functions such as ChrW or AscW to access UNICODE properties .
(see 2 UDFs come with my example)

Function Unicode(target As String)
' ----Give Unicode value
Unicode = AscW(Left(target, 1))
End Function

Function ChrU(target As Integer)
' ----give Unicode Character

ChrU = ChrW(target)
End Function

HTH

--
@+
;o)))