Formula Conversion Problem

  • Thread starter Thread starter JimS
  • Start date Start date
J

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.
 
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
 
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.
 
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
 
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
 
Bonsour® JimS avec ferveur ;o))) vous nous disiez :
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.
Click to expand...

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

HTH
 
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.
 
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
 
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.
Click to expand...

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
 
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
 
Thank you. I'll try this out as well.

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
Click to expand...
 
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
 
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



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
 
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


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,
Click to expand...

;o)))
what about :
http://groups.google.com/group/micr...8/d70b74aa4ef2506c?lnk=st&q=#d70b74aa4ef2506c
 
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.
Click to expand...

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,
Click to expand...

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
 
Back
Top