alerts!

[Guest]

i have a pivot table which analyses the quantity of products bought by customers.

the table shows the volume over the past 4 years (rows) and months (columns) to try and show a pattern.

is there a way that i can define a pattern, and then an alert to show that an expected purchase has not happened?

thanks

richard

 

[Damon Ostrander]

Hi Richard,

I would recommend that you look into Excel's correlation coefficient calculation (function CORREL). I assume you want to see if there is a pattern in the month-to-month purchasing between different years. The correlation coefficient should tell you whether there is a similar pattern between 2001 and 2000, for example. If the correlation coefficient between years is zero (or very small) there is no correlation (no similarity in the pattern), but if it is 0.5 or larger there is a significant correlation (pattern). You would need to experiment a bit to find out how to set the alert threshold, but once you determine this you might come up with a test that says, for example, that if the correlation coefficient is below .2 an alert (use MsgBox) is issued to indicate that a significant departure from past history has occurred.

 

[Richiejjj]

i was the original poster of the question,..

thanks for your help

will this work if am comparing each month as the results happen, ie. comparing a series of months over the 2 years.

thanks