how best to analyse data

  pookie 13:42 PM 26 Nov 11


I have windows 7 with Office 2010. Using the Office software eg Excel, is it possible to analayse data to make predictions? For example if the shop in question does a paper based survey of clients and finds that every 3 weeks a 40-50 year olds buys a black dress and spends £50, every 2 days a 20-30 year spends £25 on brown shoes etc is it possible with lots of that data of ages, clothes items etc to predict as best as possible that eg tomorrow a 40 year old will buy a blue shirt. This is not for stock purposes but more for anaylsing clients needs, spending habits etc. I have already put most of the data on a spreadsheet.

Or, do I need new software to do this?

Many thanks


  Picklefactory 14:47 PM 26 Nov 11

You could certainly use Excel or Access to track data and create averages, they are both very powerful and specifically designed for juggling numbers and data, but the main restriction would be on your ability to use them to their potential. By tracking details the averages produced could tell you how often a particular event has happened historically, and thus give an indication of a possible repeat, but I can't see how anything can predict the future.

  pookie 17:02 PM 26 Nov 11

Thank you. I'll try it on excel and if no good then Access (never used it)


  pookie 15:53 PM 29 Jan 12


It is working fine just using Excel. Do you happen to know if there is a way/formulae to sort data automatically without clicking on sort, smallest to largest etc. For example if column A has values a to z and column B has number values can I set it up some way that columns C and D automtically sort and show the largest number values and their a to z value if I makes changes to column A or B? At the moment I am copying and pasting A and B to other columns and sorting, smalled to largest etc manually.

Kind regards


  Newuser939 19:11 PM 30 Jan 12

You may find that a pivot table would do what you want, but there is a bit of a learning curve. If you google for how to create a pivot table, you will find several tutorials, but here is one example

  Picklefactory 12:56 PM 31 Jan 12


It is certainly possible to auto sort by a few methods. I could provide some simple VBA that would sort by your favoured columns when anything on the worksheet changes, or when a specific cell changes, or with a button on your sheet that would do a custom sort when clicked, maybe on completion of entry of one row of data. At what point would you want the sheet to sort?

  pookie 12:55 PM 01 Feb 12

Thank you. I'll try that


This thread is now locked and can not be replied to.

Best Cyber Monday deals 2015 live blog: Best UK Cyber Monday tech deals, sales, discounts & offers

1995-2015: How technology has changed the world in 20 years

Harry Potter's graphic designers showcase magical props & posters in new exhibition

Apple rumours & predictions 2016: What to expect from Apple in 2016