Excel Functions

[Be Frank]

I have some data that was imported into Excel from a large databse system
(name unknown). This data acts very strange: it looks numeric but doesn't
act like it. It may even act numeric for a function or two, then 'lose" its
ability to be numeric. I've gone through various conversions that the Help
has described, but to no avail. Specifically, I'm trying to do a "countif"
adding some of the "numbers" in the 200 range, e.g. countif >200. The cell
returns the formula, that is: countif >200. I've done a lot of Excel work,
but I have never had data work like this. I've copied in some data from the
SS.

F Native Amer/AK Nat. n n y n rml n
N n N Y N N 4 189 BAS 182 BB 191 BAS
M Caucasian n n n n n n N n N Y N N 4 184 BB
M Caucasian y n y n rml n N n N N N N 4
M Caucasian n n n n n n N n N N N N 4
M Caucasian y n y n n n
N n N N N N 4 200 Prof 211 Prof 203 Prof
M Caucasian y n n n n n
N n N N N N 4 177 BB 190 BAS 184 BB
M Caucasian y n y n rml n N n N N N N 4
M Caucasian y n y n n n
N n N N N N 4 182 BB 179 BB 185 BB

 

[Ken Wright]

Your data has come in as text, that's all.

For the numeric data, put a 1 in an empty cell somewhere, select all your
data, do edit / Paste special / Multiply

For the formulas, select the entire sheet, do format / cells / general or
number etc, then just renter the formulas. If you have lot sof them, just
select all and do edit / replace, replacing = with =. Sounds daft i know
but it works.

 

[Niek Otten]

Minor addition:

For the numeric data, put a 1 in an empty cell somewhere,

EDIT>COPY,

select all your data, do edit / Paste special / Multiply

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Ken Wright]

Dohhhh - Cheers Niek

 

[RagDyeR]

Yeah ... But guys,

Isn't it better to use an *empty*, new cell instead, and then use "Add"?

That way, you don't create zeroes in empty cells within the selected range!
--

Regards,

RD

 

[Ken Wright]

True . Must admit though, I usually tend to use specialcells to just
select the cells I'm interested in.

 

[RagDyeR]

You're smart ... OPs usually aren't.<g>
XL smart, that is!
--

Regards,

RD

 

[Ken Wright]

I have plenty of dense moments too