Correlation between categorical and numerical values - Excel 2016

[lamaman]

Dear all, hope there is a statistical crack in here who can help me with the challenge of measuring the correlation a numerical and a set of categorical numbers.

In a nutshell:
I'm analysing a set of marketing data that is showing me the response of customers (measured as '# of people calling a hotline') to radio advertisement, which is promoting the hotline. Every ad that was aired represents one line in the data sheet and contains coloumns that describe the details of the ad such as the name of the radio station, the time, the day and the type of spot (see table below - the actual data table is several 1.000s of lines long).

I'm now interested in how strongly each of those categorical variables (radio station, time etc) correlate with the # of calls. Meaning, what is influencing the # of calls the most? Is it the radio station, is it the time, the spot type...?!

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]# of calls[/TD]
[TD]Radio station[/TD]
[TD]Time[/TD]
[TD]Day[/TD]
[TD]Spot type[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Station A[/TD]
[TD]9.00[/TD]
[TD]Mo[/TD]
[TD]Spot A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Station A[/TD]
[TD]13.00[/TD]
[TD]Mo[/TD]
[TD]Spot B[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Station C[/TD]
[TD]9.00[/TD]
[TD]Wed[/TD]
[TD]Spot B[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Station B[/TD]
[TD]20.00[/TD]
[TD]Thur[/TD]
[TD]Spot C[/TD]
[/TR]
</tbody>[/TABLE]









So far I've tried excel's correlation function of the Data Analysis Tool Pack and by translating all categorical values into numerical ones - which turned out to be complete BS because there is no particular order in those variables

Any support is greatly appreciated!!

 

[NotoriousPopol]

A pivot table could help you visualize the trend for each factor. But to have a clear mesure of correlation, I'm not sure you can do that without an external tool!