Help with excel finance problem

BATCHAMP2000 · #1 (**permalink**) 05-07-2009, 06:29 PM

Hey all, I was just wondering if anyone who is familiar with doing financial modeling in excel could help me out with this problem:

This sheet contains dates and daily volumes from 1994-2003. Determine the average trading volume (and standard deviations) on Mondays, on Tuesdays, on Wednesdays, on Thursdays, and on Fridays. Graph your results.

Basically its a spreadsheet with 2 columns- date (the first date is formatted as 1/3/1994) and volume and I have to do the above, any idea how i would go about it?

Watts · #2 (**permalink**) 05-07-2009, 09:24 PM

I'm about to save your life :P.

First, create a third column called weekday. The function for that column is =weekday(cell). Then sort the list by weekday (make sure to select all columns). Take the =average(cells) of volume for day 1, then the =stdevp(cells) of volume for day 1.

There are other ways, but this one is the easiest for someone unfamiliar with writing loops in excel.

If you need more explicit directions or a quick example, send me your email through a private message and I'll provide additional help.

IntelligentInvestor · #3 (**permalink**) 06-06-2009, 03:14 PM

Average (assume dates in column A, data in column B, row 1 is header)
=average(if(weekday(A2:A65536)=0,B2:B65536)) and press CTRL+SHIFT+ENTER (not just enter), same goes for stdev.
you change 0 to 1, 2, 3, 4, 5, 6 accordingly

Sponsors