How do I ingnore non-numeric cells in Analysis toolpak?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

toloughlin - 10 May 2008 19:06 GMT

When using the Correlation Function in the analysis toolpak, I get an error
message if my dataset contains non-numeric fields. Is there a way to get
Excel to ignore fields that are non-numeric?

Reply to this Message

Jim Cone - 10 May 2008 20:32 GMT

Instead of using the wizard use the Correl function entered on the worksheet.
It will ignore text values in the data...
=CORREL(B5:B99,C5:C99)
-or-
You could sort your data and only use the numerical portion (text sorts separately).

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"toloughlin" <toloughlin@discussions.microsoft.com>
wrote in message
When using the Correlation Function in the analysis toolpak, I get an error
message if my dataset contains non-numeric fields. Is there a way to get
Excel to ignore fields that are non-numeric?

Reply to this Message

toloughlin - 11 May 2008 00:51 GMT

thanks. I really appreciate the help. I ended up doing a 'replace' of the
fields that I knew were the text fields and it worked. I just replaced them
with blank fields and it worked. I had too many columns to do a correl
function for each one but I really appreciate your advice. It's really cool
to see people offering help to strangers.

> Instead of using the wizard use the Correl function entered on the worksheet.
> It will ignore text values in the data...
> =CORREL(B5:B99,C5:C99)
> -or-
> You could sort your data and only use the numerical portion (text sorts separately).

Reply to this Message