DC-Den

🂡🂢🂣🂤🂥 In DC-ML's Supporting Functions , I created an Excel LAMBDA function, SelectData , to split data into training and testing datasets. This assumes data is randomly collected. If it isn't shuffling before splitting can increase confidence in the results. In this blog, I’ll show how to shuffle your dataset. Why Shuffle your data Splitting data helps prevent overfitting by using the test dataset to evaluate model accuracy on unseen data. Shuffling data prevents models from learning ordered patterns that could introduce bias. By randomizing the data, the model generalizes better to unseen data, improving performance and accuracy. ShuffleData The ShuffleData function is simple. It generates a random array ( randArray ) matching the dataset's rows and sorts the dataset using SORTBY based on this array. =LAMBDA(array, [headers], LET( randArray, RANDARRAY(ROWS(array), 1), sortArray, SORTBY(array, randArray), IF(ISOMITTED(headers), sortArray, VS...

We wrap up our "Out of the Box" series with the  F.TEST  function, used to check if the variances of two samples are equal. Variance is simply the square of the standard deviation `sigma^2`, which measures how spread out data is around the mean. Since variance and standard deviation are mathematically linked, we use the terms interchangeably. The F.TEST formula is simple: F.TEST(array1, array2) where array1 and array2  are your data ranges, which don’t need to be the same size. Visualizing Variance with Box Plots In the previous post, we looked at box plots for five sample distributions. From the box plots, you might think that samples A, B, and D have the same spread , while C and E are different . However, these box plots only show quartiles (25% to 75% of the data) and may not accurately reflect variance. Using the F.TEST formula, we find that while sample A's variance differs from sample B's, sample C's variance is similar to B's. As it turns out sample...

🎉Happy new year, 2025! 🎊 It’s been a stressful and tumultuous year for me, but I’m grateful to have made it through one of the most challenging times of my life. While I’m not completely out of the woods, I thank God for protecting me and keeping me safe in him. Looking forward, I plan to continue writing about Excel and LAMBDA, not only for my own growth but to explore niche topics that interest me. Off the blog, I’ve been diving deeper into numerical analysis with Excel. Once I’m satisfied with my approach, I will share my insights that hopefully could be valuable to others. Wishing you all a Happy New Year filled with peace. God bless!

Excel's  T.TEST  function is commonly used to compare the means of two sample data sets. According to its documentation , the function is designed to: ...to determine whether two samples are likely to have come from the same two underlying populations that have the same mean . In order words, T.TEST  tests whether the average (mean) values of two samples are significantly different. However, it does not assess whether one sample's average is specifically greater than or less than the other. If your goal is to test if the mean of one sample is greater than or lesser than the other, T.TEST may not be the right tool. Instead, you might consider alternatives like  DC-DEN's two sample mean test , which is specifically designed to compare means in a directional (greater, lesser or equal) context. Understanding the T.TEST formula in Excel The T.TEST function in Excel compares the means of two data sets to determine if they are statistically different. The formula is as fo...

Previously we saw the One Sample Mean test implemented using LAMBDA  by calculating the test statistics. Excel provides something similar using the  Z.TEST  function. The Z.TEST function compares the average of a sample if it is statistically less than or equal to a test value. The formula is stated as: Z.TEST(array, x, [sigma]) where array is the range of data to be tested against x x is the value of test. sigma is optional. If the population standard deviation is known, put it here. Otherwise leave it empty, and Excel will use the sample standard deviation instead. In effect you are testing to see if the average of the array is less than or equal to x `bar(array) < x` Comparing for Less Than or Equal To Observe the box plot of 5 sample distributions above. We want to check if the distributions averages are less than or equal to the value of 50 . We see samples A and B less than 50, sample C around 50, and samples D and E more than 50. NOTE: Where possible do box...

