Where is the non numeric data in this file?

[frozenfred]

non numeric data qn.xlsx
	F
1
Sheet1

Hi everyone
I'm trying to run some descriptive analysis but apparently there's some non numeric data in my file. I have tried to do the text to columns process, and all the cells are formatted as General but still no joy. Please help Thank you

 

[Toadstool]

Hi FrozenFred,

For XL2BB you neeed to select the range to post (max 3,000 cells) before selecting Capture Range.

Changing the format won't help. You can use TYPE to check if a cell contains a number, maybe something like this where I find two sneaky alpha characters in my range:

FrozenFred.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Data	Data	Data	Data	Data
2	47	589	267	10	352		.	.	.	.	.
3	106	277	134	358	456		.	.	.	.	.
4	391	547	3O7	465	599		.	.	2:$C$4	.	.
5	702	647	309	751	351		.	.	.	.	.
6	549	314	586	892	131		.	.	.	.	.
7	861	248	514	293	568		.	.	.	.	.
8	384	484	39	77I	276		.	.	.	2:$D$8	.
9	208	76	784	269	94		.	.	.	.	.
10	260	125	622	167	784		.	.	.	.	.
Sheet2

Cell Formulas
Range	Formula
G2:K10	G2	=IF(TYPE(A2)=1,".",TYPE(A2)&":"&CELL("address",A2))

 

[frozenfred]

Thank you @Toadstool I'd only changed the format as part of the advice of convert text to number. Anyhow, seems the problem is the blank cells but we are supposed to be able to run descriptive statistics with blank cells. Can you advise what I should do now, or should I start a new topic?

 

[frozenfred]

Ignore my second question. I have realised a purely blank cell fixes the problem.

 

[Toadstool]

So your problem is some cells are null but should contain a zero? Like this?

FrozenFred.xlsx
	A	B	C	D	E
1	Data	Data	Data	Data	Data
2	47	589	267	10	352
3	106	277	134	358	456
4	391	547		465	599
5	702	647	309	751	351
6	549	314	586	892	131
7	861	248	514	293	568
8	384	484	39		276
9	208	76	784	269	94
10	260	125	622	167	784
Sheet1

You could use Find & Select, Replace and leave "Find what" empty and put a zero in "Replace with" then click Replace All.

 

[frozenfred]

Shame the three won't let me say you solved two of my problems @Toadstool. Much appreciated

 

[frozenfred]

Btw actually no, for the record I need the cell to be completely blank but I fixed the issue.