In Excel: nstructions:

Data File: Customer Data File.xlsx

Study: The content of the following folders:

$$ The Concept of Similarity and Distance $($Euclidean distance$)$

$$ Data transformation and coding

The goal is to construct a matrix of Euclidean distances measuring similarity.

$1.$ Transform the variables and presented in the yellow table as follows:

a$.$ Income bins $($ordinal scale$)$ have been assigned the following scores $($this is done for you,

Table$1$ in the Excel file$)$

$20-30$k $...1$

$30-40$k $...2$

$40-50$k $...3$

$50-60$k $...4$

b$.$ Categorical variable measured on an ordinal scale assigned the following scores $($done

for you$).$

$$ High School $...1$

$$ B$.$S$....2$

$$ MS$/$PhD $...3$

c$.$ Categorical variable Gender, measured on a nominal scale assigned the following

dummy binary values $($done for you$)$:

$$ Male $...0$

$$ Female $...1$

d$.$ Quantitative variable Age, measured on a ratio scale are transformed using $0-1$

normalization. Apply Excel formulas to calculate.

$2.$ Apply the Euclidean distance formula to the table of transformed variables $($worksheet$)$ and

calculate the distance $($similarity$)$ between each pair of customers. This task should be done on

the $$Transformed Data$$ worksheet. Calculate the distance for only the first four customers

$($highlighted cells of Table $2).$

Note $1$: Each cell must have a formula that calculates the appropriate distance. Do not type a

previously$-$calculated distance in the cell.

Note $2$: By properly applying $$absolute cell address$$ and $$relative cell address$$ you should be able to

streamline the typing of formulas $($copy$/$paste$)$; but needs some careful attention.

Save and submit your Excel sheet to BB$.$