So Excel Lambda is great! You can create custom functions that are not included in Excel. And we have seen in this blog how you could implement various statistical hypothesis testing .  But then what are those Excel TEST functions for? What hypothesis testing can I do with these Out-of-the-box TEST functions? In the next few posts, I will describe some common hypothesis testing you could do: Proportion Test with  CHISQ.TEST One Sample Mean Test with  Z.TEST Two Sample Mean Test with  T.TEST Variance Test with  F.TEST Excel's Chi Squared Test for One Proportion Testing In Excel's documentation, CHISQ.TEST is described as a test for independence . It does this by doing a goodness-to-fit test on how well the data matches the expected. This means we can also use it for one proportion testing. To use CHISQ.TEST we will need to compare the sample proportion against the expected proportion. But unlike DC-Den's One Proportion Testing  where we only need to spe...

Terminologies We learnt a few machine learning  terminologies  and algorithms in this blog. Supervised  means we rely on labelled training data. It is task driven to identify a goal. Unsupervised  means unlabeled training data. It is data driven to identify a pattern. Classification arranges data into classes/categories using a labeled dataset. Regression develops a model to predict continuous numerical values. Clustering  separates an unlabeled dataset into clusters/groups of similar objects. Classification is a supervised learning algorithm, while Clustering is an unsupervised algorithm. Regression is considered supervised learning because the model is trained using both the input features and output labels - which can be numerical values. I will mention here that two other unsupervised approaches are:  Association , to identify underlying relationships, and Dimension Reduction , to reduce the number dimensions/features to make calculations simpler. I d...

Naïve Bayes is a categorial probabilistic supervised classification. You may already be familiar with the terminology  supervised classification , so I will not repeat it here. Naïve Bayes doesn't require numerical values. It relies on categories or labels only. It is probabilistic because it uses probabilities to calculate the classification. It calculates the classification probabilities based on given records. The more consistent the data (repeatable patterns), the stronger the probability of the classification.  Note : This implementation and example here follows closely from Learn Data Mining Through Excel by Hong Zhou . Great book! Bayes Theorem Naïve Bayes is based on Bayes theorem most famously written as below: `P(y|x) = (P(x|y)*P(y)) / (P(x))` where: `P(y|x)` is the probability of `y` given `x` `P(x|y)` probability of `x` given `y` `P(y)` and `P(x)` are the probabilities of y and x respectively. With multi-independent variables `(x_1, x_2, ..., x_n)` the equation wou...

The closer an observation to a class, the more likely it belongs to that class. KNN or K-Nearest Neighbour is a non-parametric supervised learning classification algorithm. It uses proximity to make classifications about the grouping of an observation point. How do you measure proximity? You may remember Pythagoras' theorem from your school days. The distance between two points is the square root of the sum of the squares of the sides. This would be one way to define proximity. But we could define proximity differently. Given two points (`x_1`, `x_2`, `x_3`) and (`y_1`, `y_2`, `y_3`)  Euclidean distance `= sqrt( (y_1-x_1)^2 + (y_2-x_2)^2 + (y_3-x_3)^2` Manhattan distance: `= |y_1-x_1| + |y_2-x_2| + |y_3-x_3|` Chebyshev distance: `= max(|y_1-x_1|, |y_2-x_2|, |y_3-x_3|)` Each of these definitions have their pros and cons. For our implementation, I will be using Euclidean distance. However if you wish to reduce the computation complexity, you might want to try Manhattan or Chebyshev...

Linear Discriminant Analysis LDA uses linear regression to supervise the classification of data. Essentially you assign each class a numerical value. Then use  linear regression method  to calculate the projection of your observations to the assigned numerical values. Finally you calculate the thresholds to distinguish between classes. Essentially LDA attempts to find the best linear function that separates your data points into distinct classes. The above diagram illustrates this idea. Implementing LDA using LAMBDA Fit Steps in implementing LDA's Fit : 1. Find the distinct classes and assign each with an arbitrary value - UNIQUE and SEQUENCE . 2. Designate each observation with the arbitrary assigned value depending on its class - XLOOKUP . 3. Find the linear regression coefficients for this observations - dcrML.Linear.Fit . 4. Project each observation on the linear regression - dcrML.Linear.Predict . 5. Find the threshold of each class - classCutOff  from the spread ...

