Hi Guys,
I'm New to forum(but not to the "Umms"and"Errs" of Exel) I will start by saying good day to all.
I'm quite new to Excel formulas and although I seem reasonably capable at using formulas on their own I am finding it difficult trying to link formulas together to create something simple and usable. After many day of head scratching I am offering this one up to the public in the hopes that someone can point me in the right direction for my research into solving my problems.
I will attach the workbook in question and post as many clear images as I can to try and clearly identify the issues.
Basics:
I have 2 drop down menus which each contain 28 pair selection options.
I need a formula that will identify which pair has been selected within each drop down box.
Once identified, the formula will then select the corresponding column for that pair from the "Price History sheet" (all columns are named after pair)
The formula then needs to calc the =CORREL between the two pairs selected and print a return value into a single cell.
I have tried to attach a numerical value to each pair using sumif or vlookup 1-28 (28 pairs). Then using "IF(AND)" I tried to manually pair each numerical value for the pairs drop down to the appropriate correl formula.
This did work, however I have 28x28 of these to input.
This also prints a return value for each formula into its own separate cell and I need all the return values to be returned into a single cell.
The third drop down menu "Time frame":
This formula needs to identify the selection from the "time frame" dropdown.
Dependent on the time frame chosen, it then needs to select the appropriate length of time/row-columns from the Price history sheet and apply the =CORREL to the selected pairs specific to the time frame chosen.
This formula would need to be dynamic in some sense.The Price History Sheet will continue gathering data and grow in size. The time frame formula needs to move down as the data is added. (e.g. It needs to select the most recent weeks,months,year of date and not just keep displaying the same range)
Closing Price:
This sheets brings in prices and updates live.
I am lost on this one.
Is it possible to have the live closing prices for each pair print onto the Price History sheets on a per week basis?
Ideally the formula would take the closing prices from the chart and print a new horizontal row within the price history sheet on a set date weekly. (e.g. Every sunday at 12pm the formula prints the current live price data into horizontal row of price history sheet.
This would need to print a new week ending date in column A and then apply the right closing price to the corresponding column.
Wow! I thought I just had a couple of small issues to resolve:D
I hope I explained things in an understandable format but please forgive me if this is not the case. If anyone has any questions then please feel free to ask.
I am not looking for someone to do my homework for me or anything, but if anyone could advise me as to whether these issues can be resolved and guidance towards direction or the types of logic needed to progress would be greatly appreciated.
Thank you for your time.
Bookmarks