vba tricks for customer statistics in a nutshell

Hi there!

 

Finally we meet again:) OK! I've got a VBA trick for you which can help MSE company financial analysts to integrate this into statistics like forecasting and probability testing. I have included an example video content above so that no one will get stuck in the ruts. Today, while I was doing this routine, I was very comfortable and not hectic unlike other things from the past.

 

Before that, here’s the source code:

 

Dim lastrow As Long, erow As Long

 

'to check the last filled line in sheet named sales

 

lastrow = Worksheets("sales").Cells(Rows.Count, 1).End(xlUp).Row

 

For i = 2 To lastrow

 

Worksheets("sales").Cells(i, 1).Copy

 

erow = Worksheets("forecast").Cells(Rows.Count, 1).End(xlUp).Row

 

Worksheets("sales").Paste Destination:=Worksheets("forecast").Cells(erow + 1, 1)

 

Worksheets("sales").Cells(i, 2).Copy

 

Worksheets("sales").Paste Destination:=Worksheets("forecast").Cells(erow + 1, 2)

 

 Next i

 

Picked off this code from YouTube: https://www.youtube.com/watch?v=_2h2Buzhcf4

Next, I tested it on Chi-Square trying to test the probability of my sales to individual customers in my line of business. It’s helpful if I have 100 customers which is hard to keep track of. Plus, this code can be used in other stats where a sample is required.

 

Alright! Here’s my Chi-Square formulae that I’ve used:

Actual Sales: It’s actual sales

Expected Frequency: Sum of actual sales / 12 months

Expected sales: (Actual sales for month 1-Expected Frequency)^2 / Expected Frequency. Like that, please repeat the same for the rest of the months.

Chi-Square Score: Sum of all Expected sales and the bigger the better it is cause it signifies the statistical relationship.

Degrees of Freedom: 12 Months-1 = 11 (This means all values can differ by 11)

Significance level: Used 0.05 in my test

P-Value: =CHITEST(B2:B13,C2:C13)

Critical Value of Chi test: =CHISQ.INV.RT(I6,I5)

When this is all over, we have to interpret the hypothesis

Chis-Square Null Hypothesis: Reject it if Chi-Square result > Critical value

P-Value Null Hypothesis: Reject if P-value < Significance level

 

P-value ≤ α: The variables have a statistically significant association (Reject H0)

If the p-value is less than or equal to the significance level, you reject the null hypothesis and conclude that there is a statistically significant association between the variables.

P-value > α: Cannot conclude that the variables are associated (Fail to reject H0)

If the p-value is larger than the significance level, you fail to reject the null hypothesis because there is not enough evidence to conclude that the variables are associated.

 

This is not what I wanted to do today. I wanted to do this same thing from a dropdown list which offers more comfort than creating Macros and clicking on them!! Right now, I'm on a look out for that code and will be back when I get it.

Example

 

Bye