Copied data not treated same way as typed in data - can anyone help?

P

Poppy Gerard

Hi there. I am new to this newsgroup - but I wonder if someone might
be able to help.

I have an Excel spreadsheet (MS Excel'97 .xls Workbook) which contains
a whole load of data. I am trying to use the FREQUENCY command [e.g.
=FREQUENCY($N$7:$N$2000,C60:C61)] to summarise certain columns of data
- to tell me the number of times that certain codes appear.

I have used the FREQUENCY command before in another spreadsheet
without any problem.

In my current spreadsheet I have setup the FREQUENCY commands in
exactly the same way, but all the output fields appear as zero.
Interestingly though when I manually re-type the source data in the
original columns - then the FREQUENCY commands recognise the data and
work fine.

The original source data that I am trying to summarise has been cut
and pasted (or copied) from another Excel spreadsheet - that was
exported from a Microsoft Access database.

This is wierd. I can't quite get my head round this. However is
there something in Excel that means it treats directly typed in data
differently from cut and pasted / copied data? I don't see why this
should be, but if anyone has come across this before - and knows a way
round the problem - any suggestions / advice would be much
appreciated.

BTW I have checked the format of the source data and ensured that
Excel is recognising these as numbers (not text). Otherwise I am at a
bit of a loss to know how to fix the problem. I am obviously keen not
to have to re-type all the data..

Any ideas?

With many thanks in anticipation.

Best wishes,


Poppy Gerard
[email protected]
 
H

Harlan Grove

Poppy Gerard said:
In my current spreadsheet I have setup the FREQUENCY commands in
exactly the same way, but all the output fields appear as zero.
Interestingly though when I manually re-type the source data in the
original columns - then the FREQUENCY commands recognise the data and
work fine. ....
BTW I have checked the format of the source data and ensured that
Excel is recognising these as numbers (not text). Otherwise I am at a
bit of a loss to know how to fix the problem. I am obviously keen not
to have to re-type all the data..
Click to expand...

How are you checking these? Number format is irrelevant. If you have a
formula in which =FREQUENCY($N$7:$N$2000,C60:C61) returns zeros, have you
tried the formula =ISNUMBER(N7) to see if Excel really does consider N7 to
be a number? If not, this is step 1. If ISNUMBER returns FALSE, as is likely
the case, then the standard hack is copying a blank cell then selecting the
data range (N7:N2000) and Edit > Paste Special, Add.
 
G

Gord Dibben

Poppy

The fact that re-typing the data allows the Function to recognize the data
leads me to believe the data is Text not numeric.

How did you check the format? =ISNUMBER(A1) which returns true or false? Or
just look at the format in Cells>Format?

I would try this step..........

Copy an empty cell then select your range of data and Paste
Special>Add>OK>Esc.

Try your FREQUENCY again. Make sure you enter with CRTL + SHIFT + ENTER

FREQUENCY is entered as an array formula after you select a range of adjacent
cells into which you want the returned distribution to appear.

The number of elements in the returned array is one more than the number of
elements in bins_array. The extra element in the returned array returns the
count of any values above the highest interval. For example, when counting
three ranges of values (intervals) that are entered into three cells, be sure
to enter FREQUENCY into four cells for the results. The extra cell returns the
number of values in data_array that are greater than the third interval value.

Gord Dibben Excel MVP
 
P

Poppy Gerard

Many thanks for these two postings. The problem was with the numeric
status of the cells. Excel didn't recognise my numbers as numbers.
Once I have done copy / paste special / add everything works fine!
Thank you kindly for answering this query, and so quickly. This is
much appreciated. With best wishes - PoppyG.


Gord Dibben said:
Poppy

The fact that re-typing the data allows the Function to recognize the data
leads me to believe the data is Text not numeric.

How did you check the format? =ISNUMBER(A1) which returns true or false? Or
just look at the format in Cells>Format?

I would try this step..........

Copy an empty cell then select your range of data and Paste
Special>Add>OK>Esc.

Try your FREQUENCY again. Make sure you enter with CRTL + SHIFT + ENTER

FREQUENCY is entered as an array formula after you select a range of adjacent
cells into which you want the returned distribution to appear.

The number of elements in the returned array is one more than the number of
elements in bins_array. The extra element in the returned array returns the
count of any values above the highest interval. For example, when counting
three ranges of values (intervals) that are entered into three cells, be sure
to enter FREQUENCY into four cells for the results. The extra cell returns the
number of values in data_array that are greater than the third interval value.

Gord Dibben Excel MVP


Hi there. I am new to this newsgroup - but I wonder if someone might
be able to help.

I have an Excel spreadsheet (MS Excel'97 .xls Workbook) which contains
a whole load of data. I am trying to use the FREQUENCY command [e.g.
=FREQUENCY($N$7:$N$2000,C60:C61)] to summarise certain columns of data
- to tell me the number of times that certain codes appear.

I have used the FREQUENCY command before in another spreadsheet
without any problem.

In my current spreadsheet I have setup the FREQUENCY commands in
exactly the same way, but all the output fields appear as zero.
Interestingly though when I manually re-type the source data in the
original columns - then the FREQUENCY commands recognise the data and
work fine.

The original source data that I am trying to summarise has been cut
and pasted (or copied) from another Excel spreadsheet - that was
exported from a Microsoft Access database.

This is wierd. I can't quite get my head round this. However is
there something in Excel that means it treats directly typed in data
differently from cut and pasted / copied data? I don't see why this
should be, but if anyone has come across this before - and knows a way
round the problem - any suggestions / advice would be much
appreciated.

BTW I have checked the format of the source data and ensured that
Excel is recognising these as numbers (not text). Otherwise I am at a
bit of a loss to know how to fix the problem. I am obviously keen not
to have to re-type all the data..

Any ideas?

With many thanks in anticipation.

Best wishes,


Poppy Gerard
[email protected]
Click to expand...
Click to expand...
 
Top