Statistics

Posted by Mo on May 16, 2001 1:01 PM

Hi, I'm not sure if this is the right forum for this question, I just thought since excel is a popular programme for business analysts, someone might know.
My question basically is that I have a column consisting of money. This is a column derived from multiplying the amount of goods(shares) by price.

We sometimes recieve enquiries about particular trades. I am tring to deduce if there is any correlation between their enquiries and the size of the trade. I have decided to use the money column as my prime variable.

I was thinking of using averages of size of trade, but there are big differences in the trades that averages might be misleading. Is there a better way to do this in excel by using different techniques apart from averaging.

Before you all clever people tell me I'm trying to perfom voodoo by picking just one variable, as to deduce a relationship I will need to correlate that info with alot of other variables, all I have to say is you have a point. However I'm only trying to do something simple, I'm not an analyst.
Thankyou
Mo

Posted by iml on May 16, 2001 1:27 PM

Just an idea if I understand you correctly:
What if you categorized your size of trade by some categories such as Large, medium, small. You could put a formula in referencing the dollar. For example, if the trade is in A1:
=IF(A1>=10000000,"large",IF(A1>=5000000,"medium","small"))

You could then mark those trade with inquiries in say, column C by entering a 1.

You could sum up the inquires on large trades at the bottom by using an array formula such as:
=SUM((B1:B3="large")*(C1:C3)) (hit control shift enter

By counting up the total number of large trades (=COUNTIF($B$1:$B$3,"large") you could summurize for example that 15% of all large trades result in inquiries, whereas say 5% of small trades do?

Good luck

Posted by Dave Hawley on May 17, 2001 12:45 AM

Hi Mo

Have you looked at the MEDIAN, LARGE and SMALL functions. I know that Real Estate people quite often use the MEDIAN as apposed to AVERAGE.

Dave

OzGrid Business Applications

Posted by Mo on May 17, 2001 1:16 PM

Thanks both of you, Dave I think median is one of them, also there are quite a few others. Its really complicated if you want to do it properly. I could do econometrics when I left Uni, never persued it as a career, now its all gone.
Its the equavalent of you coming to this board in a few years time asking how to record a macro. Lol

Posted by John on May 21, 2001 7:56 AM

Mo-
You might want to do statistics on the quantiies (either shares or dollar amounts)for each particular item over some time period (perhaps discounting if you use dollars amounts and the time horizon is long). As a result you would have a mean and a standard deviation. With these two numbers, you could then establish a (one sided or two sided) confidence interval for each particular item. This will allow u to include the big variances (standard deviations) you mention....so you would have a list of companies and a 'band of trading' for each (say a 90% confidence interval)...then any trade that exceeded the confidence interval 'band', could be flagged for possible correlation with the number of inquiries.
You would also have the ability to change the size of the confidence interval...adjusting it to test you correlation hypothesis and examine type I and type II error.
Sounds like fun. good luck.