Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
I copy a page from the Internet into my spread sheet. It's a table
and everything transposes OK, except fractions like 9/5. In my spreadsheet it reads 39,330. Only the fractions do this. Here are some others: 9/2 = 4.5 = 39,327 7/2 = 3.5 = 39,265 5/2 = 2.5 = 39,204 9/5 = 1.8 = 39,330 8/5 = 1.6 = 39,299 3/2 = 1.5 = 7/5 = 1.4 = 6/5 = 1.2 = 4/5 = 0.8 = 3/5 = 0.6 = 0 = 0.4 = I don't have them all yet, but I'm sure there is a mathematical corelation. As soon as I see the rest of them I'll have them. The frist column is how they look when I copy them. The second column is how I want them to look, and the third column is how they show up. Anyway, when I paste them into my spreadsheet how can I make them show up as 4.5, 3.5, 2.5, etc? Thank you |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
For those fractions where the numbers match your system's local settings for
dates (minus the year), that is, the number in the month's position is between 1 and 12 and the day's position is between 1 and 28 and 31 (depending on the number of days for the number in the month's position), Excel thinks you are entering a date (minus the year) and "helps" you out by converting the entry into a date for the current year. By the way, if the fraction cannot be interpreted as a date, the entry will be entered as text (for example, 14/5 will not convert to 2.8). To get your fractions as numerical values, precede them with an equal sign (making them into a formula) and you should get the results you are looking for. Rick "Peter" wrote in message ... I copy a page from the Internet into my spread sheet. It's a table and everything transposes OK, except fractions like 9/5. In my spreadsheet it reads 39,330. Only the fractions do this. Here are some others: 9/2 = 4.5 = 39,327 7/2 = 3.5 = 39,265 5/2 = 2.5 = 39,204 9/5 = 1.8 = 39,330 8/5 = 1.6 = 39,299 3/2 = 1.5 = 7/5 = 1.4 = 6/5 = 1.2 = 4/5 = 0.8 = 3/5 = 0.6 = 0 = 0.4 = I don't have them all yet, but I'm sure there is a mathematical corelation. As soon as I see the rest of them I'll have them. The frist column is how they look when I copy them. The second column is how I want them to look, and the third column is how they show up. Anyway, when I paste them into my spreadsheet how can I make them show up as 4.5, 3.5, 2.5, etc? Thank you |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
Hi
The problem is that Excel is seeing these as dates. 9/2 is 02 Sep and it is showing as the serial number of the date 39327 It would need to be =9/2 for Excel to interpret it in the way that you wish. If you made the column a Text column, before copying data to it, then it should come through as '9/2 and display as 9/2. You can convert this to 4.5 if you do the following. Suppose the column with the data is column C, and that column D is blank. InsertNameDefine Name Evaluate Refers to =EVALUATE($C1) In D1 enter =Evaluate and copy down. Then copy column Dplace cursor on C1Paste SpecialValues to fix the data. Column D can then be deleted -- Regards Roger Govier "Peter" wrote in message ... I copy a page from the Internet into my spread sheet. It's a table and everything transposes OK, except fractions like 9/5. In my spreadsheet it reads 39,330. Only the fractions do this. Here are some others: 9/2 = 4.5 = 39,327 7/2 = 3.5 = 39,265 5/2 = 2.5 = 39,204 9/5 = 1.8 = 39,330 8/5 = 1.6 = 39,299 3/2 = 1.5 = 7/5 = 1.4 = 6/5 = 1.2 = 4/5 = 0.8 = 3/5 = 0.6 = 0 = 0.4 = I don't have them all yet, but I'm sure there is a mathematical corelation. As soon as I see the rest of them I'll have them. The frist column is how they look when I copy them. The second column is how I want them to look, and the third column is how they show up. Anyway, when I paste them into my spreadsheet how can I make them show up as 4.5, 3.5, 2.5, etc? Thank you |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
Hi, and thanks.
I'm using Excel 2000. I didn't see an "evaluate function." I don't think I quite followed your instructions, or I did follow them and it didn't work. Either way, I'm a little confused. There are only about 11 fractions I need to be concerned with. I'm wondering if I created a table and used Vlookup to convert the long number string to the fraction I need--would work? I'm sure there is an easy solution. I appreciate the advice. On Sun, 9 Dec 2007 09:19:16 -0000, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi The problem is that Excel is seeing these as dates. 9/2 is 02 Sep and it is showing as the serial number of the date 39327 It would need to be =9/2 for Excel to interpret it in the way that you wish. If you made the column a Text column, before copying data to it, then it should come through as '9/2 and display as 9/2. You can convert this to 4.5 if you do the following. Suppose the column with the data is column C, and that column D is blank. InsertNameDefine Name Evaluate Refers to =EVALUATE($C1) In D1 enter =Evaluate and copy down. Then copy column Dplace cursor on C1Paste SpecialValues to fix the data. Column D can then be deleted |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
Here is the full answer. Excel is seening 9/5 as the date 9/5/2007. The
39,000 number is trhe numeric value that excel uses for the date. It is the count of the number of days that have past since Jan 1, 1900 with Jan 1, 1900 being 1. The easy solution is to format the column as a fraction before you do the paste. I think you need to do a pastespecial and paste value only so none of the formating in the text changes the formating in excel. "Peter" wrote: Hi, and thanks. I'm using Excel 2000. I didn't see an "evaluate function." I don't think I quite followed your instructions, or I did follow them and it didn't work. Either way, I'm a little confused. There are only about 11 fractions I need to be concerned with. I'm wondering if I created a table and used Vlookup to convert the long number string to the fraction I need--would work? I'm sure there is an easy solution. I appreciate the advice. On Sun, 9 Dec 2007 09:19:16 -0000, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi The problem is that Excel is seeing these as dates. 9/2 is 02 Sep and it is showing as the serial number of the date 39327 It would need to be =9/2 for Excel to interpret it in the way that you wish. If you made the column a Text column, before copying data to it, then it should come through as '9/2 and display as 9/2. You can convert this to 4.5 if you do the following. Suppose the column with the data is column C, and that column D is blank. InsertNameDefine Name Evaluate Refers to =EVALUATE($C1) In D1 enter =Evaluate and copy down. Then copy column Dplace cursor on C1Paste SpecialValues to fix the data. Column D can then be deleted |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
Hi
You won't see an Evaluation function. That is why I said to use InsertNameDefine to create the the function. It does not matter what name you give the defined name, it is the Refers to part that is important. Try again InsertNameDefine Name Evaluate Refers to =Evaluate($C1) Change the column reference to whatever column is holding the fractions. In column D (or anywhere else) type =Evaluate -- Regards Roger Govier "Peter" wrote in message ... Hi, and thanks. I'm using Excel 2000. I didn't see an "evaluate function." I don't think I quite followed your instructions, or I did follow them and it didn't work. Either way, I'm a little confused. There are only about 11 fractions I need to be concerned with. I'm wondering if I created a table and used Vlookup to convert the long number string to the fraction I need--would work? I'm sure there is an easy solution. I appreciate the advice. On Sun, 9 Dec 2007 09:19:16 -0000, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi The problem is that Excel is seeing these as dates. 9/2 is 02 Sep and it is showing as the serial number of the date 39327 It would need to be =9/2 for Excel to interpret it in the way that you wish. If you made the column a Text column, before copying data to it, then it should come through as '9/2 and display as 9/2. You can convert this to 4.5 if you do the following. Suppose the column with the data is column C, and that column D is blank. InsertNameDefine Name Evaluate Refers to =EVALUATE($C1) In D1 enter =Evaluate and copy down. Then copy column Dplace cursor on C1Paste SpecialValues to fix the data. Column D can then be deleted |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
On Sun, 9 Dec 2007 04:46:00 -0800, Joel
wrote: Here is the full answer. Excel is seening 9/5 as the date 9/5/2007. The 39,000 number is trhe numeric value that excel uses for the date. It is the count of the number of days that have past since Jan 1, 1900 with Jan 1, 1900 being 1. The easy solution is to format the column as a fraction before you do the paste. I think you need to do a pastespecial and paste value only so none of the formating in the text changes the formating in excel. If you made the column a Text column, before copying data to it, then it should come through as '9/2 and display as 9/2. You can convert this to 4.5 if you do the following. Suppose the column with the data is column C, and that column D is blank. InsertNameDefine Name Evaluate Refers to =EVALUATE($C1) In D1 enter =Evaluate and copy down. Then copy column Dplace cursor on C1Paste SpecialValues to fix the data. Column D can then be deleted I tried both of these solutions over and over to no avail. When I copy and paste from the web site my only options under "paste special" are text, unicode text, and html. I use text and paste it into a text formatted cell. I now manually convert the numbers 39,237, etc. to their equvalent fractions--it's a pain. I'm tryng to understand the name define thing, but you're losing me. I created a new sheet and did exactly as you said, but I couldn't get it to work. There's got to be an easy way to solve this. Thanks for all the help. "Peter" wrote: Hi, and thanks. I'm using Excel 2000. I didn't see an "evaluate function." I don't think I quite followed your instructions, or I did follow them and it didn't work. Either way, I'm a little confused. There are only about 11 fractions I need to be concerned with. I'm wondering if I created a table and used Vlookup to convert the long number string to the fraction I need--would work? I'm sure there is an easy solution. I appreciate the advice. On Sun, 9 Dec 2007 09:19:16 -0000, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi The problem is that Excel is seeing these as dates. 9/2 is 02 Sep and it is showing as the serial number of the date 39327 It would need to be =9/2 for Excel to interpret it in the way that you wish. If you made the column a Text column, before copying data to it, then it should come through as '9/2 and display as 9/2. You can convert this to 4.5 if you do the following. Suppose the column with the data is column C, and that column D is blank. InsertNameDefine Name Evaluate Refers to =EVALUATE($C1) In D1 enter =Evaluate and copy down. Then copy column Dplace cursor on C1Paste SpecialValues to fix the data. Column D can then be deleted |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
On Dec 9, 4:06 am, Peter wrote:
There are only about 11 fractions I need to be concerned with. I'm wondering if I created a table and used Vlookup to convert the long number string to the fraction I need--would work? I must be missing something. If there are only 11 fractions, why not type them in manually, preceding each with the equal sign? That is, turn them into formulas as follows: =9/2 =7/2 =5/2 =9/5 ....etc... If you would like a table that shows the fraction in one column and its decimal value in another column, enter the first column as follows: '9/2 '7/2 '5/2 '9/5 ....etc... Note the apostrophe (') in front of each text. Caveat emptor: If you have =9/5 in A4 and =3/5 in A10, don't expect 3*A10 to equal A4. But ROUND(3*A10,1) does equal A4 in this case. PS: In your first posting, the last "fraction" is "0". How could that evaluate to 0.4!? Perhaps you meant to write 2/5. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
Hi,
When I paste the data into my spreadsheet I want it to just convert and be done with it. The way things are now I have to manually change the fractions that are in error, and sometimes I forget and that causes a problem. Here is how the table should look. I'm missing two, but they're not important. 9/2 = 4.5 = 39,327 7/2 = 3.5 = 39,265 5/2 = 2.5 = 39,204 9/5 = 1.8 = 39,330 8/5 = 1.6 = 39,299 3/2 = 1.5 = 39,143 7/5 = 1.4 = 39,268 6/5 = 1.2 = 39,238 4/5 = 0.8 = 39,177 3/5 = 0.6 = 39,146 1/2 = 0.5 = 38,084 2/5 0.4 1/5 = 0.2 = 1/9 = .11 39,019 On Sun, 16 Dec 2007 07:48:57 -0800 (PST), joeu2004 wrote: On Dec 9, 4:06 am, Peter wrote: There are only about 11 fractions I need to be concerned with. I'm wondering if I created a table and used Vlookup to convert the long number string to the fraction I need--would work? I must be missing something. If there are only 11 fractions, why not type them in manually, preceding each with the equal sign? That is, turn them into formulas as follows: =9/2 =7/2 =5/2 =9/5 ...etc... If you would like a table that shows the fraction in one column and its decimal value in another column, enter the first column as follows: '9/2 '7/2 '5/2 '9/5 ...etc... Note the apostrophe (') in front of each text. Caveat emptor: If you have =9/5 in A4 and =3/5 in A10, don't expect 3*A10 to equal A4. But ROUND(3*A10,1) does equal A4 in this case. PS: In your first posting, the last "fraction" is "0". How could that evaluate to 0.4!? Perhaps you meant to write 2/5. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
Hi Peter
Mail me a copy of your sheet direct. Send to roger at technology4u dot co dot uk Do the obvious with the dot and at. -- Regards Roger Govier "Peter" wrote in message ... On Sun, 9 Dec 2007 04:46:00 -0800, Joel wrote: Here is the full answer. Excel is seening 9/5 as the date 9/5/2007. The 39,000 number is trhe numeric value that excel uses for the date. It is the count of the number of days that have past since Jan 1, 1900 with Jan 1, 1900 being 1. The easy solution is to format the column as a fraction before you do the paste. I think you need to do a pastespecial and paste value only so none of the formating in the text changes the formating in excel. If you made the column a Text column, before copying data to it, then it should come through as '9/2 and display as 9/2. You can convert this to 4.5 if you do the following. Suppose the column with the data is column C, and that column D is blank. InsertNameDefine Name Evaluate Refers to =EVALUATE($C1) In D1 enter =Evaluate and copy down. Then copy column Dplace cursor on C1Paste SpecialValues to fix the data. Column D can then be deleted I tried both of these solutions over and over to no avail. When I copy and paste from the web site my only options under "paste special" are text, unicode text, and html. I use text and paste it into a text formatted cell. I now manually convert the numbers 39,237, etc. to their equvalent fractions--it's a pain. I'm tryng to understand the name define thing, but you're losing me. I created a new sheet and did exactly as you said, but I couldn't get it to work. There's got to be an easy way to solve this. Thanks for all the help. "Peter" wrote: Hi, and thanks. I'm using Excel 2000. I didn't see an "evaluate function." I don't think I quite followed your instructions, or I did follow them and it didn't work. Either way, I'm a little confused. There are only about 11 fractions I need to be concerned with. I'm wondering if I created a table and used Vlookup to convert the long number string to the fraction I need--would work? I'm sure there is an easy solution. I appreciate the advice. On Sun, 9 Dec 2007 09:19:16 -0000, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi The problem is that Excel is seeing these as dates. 9/2 is 02 Sep and it is showing as the serial number of the date 39327 It would need to be =9/2 for Excel to interpret it in the way that you wish. If you made the column a Text column, before copying data to it, then it should come through as '9/2 and display as 9/2. You can convert this to 4.5 if you do the following. Suppose the column with the data is column C, and that column D is blank. InsertNameDefine Name Evaluate Refers to =EVALUATE($C1) In D1 enter =Evaluate and copy down. Then copy column Dplace cursor on C1Paste SpecialValues to fix the data. Column D can then be deleted |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
39,330 = 9/5
On Dec 16, 8:42 am, Peter wrote:
When I paste the data into my spreadsheet I want it to just convert and be done with it. I understand. But you said "[t]here are only about 11 fractions I need to be concerned with". I would simply dispense with cut-and- paste in that case. But perhaps you meant to say: you are doing this often, and there are 11 __different__ fractions each time. (And I guess you meant to say 14 factions, not 11.) Note: If you are doing it often, but it is always the same 14 fractions, I would simply set up a spreadsheet with the appropriate "=fraction" formulas, and cut-and-pasteSpecial-value from there. I use Excel 2003. If I wanted to cut-and-paste a table from a web page, I would use either New Web Query or Import Data, both under DataImport External Data. If either or both are available in your version of Excel and you need help using them, post back for instructions. To answer your question about VLOOKUP, I would populate the table manually, and I would use vlookup(...,false) to do the lookup. If VLOOKUP returns #N/A, that's your clue that you need to update the table with one or more additional fractions. Suppose you put your table into Sheet2!A1:B14. Then if A1 (on any other sheet) contains the first of the fractions cut-and-pasted from a web age, B1 (on that sheet) might have the formula: =vlookup(A1, Sheet2!A1:B14, 2, false) If you want to eliminate the VLOOKUP formulas, you could copy them, then use Paste SpecialValue to overwrite the text in column A. (I put the lookup table on another sheet simply to get it out of the way. Of course, you might choose to keep everything on the same sheet.) Note: I have had problems with cut-and-paste of text from web pages. I have had to resort to pasting them into Notepad, saving the file, then using DataImport External DataImport Data to put the desired text into Excel. Hence my suggestion above to use one of the Import External Data features. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|