If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
All arithmetic functions return Zero as result
I have a column of 2960 numbers (amplitudes of an earthquake wave). I want to
find the max and min of them, max and min functions return zero. I doubted maybe the data are text, but multiplication of a single cell in a multiplier (let say 2), returns correct result. Sum on all data doesn't work as well (returns zero) but sum of two cells works correctly. Also x-y plot returns zero value for Y. I checked the format of data in cell command. the format is number. Also I checked Option to see if automatic calculation is turned off, but it on. Really confused. This is the first time to face such a malfunction. What else could be considered to be checked? -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan |
#2
|
|||
|
|||
All arithmetic functions return Zero as result
Problem is with ":" as span indicator. My Excel doesn't accept ":" as in
Max(A1:A2960). Where should look in Excel to fix this problem -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: I have a column of 2960 numbers (amplitudes of an earthquake wave). I want to find the max and min of them, max and min functions return zero. I doubted maybe the data are text, but multiplication of a single cell in a multiplier (let say 2), returns correct result. Sum on all data doesn't work as well (returns zero) but sum of two cells works correctly. Also x-y plot returns zero value for Y. I checked the format of data in cell command. the format is number. Also I checked Option to see if automatic calculation is turned off, but it on. Really confused. This is the first time to face such a malfunction. What else could be considered to be checked? -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan |
#3
|
|||
|
|||
All arithmetic functions return Zero as result
What is indicator for range? I thought it is ":", but it doesn't work for me.
-- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: Problem is with ":" as span indicator. My Excel doesn't accept ":" as in Max(A1:A2960). Where should look in Excel to fix this problem -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: I have a column of 2960 numbers (amplitudes of an earthquake wave). I want to find the max and min of them, max and min functions return zero. I doubted maybe the data are text, but multiplication of a single cell in a multiplier (let say 2), returns correct result. Sum on all data doesn't work as well (returns zero) but sum of two cells works correctly. Also x-y plot returns zero value for Y. I checked the format of data in cell command. the format is number. Also I checked Option to see if automatic calculation is turned off, but it on. Really confused. This is the first time to face such a malfunction. What else could be considered to be checked? -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan |
#4
|
|||
|
|||
All arithmetic functions return Zero as result
range indicator ":" works fine in other workbooks. So aparently the problem
is not range indicator. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: What is indicator for range? I thought it is ":", but it doesn't work for me. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: Problem is with ":" as span indicator. My Excel doesn't accept ":" as in Max(A1:A2960). Where should look in Excel to fix this problem -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: I have a column of 2960 numbers (amplitudes of an earthquake wave). I want to find the max and min of them, max and min functions return zero. I doubted maybe the data are text, but multiplication of a single cell in a multiplier (let say 2), returns correct result. Sum on all data doesn't work as well (returns zero) but sum of two cells works correctly. Also x-y plot returns zero value for Y. I checked the format of data in cell command. the format is number. Also I checked Option to see if automatic calculation is turned off, but it on. Really confused. This is the first time to face such a malfunction. What else could be considered to be checked? -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan |
#5
|
|||
|
|||
All arithmetic functions return Zero as result
range indicator works fine in other worksheets. so the problem may not be
related to range indicator alone. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: What is indicator for range? I thought it is ":", but it doesn't work for me. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: Problem is with ":" as span indicator. My Excel doesn't accept ":" as in Max(A1:A2960). Where should look in Excel to fix this problem -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: I have a column of 2960 numbers (amplitudes of an earthquake wave). I want to find the max and min of them, max and min functions return zero. I doubted maybe the data are text, but multiplication of a single cell in a multiplier (let say 2), returns correct result. Sum on all data doesn't work as well (returns zero) but sum of two cells works correctly. Also x-y plot returns zero value for Y. I checked the format of data in cell command. the format is number. Also I checked Option to see if automatic calculation is turned off, but it on. Really confused. This is the first time to face such a malfunction. What else could be considered to be checked? -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan |
#6
|
|||
|
|||
All arithmetic functions return Zero as result
Problem is with the format of numbers. THeir format is scientific. I cant
change the firmat so I multiplied each number to 1 and format changed to general. Now all functions work fine. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: What is indicator for range? I thought it is ":", but it doesn't work for me. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: Problem is with ":" as span indicator. My Excel doesn't accept ":" as in Max(A1:A2960). Where should look in Excel to fix this problem -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Khoshravan" wrote: I have a column of 2960 numbers (amplitudes of an earthquake wave). I want to find the max and min of them, max and min functions return zero. I doubted maybe the data are text, but multiplication of a single cell in a multiplier (let say 2), returns correct result. Sum on all data doesn't work as well (returns zero) but sum of two cells works correctly. Also x-y plot returns zero value for Y. I checked the format of data in cell command. the format is number. Also I checked Option to see if automatic calculation is turned off, but it on. Really confused. This is the first time to face such a malfunction. What else could be considered to be checked? -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan |
#7
|
|||
|
|||
All arithmetic functions return Zero as result
One common reason formulas don't work is if the "numbers" or "dates" are text
that looks like numbers or dates. You can test this possibility by trying to change the format of the cell. If this works, then the cell contains a number or date/time serial number. If it doesn't, then you need to convert them. Changing the format from text to a number format won't change the data--it will still be text as you can see from a formula like: =ISTEXT(A1) returns TRUE if value is text (even if cell is formatted as a number and the contents look like a number) =ISNUMBER(A1) returns TRUE if value really is a number (even if cell is formatted to display text like Friday, January 1, 2005) A few easy ways to convert them a 1) Copy a blank cell 2) Select the cells to convert 3) Edit...Paste Special...Add 4) Format the cells as desired A macro to do this is quite simple. It goes in a regular module sheet. To use it, select the cells to be converted, then run the macro. The macro will ask you to point to a cell with the desired date/time or number format. Sub TextToNumbers() Dim cel As Range, rg As Range On Error Resume Next Set rg = Selection Set cel = Application.InputBox("Please pick a cell that has the desired number format", Type:=8) If Not cel Is Nothing Then rg.NumberFormat = cel.NumberFormat rg.Value = rg.Value End If On Error GoTo 0 End Sub Another way to convert text to numbers uses the Data...Text to Columns menu item. One benefit of this approach is that you can specify the format of dates (such as if they were exported with leading zeros as mmddyy). You can also throw away part of the data (if you want). 1) Select the cells to be converted 2) Open the Data...Text to Columns menu item 3) In the first step of the wizard, choose "Delimited" ("Fixed if you want to get rid of certain information) 4) Click "Next" twice 5) In the third step of the wizard, you can specify whether a column contains text or dates. Choose General if the column contains numbers. If you choose Dates, specify the format in the dropdown to the right. 6) Click "Finish" -- Brad "Khoshravan" wrote: I have a column of 2960 numbers (amplitudes of an earthquake wave). I want to find the max and min of them, max and min functions return zero. I doubted maybe the data are text, but multiplication of a single cell in a multiplier (let say 2), returns correct result. Sum on all data doesn't work as well (returns zero) but sum of two cells works correctly. Also x-y plot returns zero value for Y. I checked the format of data in cell command. the format is number. Also I checked Option to see if automatic calculation is turned off, but it on. Really confused. This is the first time to face such a malfunction. What else could be considered to be checked? -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan |
#8
|
|||
|
|||
All arithmetic functions return Zero as result
You are right. They were text not numbers. But interestingly, when I multiply
them, the result is correct. So I solved my problem in this way: I made another column based on the text column multiplied by 1. The resulting column is no number not text?!?. Although I cant understand the logic behind this solution, but any way it solved my problem. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "byundt" wrote: One common reason formulas don't work is if the "numbers" or "dates" are text that looks like numbers or dates. You can test this possibility by trying to change the format of the cell. If this works, then the cell contains a number or date/time serial number. If it doesn't, then you need to convert them. Changing the format from text to a number format won't change the data--it will still be text as you can see from a formula like: =ISTEXT(A1) returns TRUE if value is text (even if cell is formatted as a number and the contents look like a number) =ISNUMBER(A1) returns TRUE if value really is a number (even if cell is formatted to display text like Friday, January 1, 2005) A few easy ways to convert them a 1) Copy a blank cell 2) Select the cells to convert 3) Edit...Paste Special...Add 4) Format the cells as desired A macro to do this is quite simple. It goes in a regular module sheet. To use it, select the cells to be converted, then run the macro. The macro will ask you to point to a cell with the desired date/time or number format. Sub TextToNumbers() Dim cel As Range, rg As Range On Error Resume Next Set rg = Selection Set cel = Application.InputBox("Please pick a cell that has the desired number format", Type:=8) If Not cel Is Nothing Then rg.NumberFormat = cel.NumberFormat rg.Value = rg.Value End If On Error GoTo 0 End Sub Another way to convert text to numbers uses the Data...Text to Columns menu item. One benefit of this approach is that you can specify the format of dates (such as if they were exported with leading zeros as mmddyy). You can also throw away part of the data (if you want). 1) Select the cells to be converted 2) Open the Data...Text to Columns menu item 3) In the first step of the wizard, choose "Delimited" ("Fixed if you want to get rid of certain information) 4) Click "Next" twice 5) In the third step of the wizard, you can specify whether a column contains text or dates. Choose General if the column contains numbers. If you choose Dates, specify the format in the dropdown to the right. 6) Click "Finish" -- Brad "Khoshravan" wrote: I have a column of 2960 numbers (amplitudes of an earthquake wave). I want to find the max and min of them, max and min functions return zero. I doubted maybe the data are text, but multiplication of a single cell in a multiplier (let say 2), returns correct result. Sum on all data doesn't work as well (returns zero) but sum of two cells works correctly. Also x-y plot returns zero value for Y. I checked the format of data in cell command. the format is number. Also I checked Option to see if automatic calculation is turned off, but it on. Really confused. This is the first time to face such a malfunction. What else could be considered to be checked? -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan |
#9
|
|||
|
|||
All arithmetic functions return Zero as result
Math operators (+-*/) will coerce non-numeric expressions into numbers, if
possible. Math functions (max, min, etc) will simply ignore non-numeric expressions. Your solution accomplised (with a helper column, what Brad's would have accomplished without a helper column--it used a math operator to do nothing except coerce from text to a number. Jerry "Khoshravan" wrote: You are right. They were text not numbers. But interestingly, when I multiply them, the result is correct. So I solved my problem in this way: I made another column based on the text column multiplied by 1. The resulting column is no number not text?!?. Although I cant understand the logic behind this solution, but any way it solved my problem. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "byundt" wrote: One common reason formulas don't work is if the "numbers" or "dates" are text that looks like numbers or dates. You can test this possibility by trying to change the format of the cell. If this works, then the cell contains a number or date/time serial number. If it doesn't, then you need to convert them. Changing the format from text to a number format won't change the data--it will still be text as you can see from a formula like: =ISTEXT(A1) returns TRUE if value is text (even if cell is formatted as a number and the contents look like a number) =ISNUMBER(A1) returns TRUE if value really is a number (even if cell is formatted to display text like Friday, January 1, 2005) A few easy ways to convert them a 1) Copy a blank cell 2) Select the cells to convert 3) Edit...Paste Special...Add 4) Format the cells as desired A macro to do this is quite simple. It goes in a regular module sheet. To use it, select the cells to be converted, then run the macro. The macro will ask you to point to a cell with the desired date/time or number format. Sub TextToNumbers() Dim cel As Range, rg As Range On Error Resume Next Set rg = Selection Set cel = Application.InputBox("Please pick a cell that has the desired number format", Type:=8) If Not cel Is Nothing Then rg.NumberFormat = cel.NumberFormat rg.Value = rg.Value End If On Error GoTo 0 End Sub Another way to convert text to numbers uses the Data...Text to Columns menu item. One benefit of this approach is that you can specify the format of dates (such as if they were exported with leading zeros as mmddyy). You can also throw away part of the data (if you want). 1) Select the cells to be converted 2) Open the Data...Text to Columns menu item 3) In the first step of the wizard, choose "Delimited" ("Fixed if you want to get rid of certain information) 4) Click "Next" twice 5) In the third step of the wizard, you can specify whether a column contains text or dates. Choose General if the column contains numbers. If you choose Dates, specify the format in the dropdown to the right. 6) Click "Finish" -- Brad "Khoshravan" wrote: I have a column of 2960 numbers (amplitudes of an earthquake wave). I want to find the max and min of them, max and min functions return zero. I doubted maybe the data are text, but multiplication of a single cell in a multiplier (let say 2), returns correct result. Sum on all data doesn't work as well (returns zero) but sum of two cells works correctly. Also x-y plot returns zero value for Y. I checked the format of data in cell command. the format is number. Also I checked Option to see if automatic calculation is turned off, but it on. Really confused. This is the first time to face such a malfunction. What else could be considered to be checked? -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan |
#10
|
|||
|
|||
All arithmetic functions return Zero as result
Dear Jerry
Thanks for your additional informative information. I applied Brad's method after I get your email. His method adds a zero (+) to end of the number, which is the method you explained. Although it is a smart way. His next solution seams not to be proper for me as it deals with dates but I have no date. Also I think another way to convert text to number is "value" command. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "Jerry W. Lewis" wrote: Math operators (+-*/) will coerce non-numeric expressions into numbers, if possible. Math functions (max, min, etc) will simply ignore non-numeric expressions. Your solution accomplised (with a helper column, what Brad's would have accomplished without a helper column--it used a math operator to do nothing except coerce from text to a number. Jerry "Khoshravan" wrote: You are right. They were text not numbers. But interestingly, when I multiply them, the result is correct. So I solved my problem in this way: I made another column based on the text column multiplied by 1. The resulting column is no number not text?!?. Although I cant understand the logic behind this solution, but any way it solved my problem. -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan "byundt" wrote: One common reason formulas don't work is if the "numbers" or "dates" are text that looks like numbers or dates. You can test this possibility by trying to change the format of the cell. If this works, then the cell contains a number or date/time serial number. If it doesn't, then you need to convert them. Changing the format from text to a number format won't change the data--it will still be text as you can see from a formula like: =ISTEXT(A1) returns TRUE if value is text (even if cell is formatted as a number and the contents look like a number) =ISNUMBER(A1) returns TRUE if value really is a number (even if cell is formatted to display text like Friday, January 1, 2005) A few easy ways to convert them a 1) Copy a blank cell 2) Select the cells to convert 3) Edit...Paste Special...Add 4) Format the cells as desired A macro to do this is quite simple. It goes in a regular module sheet. To use it, select the cells to be converted, then run the macro. The macro will ask you to point to a cell with the desired date/time or number format. Sub TextToNumbers() Dim cel As Range, rg As Range On Error Resume Next Set rg = Selection Set cel = Application.InputBox("Please pick a cell that has the desired number format", Type:=8) If Not cel Is Nothing Then rg.NumberFormat = cel.NumberFormat rg.Value = rg.Value End If On Error GoTo 0 End Sub Another way to convert text to numbers uses the Data...Text to Columns menu item. One benefit of this approach is that you can specify the format of dates (such as if they were exported with leading zeros as mmddyy). You can also throw away part of the data (if you want). 1) Select the cells to be converted 2) Open the Data...Text to Columns menu item 3) In the first step of the wizard, choose "Delimited" ("Fixed if you want to get rid of certain information) 4) Click "Next" twice 5) In the third step of the wizard, you can specify whether a column contains text or dates. Choose General if the column contains numbers. If you choose Dates, specify the format in the dropdown to the right. 6) Click "Finish" -- Brad "Khoshravan" wrote: I have a column of 2960 numbers (amplitudes of an earthquake wave). I want to find the max and min of them, max and min functions return zero. I doubted maybe the data are text, but multiplication of a single cell in a multiplier (let say 2), returns correct result. Sum on all data doesn't work as well (returns zero) but sum of two cells works correctly. Also x-y plot returns zero value for Y. I checked the format of data in cell command. the format is number. Also I checked Option to see if automatic calculation is turned off, but it on. Really confused. This is the first time to face such a malfunction. What else could be considered to be checked? -- Rasoul Khoshravan Azar Civil Engineer, Osaka, Japan |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Currency to Text | mytipi | Worksheet Functions | 1 | February 21st, 2006 11:43 PM |
Number format | Mani K | General Discussion | 2 | December 28th, 2005 11:01 AM |
spell number | JAWAD CHOHAN | Worksheet Functions | 1 | December 12th, 2005 05:58 PM |
excel | melissa | Worksheet Functions | 2 | August 19th, 2005 09:46 PM |
convert value in word. For Exampe Rs.115.00 convert into word as . | Shakti | General Discussion | 1 | May 10th, 2005 12:00 PM |