Mick,
Actually it is possible if you make an assumption that the original data
cells are formatted as text cells rather than a numeric cell. I think you
have to anyway simply because a numeric cell with a value of say 3.1 is the
same as 3.10. A text format would preserve the actual text.
Thus you can use text manipulation based on this assumption. Even if the
original cells are not in text format, it is easy to change them. The
following formula will work in this case, not for this example that C1 is
the cell containg the original data in stone.pound format. Simply change
all instances of C1 to the proper cell in the spreadsheet..
=MID(C1,1,SEARCH(".",C1,1)-1)*14+MID(C1,SEARCH(".",C1,1)+1,2)
What this does is use the "SEARCH" function to find the '.' delimiter, then
it uses"MID" to pick off the part before the '.', multiply it by 14, and
add in the part after the '.', again using the "MID" function to get just
the characters after the '.'.
Russ Poffenberger
[log in to unmask]
At 10:02 AM 9/27/2005, you wrote:
>Dear Mick and Peter,
>
>Maybe I'm missing something since math was not part of my climb to
>scholastic greatness, (come to think of it no subject was), but I am of
>the opinion that the 12 (stones).5(pounds) example will never work since
>we are trying to fractionalize apples (stones) and oranges (pounds). I
>believe that the .5 should be .3571 (the value of 5 pounds translated into
>its equivalent value in stones. Then the formula will work. (I think)
>
>Happy calculation,
>
>Maurice J. Reid
Do you want to signoff PCSOFT or just change to
Digest mode - visit our web site:
http://freepctech.com/pcsoft.shtml
|