How to write a user defined function in Excell?
Okay, so I have a list of stock prices in excell set up so that each row (64 rows) corresponds to a specific date, and each column (7 columns) corresponds to a different stock. The dates are ascending, meaning that the further back dates are down the rows and the most recent dates are at the top. For every date and every stock column I want to do the following:
Compute the Percentile Difference from the day before. So if I was at some date in the column for VNC stock, I want to create a column named "PVNC" that computes the following for each value of the stock at a specific date which corresponds to the value at that point, so 'n' stands for the value of the stock on that day, and '(n1)' stands for the value of the stock the day before: P = ((100)*((n1)n))/(n1)
So I want Excell to run this calulation and make a column corresponding to each of the 7 stocks such that the dates row lines up with the calculation for with the price at that date as 'n' and the price the day before as 'n1'. These price values are all in the spread sheet. I want to add 7 more columns to the right of the columns that are there, that all represent the appropriate values of the price change percentile at that date, each accosiated with the name of the stock at the top (which is just a matter of typing in the name of the stock, the rest I'm unable to do). Is there a function in excell to do this? Or is there a simple way to write a user defined function that can refer to the nth cell of a specific column? This is crucial to my job right now, so any help would be absolutly and greatly appreciated.
