Hi guys,
Firstly apologies i don't have any example code or functions to show you where i am at atm, as i am very lost with regards to this particular situation.
I really just want to know if this is actually possible using VBA or some collection of excel functions.
Effectively i have a column of prices and a column of returns calculated at the moment as IFERROR(LN(t+1/t),""). This is fine when there is back to back prices in the column, however when there is a missing value for t+1 it obviously still tries to calculate the return with the missing value. This can be seen in the first set of 'Current' Columns.
My desired outcome is that excel recognises when there is a blank cell in t+1, and skips over it to the next available cell with a price in it. This can be seen in the 'desired' columns. I.e. if t+1 is blank, skip to t+2 or t+n and calculate the RTN as LN(t+n/t). It should then carry on as usual calculating Ln(t+1/t).
I hope that makes some sense. fairly confusing to articulate. Again im more interested in knowing if this is possible rather than having a respondent do the code or functions for me.
Many thanks
Firstly apologies i don't have any example code or functions to show you where i am at atm, as i am very lost with regards to this particular situation.
I really just want to know if this is actually possible using VBA or some collection of excel functions.
Effectively i have a column of prices and a column of returns calculated at the moment as IFERROR(LN(t+1/t),""). This is fine when there is back to back prices in the column, however when there is a missing value for t+1 it obviously still tries to calculate the return with the missing value. This can be seen in the first set of 'Current' Columns.
My desired outcome is that excel recognises when there is a blank cell in t+1, and skips over it to the next available cell with a price in it. This can be seen in the 'desired' columns. I.e. if t+1 is blank, skip to t+2 or t+n and calculate the RTN as LN(t+n/t). It should then carry on as usual calculating Ln(t+1/t).
I hope that makes some sense. fairly confusing to articulate. Again im more interested in knowing if this is possible rather than having a respondent do the code or functions for me.
Many thanks
| Book2 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| X | Y | Z | AA | AB | AC | AD | AE | |||
| 139416 | ||||||||||
| 139417 | Current | Desired | ||||||||
| 139418 | Prices | RTN | Prices | RTN | ||||||
| 139419 | .702 | .702 | ||||||||
| 139420 | .70185 | -0.021% | .70185 | -0.021% | ||||||
| 139421 | .70187 | 0.003% | .70187 | 0.003% | ||||||
| 139422 | .70189 | 0.003% | .70189 | 0.003% | ||||||
| 139423 | .70196 | 0.010% | .70196 | 0.010% | ||||||
| 139424 | .70199 | 0.004% | .70199 | 0.004% | ||||||
| 139425 | .70195 | -0.006% | .70195 | -0.006% | ||||||
| 139426 | .70196 | 0.001% | .70196 | 0.001% | ||||||
| 139427 | ||||||||||
| 139428 | .70198 | .70198 | 0.003% | |||||||
| 139429 | .70197 | -0.001% | .70197 | -0.001% | ||||||
| 139430 | .70197 | 0.000% | .70197 | 0.000% | ||||||
| 139431 | ||||||||||
| 139432 | ||||||||||
| 139433 | .70189 | .70189 | -0.011% | |||||||
| 139434 | .70185 | -0.006% | .70185 | -0.006% | ||||||
| 139435 | .70185 | 0.000% | .70185 | 0.000% | ||||||
| 139436 | .70187 | 0.003% | .70187 | 0.003% | ||||||
| 139437 | .70165 | -0.031% | .70165 | -0.031% | ||||||
| 139438 | .70177 | 0.017% | .70177 | 0.017% | ||||||
| 139439 | .70175 | -0.003% | .70175 | -0.003% | ||||||
| 139440 | ||||||||||
1 Min Closing data 2019 - 2Min | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| Z139420:Z139439,AC139420:AC139427,AC139429:AC139432,AC139434:AC139439 | Z139420 | =IFERROR(LN(Y139420/Y139419),"") |
| AC139428 | AC139428 | =IFERROR(LN(AB139428/AB139426),"") |
| AC139433 | AC139433 | =IFERROR(LN(AB139433/AB139430),"") |
