Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: alerts!

  1. #1
    Guest

    Default

    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

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.




    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •