Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I have a column of decimal values in an Excel 2003. I have converted the
values to fractions using the "format cell" function and would like to extract the numerator values from the fractions. I thought I could do this by converting text to columns with the "/" as a delimiter, such that the numerator and denominator values would be returned in separate columns. The problem is that even though my values show as a fraction, they are still read as decimals by Excel and the text to columns won't work. I've tried using the "paste special" function to replace the decimals with the fraction values, but that did not work either. Any ideas on how to get Excel to read the values as fractions so that I can use the text to columns function; or another method that will allow me to extract the numerator values? (I don't have a common denominator, so I can't use the dollar functions) Thanks! |
#2
![]() |
|||
|
|||
![]()
This should give you a column of numerator values extracted from the original column of fractions.
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Try something like this:
A1: (a number....displayed as a fraction or not) This formula returns the 32nd's numerator, if any : =LEFT(TEXT(MOD(A1,1),"#/32"),FIND("/",TEXT(MOD(A1,1),"#/32"))-1) Example: A1: =5/32 B1 returns 5 and...this formula returns whatever numerator Excel uses with a general fraction display: =LEFT(TEXT(MOD(A1,1),"#/##"),FIND("/",TEXT(MOD(A1,1),"#/##"))-1) Example: A1: =1 3/13.....converts to: =1.23076923076923 B1 returns 3 Note though, without knowing the denominator...does only knowing the numerator help? Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "tf" wrote in message ... I have a column of decimal values in an Excel 2003. I have converted the values to fractions using the "format cell" function and would like to extract the numerator values from the fractions. I thought I could do this by converting text to columns with the "/" as a delimiter, such that the numerator and denominator values would be returned in separate columns. The problem is that even though my values show as a fraction, they are still read as decimals by Excel and the text to columns won't work. I've tried using the "paste special" function to replace the decimals with the fraction values, but that did not work either. Any ideas on how to get Excel to read the values as fractions so that I can use the text to columns function; or another method that will allow me to extract the numerator values? (I don't have a common denominator, so I can't use the dollar functions) Thanks! |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
< I thought I could do this by converting text to columns with the "/" as a delimiter
You were on the right track. This is easiest with helper columns, but can be done in one formula: =RIGHT(TEXT(A1,"? ?/?"),LEN(TEXT(A1,"? ?/?"))-FIND("/",TEXT(A1,"? ?/?"))) -- Kind regards, Niek Otten Microsoft MVP - Excel "tf" wrote in message ... |I have a column of decimal values in an Excel 2003. I have converted the | values to fractions using the "format cell" function and would like to | extract the numerator values from the fractions. I thought I could do this | by converting text to columns with the "/" as a delimiter, such that the | numerator and denominator values would be returned in separate columns. The | problem is that even though my values show as a fraction, they are still read | as decimals by Excel and the text to columns won't work. I've tried using | the "paste special" function to replace the decimals with the fraction | values, but that did not work either. | | Any ideas on how to get Excel to read the values as fractions so that I can | use the text to columns function; or another method that will allow me to | extract the numerator values? (I don't have a common denominator, so I can't | use the dollar functions) | | Thanks! |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
"tf" wrote in message
... I have a column of decimal values in an Excel 2003. I have converted the values to fractions using the "format cell" function and would like to extract the numerator values from the fractions. I thought I could do this by converting text to columns with the "/" as a delimiter, such that the numerator and denominator values would be returned in separate columns. The problem is that even though my values show as a fraction, they are still read as decimals by Excel and the text to columns won't work. I've tried using the "paste special" function to replace the decimals with the fraction values, but that did not work either. Any ideas on how to get Excel to read the values as fractions so that I can use the text to columns function; or another method that will allow me to extract the numerator values? (I don't have a common denominator, so I can't use the dollar functions) Thanks! Why are you trying to do this? What will you do with the numerator? What do you want to achieve? I ask because formatting a cell as "fraction" (of some specified kind) just displays an approximation to the value in the cell that will fit with the specified format. The error is variable. The "fraction" format is intended just for display, not for subsequent calculation. It's difficult to see what use the numerator of such an approximate fraction could possibly be, which leads me to question whether or not you understand the "fraction" format and its (severe) limitations. |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi Stephen,
I don't think you're right, but you made me think and test, so I changed my formula to =RIGHT(TEXT(A1,"? ?/????????"),LEN(TEXT(A1,"? ?/????????"))-FIND("/",TEXT(A1,"? ?/????????"))) Thanks! -- Kind regards, Niek Otten Microsoft MVP - Excel "Stephen" <none wrote in message ... | "tf" wrote in message | ... | I have a column of decimal values in an Excel 2003. I have converted the | values to fractions using the "format cell" function and would like to | extract the numerator values from the fractions. I thought I could do | this | by converting text to columns with the "/" as a delimiter, such that the | numerator and denominator values would be returned in separate columns. | The | problem is that even though my values show as a fraction, they are still | read | as decimals by Excel and the text to columns won't work. I've tried using | the "paste special" function to replace the decimals with the fraction | values, but that did not work either. | | Any ideas on how to get Excel to read the values as fractions so that I | can | use the text to columns function; or another method that will allow me to | extract the numerator values? (I don't have a common denominator, so I | can't | use the dollar functions) | | Thanks! | | Why are you trying to do this? What will you do with the numerator? What do | you want to achieve? I ask because formatting a cell as "fraction" (of some | specified kind) just displays an approximation to the value in the cell that | will fit with the specified format. The error is variable. The "fraction" | format is intended just for display, not for subsequent calculation. It's | difficult to see what use the numerator of such an approximate fraction | could possibly be, which leads me to question whether or not you understand | the "fraction" format and its (severe) limitations. | | |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hello Niek,
I agree with you. My suggestion is to use Oliver Aberth's algorithm, for example: http://www.sulprobil.com/html/neares...al_number.html Regards, Bernd |
#8
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Thanks all for your input.
I've been working with Niek's amended suggestion (though I had to replace "right" with "left" to get the numerator instead of denominator). This solution has worked for most of my values, but I have a few that are giving me trouble. Most of my fractions are four digit numerators with four digit denominators. For those fractions with five digit numerators, the numerator that is returned is missing the fifth digit. I've tried altering the number of question marks in the formula, but have not had any luck. Any further suggestions? Here's an example: The decimal "1.362162855" is formatted as the fraction "10757/7897" (using custom formatting "####/####"; I've also tried altering this formatting to get the correct numerator return). Using Niek's formula, the numerator returned is "1075". p.s. In response to Stephen, I actually need both numerator and denominator, but once I figure out how to get one I can easily get the other, so for simplicity didn't mention this. I do realize that the fraction is not the exact representation of the decimal, but I think it is close enough for my purposes, which are a little to complicated to go into! "Bernd P" wrote: Hello Niek, I agree with you. My suggestion is to use Oliver Aberth's algorithm, for example: http://www.sulprobil.com/html/neares...al_number.html Regards, Bernd |
#9
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Wed, 12 Dec 2007 07:56:01 -0800, tf wrote:
I have a column of decimal values in an Excel 2003. I have converted the values to fractions using the "format cell" function and would like to extract the numerator values from the fractions. I thought I could do this by converting text to columns with the "/" as a delimiter, such that the numerator and denominator values would be returned in separate columns. The problem is that even though my values show as a fraction, they are still read as decimals by Excel and the text to columns won't work. I've tried using the "paste special" function to replace the decimals with the fraction values, but that did not work either. Any ideas on how to get Excel to read the values as fractions so that I can use the text to columns function; or another method that will allow me to extract the numerator values? (I don't have a common denominator, so I can't use the dollar functions) Thanks! Relatively easy with VBA, if I understand what you want: Here is a UDF that will return either the numerator or the denominator. I'm not sure if you wanted this, but it uses a format based on having the same denominator as in the cell format, but there is no separate digit. In other words, something like 1 3/8 would return 11 for denominator and 8 for numerator. If you would rather have it return a "3", then we can easily make that change. In any event, to enter the UDF, <alt-F11 opens the VBEditor. Ensure your project is highlighted in the Project Explorer Window, then Insert/Module and paste the code below into the window that opens. To use the UDF, enter a formula of the type: =FxPart(cell_ref,[Part] into some cell. Part is optional. If omitted, or "N", the formula will return the numerator. If other than "N", it will return the denominator. =========================================== Option Explicit Function FxPart(c As Range, Optional Part As String = "N") As Double Dim re As Object, mc As Object Dim s As String Dim f As String f = c.NumberFormat f = "#" & Mid(f, InStr(1, f, "/"), 255) s = Application.WorksheetFunction.Text(c.Value, f) Set re = CreateObject("vbscript.regexp") re.Pattern = "(\d+)/(\d+)" If re.test(s) = True Then Set mc = re.Execute(s) If Part = "N" Then FxPart = mc(0).submatches(0) Else FxPart = mc(0).submatches(1) End If End If End Function ==================================== --ron |
#10
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hello,
I still suggest to take my NRN function. The solution 10757/7897 for 1.362162855 has an absolute error of 8.34937E-09 while my function NRN(1.362162855,9999) would return 10505/7712 with an absolute error of just 8.07054E-09. MOD(10505,7712) would give you the numerator. Regards, Bernd |
#11
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Try converting the fractions to text then you can do the Text to columns or
an Edit Replace. This can be achieved by formatting a column as text and then copying and pasting the column either to and from notepad or with the office clipboard on the edit menu (although if the data range is small you need to undo and then paste special as text after clicking the paste icon) "tf" wrote: I have a column of decimal values in an Excel 2003. I have converted the values to fractions using the "format cell" function and would like to extract the numerator values from the fractions. I thought I could do this by converting text to columns with the "/" as a delimiter, such that the numerator and denominator values would be returned in separate columns. The problem is that even though my values show as a fraction, they are still read as decimals by Excel and the text to columns won't work. I've tried using the "paste special" function to replace the decimals with the fraction values, but that did not work either. Any ideas on how to get Excel to read the values as fractions so that I can use the text to columns function; or another method that will allow me to extract the numerator values? (I don't have a common denominator, so I can't use the dollar functions) Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Showing numerator in graph | Excel Discussion (Misc queries) | |||
How do I change fraction to fraction characters? | Excel Worksheet Functions | |||
fraction | Excel Discussion (Misc queries) | |||
Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) | |||
How do I create a spread sheet w/a numerator/denomenator? | Excel Discussion (Misc queries) |