exclude cells with string for calculation?

[cpliu]

I'd like to average multiple rows of cells that some has numbers but other
has strings. How do I exclude the ones with strings?

"=AVERAGE(M2:M247)" would get #DIV/0! instead of the average.

Thanks,

cpliu

 

[JE McGimpsey]

No, AVERAGE ignores strings.

You get #DIV/0 because you don't have any numbers.

If you think you have numbers, they were probably entered/pasted as
text. Copy an empty cell, select your numbers, and choose Edit/Paste
Special, selecting the Values and Add radio buttons. This will coerce
"text numbers" to real numbers.

 

[Frank Kabel]

Hi
are you sure that you have numbers in your range M2:M247 as AVERAGE
would ignore string values without a probelm. Sou you may check if your
numbers are really numbers. e.g. with the function
=ISNUMBER(M2)
this should return TRUE for a number. If this returns FALSE your
numbers are probably stores as text

 

[cpliu]

Thanks for the suggestions. You guys are right. Although they look like
numbers but they're text. Now I have a lot cells with false numbers. How do
I change them into numbers? All of them have a green triangle in the top
left corner of their cells. I have tried selecting and formating all the
cells to "Numbers" but they're still not numbers.

Thanks,

cpliu

 

[Frank Kabel]

Hi
try
- change the format for your values to 'General'
- select an empty cell and copy this cell
- select your range of 'text-numbers'
- goto 'Edit - Paste Special' and choose the action 'Add'

 

[JE McGimpsey]

One way:

Copy a blank cell. Select your "Numbers". Choose Edit/Paste Special,
selecting the Values and Add radio buttons. Click OK.

 

[cpliu]

Hi
try
- change the format for your values to 'General'
- select an empty cell and copy this cell
- select your range of 'text-numbers'
- goto 'Edit - Paste Special' and choose the action 'Add'

Frank, that's a cool tip. They're all changed to real numbers now. Why does
copy an empty cell (and Paste special) do the trick?

I appreciate a lot for your help,


cpliu

 

[Frank Kabel]

Hi
this mathematical operation forces Excel to convert the 'text-number'
to a real number. You could achieve the same with multiplying all cells
with 1