All arithmetic functions return Zero as result

[Khoshravan]

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?

 

[Khoshravan]

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

 

[Khoshravan]

What is indicator for range? I thought it is ":", but it doesn't work for me.

 

[Khoshravan]

range indicator ":" works fine in other workbooks. So aparently the problem
is not range indicator.

 

[Khoshravan]

range indicator works fine in other worksheets. so the problem may not be
related to range indicator alone.

 

[Khoshravan]

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.

 

[byundt]

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 are:
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"

 

[Khoshravan]

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.

 

[Jerry W. Lewis]

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]

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.