[swimmer]

Hi,

First post which will show what a beginner I am.

Predictions:
I have windows 7 with Office 2010. Using the Excel software, 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.

Automatically sort data:
With the above data I already have on an Excel spreadsheet 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.

I hope the above makes sense.

Thank you

P

 

[RSpecianjr]

Hey swimmer,

It's all possible. haha

You can take a slew of data and "forecast" what is coming next. This obviously isn't necessarily true but you can take a look at trends and see where the trend is leading. This is a very common thing to do in business, particularly restaurants for ordering food etc.

As far as auto-sorting, there are various ways to do it. You can use formulas or VBA macros. It's odd to house the sorted data right next to your source data though. Usually you only want to see a snippit and so it is previewed on another worksheet, separate from the main database. That being said...

Are there duplicate number values? If not you could use a simple Large formula and a lookup formula. Something like...

Column D:

=LARGE(B1:B20,row())

Column C:

=INDEX(A1:A20,MATCH(E3,B1:B20,0),1)

This assumes you don't have titles. If you have titles you will have to adjust column D formulas. The change should look like ",row()-1)" without the ""'s. This makes sure that the largest number is the first largest number. Whatever the start row is, you have to subtract the number that will give you the value of 1.

If its in row 1, you are good. If it is in row 3, you have to subtract 2... etc.

Hope this helps,

Robert D. Specian Jr.

 

[swimmer]

Thank you Robert. I'll try it later.

Kind regrads,

P