Excel formula not working

[James]

Hello and thanks in advance.....

I have a user who has crated a spreadsheet & the formula has been entered
correctly but is not showing the true results.
The formula is;

=max(A1:A162)

There are enteries in column A and I am expecting the figure to show as 350.
This is actually showing as 100 - a figure which is also in column A
I have re inserted the formula - this does not work. I have also put the
formula in a completely different cell but again this does not work.

If I over type the data in all the cells the formula works however I cannot
tell the user to retype thousands of enteries.

I have checked the formatting of the spreadsheet, this all looks ok. Also
have checked the tools & options, this is all ok (calculation is set to
automatic).

Any ideas why this happened but more importantly is there a easy fix to
correct this?

Thanks in advance

James

 

[Lady Layla]

The formula is probably not giving you the correct answer as you see it because
the "numbers" you are looking at may not all be entered as numbers but as text.
This can be easily fixed by entering 0 in an empty cell, Edit | Copy, select the
range where these numbers are, Edit | Paste Special Add

HTH


: Hello and thanks in advance.....
:
: I have a user who has crated a spreadsheet & the formula has been entered
: correctly but is not showing the true results.
: The formula is;
:
: =max(A1:A162)
:
: There are enteries in column A and I am expecting the figure to show as 350.
: This is actually showing as 100 - a figure which is also in column A
: I have re inserted the formula - this does not work. I have also put the
: formula in a completely different cell but again this does not work.
:
: If I over type the data in all the cells the formula works however I cannot
: tell the user to retype thousands of enteries.
:
: I have checked the formatting of the spreadsheet, this all looks ok. Also
: have checked the tools & options, this is all ok (calculation is set to
: automatic).
:
: Any ideas why this happened but more importantly is there a easy fix to
: correct this?
:
: Thanks in advance
:
: James
:

 

[James]

Lady Layla,

Thanks for the advice but this did not work, anything else I can try?

 

[Gord Dibben]

James

If Layla's steps do not work, perhaps you have a non-breaking space in the
cell.

Where does the data come from. A web site or similar?

Try edit>replace

what: Hold ALT key and type 0160(on the numpad)

with: nothing


Gord Dibben Excel MVP