KMeans clustering is a method to partition your observations into k clusters with k centroids that describes the centre of each cluster . When given a new observation, it is part of a cluster if it is closest to the centroid of that cluster. The diagram above illustrates the k-means clustering concept. The KMeans approach starts by deciding the number of clusters you wish. Then you estimate where the centroids of each cluster might be located. The distance of each observation to each centroid is calculated. Then each observation is re-clustered to the closest centroid. For each new cluster, we re-calculate a new centroid by averaging the cluster data by each feature. We repeat this cycle until no further refinement is achieved. Since Excel LAMBDA does not have iterative loops, a recursive approach will be used. Implementing KMeans clustering in LAMBDA With k-means clustering we implement Predict  before  Fit . Predict Predict takes a list of observations array and a list...

This is a short post to describe some terms used in data mining. Classification arranges data into classes/categories using a labeled dataset. Clustering separates an unlabeled dataset into clusters/groups of similar objects. Regression develops a model to predict continuous numerical values.  Classification is a supervised learning algorithm, while Clustering is an unsupervised algorithm. Regression is considered supervised learning because the model is trained using both the input features and output labels - which can be numerical values. Supervised means we rely on labelled training data. Unsupervised means unlabeled training data. That's all for now from DC-DEN !

Happy New Year🎈🎉! When DC-DEN blog started in 2021, I really wanted to write on data centre stuff. But I realised a number of things I would show/share is either confidential or proprietary. So in 2023, I decided to extend the blog about Excel LAMBDA in the area of statistics and data mining. I hope you are taking a good break and that you will come back next year for more from  DC-DEN !

Excel Lambda functions can be used to create custom, reusable functions by giving them a friendly name. This means a custom Lambda function could call itself. A recursive function is a function that calls itself and self-moves towards an exit condition. Without the exit condition, the function loops continuously unless externally terminated. Why use recursion? Some problems can be expressed in an elegant recursive structure. For example the n-th factorial is defined as: `n! = n (n-1) (n-2) ... 1` with the termination condition: `0! = 1` Recursion example using Lambda Before we start, Excel provides the FACT function to calculate the factorial of a number. You can use this to compare the results of your own. This is an example of a recursive function MyFactorial =LAMBDA(n, IF(n = 0, 1, n * MyFactorial(n-1) ) ) The IF function is used to determine the termination condition. If the termination condition has not been reached, decrement the variable by 1 and call the functi...

In the previous article on  Linear Regression , I mentioned Excel's LINEST function. But if you tried using the returned coefficients, you may notice something peculiar. The order of the returned linear coefficients is in the reverse order of the input data. LINEST documents: The equation for the line is: `y = m_1x_1 + m_2x_2 + ... + m_nx_n+ b` if there are multiple ranges of x-values, where the dependent y-values are a function of the independent x-values. The m-values are coefficients corresponding to each x-value, and b is a constant value. Note that y, x, and m can be vectors. The array that the LINEST function returns is `{m_n, m_(n-1), ..., m_1, b}`.  The input is in the order 1st, 2nd, 3rd, ... but the returned coefficients are in the reverse. And if you were to use the coefficients to predict y for a given `x_1, x_2, x_3, ...`, you would either swap the x-s around or the coefficients around. This isn't intuitive. For this reason you should reinvent LINEST . The inte...

Linear Regression is the modelling of the relationship of a dependent variable to one or more independent variables. You want to predict the value of a dependent variable given other independent variable values. The aim of linear regression is to find the line with the best fit for the given data. Simple and Multiple Linear Regression Simple linear regression is the case of only one independent variable. The equation is written as: `y = b + mx` Multiple linear regression has many independent variables and the equation is written as: `y = b + m_1x_1 + m_2x_2 + m_3x_3 + ...` where: `y` is the dependent variable `x_i` are the independent variables `m_i` are the coefficient for the corresponding `x_i` variables `b` is a constant, sometimes known as error or offset `b` also has a special property. It is the y-intersect when `x_i = 0` for all `i`. For simple linear regression `m` and `b` are calculated using the formula: `m = sum_((x - barx)(y - bary))/sum_((x - barx)^2)` `b = bary - m barx`...