Function Reliability???????

L

lsmft

I am using a MS Excel workbook presently that is giving me fits over the
"Funtions" either not working at all or working incorrectly. I have
checked the formating of the cells in use and they are all formatted
the same, for "numbers" no decimals. I have re-started my computer,
which didn't seem to help. I am presently trying to use the "countif"
function in a column, to count the days of the month that have been
reached. The "countif" function doesn't count all of the cells that it
should. I have just learned that I can go back and delete each cell
that is to be counted and then re-enter the number, and the "countif"
functions starts working again. Is there something that I have done
wrong in the beginning to cause this? I am using MS Excel 2000 with
Windows 98SE.
 
N

Niek Otten

Most common cause is that cells were formatted as text BEFORE the numbers
were entered. Formatting afterwards has no effect; you have to re- enter the
data as numbers after reformatting.
One way to do that for a large number of entries:
Format an empty cell as Number. Enter the number 1. Edit>Copy. Select your
"numbers". Edit>Paste Special, check Multiply.
 
N

Nick Hodge

Sometimes Excel shows numbers which it is actually seeing as text. Try
copying an empty cell and then highlight your dates and select Edit>Paste
Special+Add. This should force them into action. You may end up with
strange numbers. This is how excel stores dates. (Days since 1st Jan 1900),
just format them as dates and all should be well

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
 
A

Arvi Laanemets

Hi

When you have some number or date entered into cell, formatted as text, and
you change the format of cell to number or to date or to General afterwards,
then Excel interprets such entries as text. To convert them really, you have
to
re-enter the values (enter values anew, or activate edit mode for cell,
double-clicking it or pressing F2, and pressing then Enter)
, or enter a value 1 into some free cell, copy it, and then select the
range with numeric strings, and PasteSpecial>Multiply


Arvi Laanemets
 
G

Gary''s Student

One possibility is that Excel considered your numbers to be text. One way to
test for this is to put 1 in an un-used cell, copy the cell and then
paste/special with multiply checked over your suspected range.
 
L

lsmft

As a matter of fact: They were all formatted originally as "Text". I did
that so that I could leave the "Tools-Options-Edit-Fixed Decimals" in
place, and so I wouldn't need to put a period after each number entered
to keep it from reducing to a zero or whatever.
What you all are saying explains why when I deleted data in a cell and
re-entered it as a number format, the "countif" function began to work.
Thanks much to you all from a definite "learner".
 
Top