hi,
in cell S8 of sheet2 im trying to do a calculation
in sheet1...
i have values in sheet 1 in column I, I2:I42
i have dates in sheet 1 in column J, J2:J42
i have values in sheet 1 in column K, K2:K42
in sheet2...
i have values in sheet 2 in column M, P, and G and dates in column E all of this data starts at row 5 going down X number of rows, however they will all go down the same number of rows, X because the external feed will update and I don't know how many rows it will populate
the dates in cell E5 of sheet2 could be entered anywhere between E5:EX depending on the update
i have a single value in cell R8 of sheet2
the final answer of the calculation would be in cell S8 of sheet2
the logic for the calculation is :
check date in column E of sheet2 with the dates in column J of sheet1, for each date in column J of sheet1 that is on or before the date in column E of sheet2 do the following calculation
(1 + ((value in sheet1 column I associated with date in column J - value in sheet2 R8) * (value in sheet1 column K associated with date in column J / 360))
depending on how many dates in column J of sheet1 that are on or before the date in column E of sheet2, this calculation above would be done multiple times
next... (1 + (value in sheet2 column P that is associated with the date in sheet2 column E - value in sheet2 R8) * (value in sheet2 column M associated with date in sheet2 column E/ 360))
multiply the result from the expression highlighted in green with each result from each of the blue expression, lets call this the denominator result...
so if four dates in column J of sheet1 are on or before the date in column E of sheet2 the "denominator result" would be
blue1 * blue2 * blue3 * blue4 * green
finally, (the value in sheet2 column G that is associated with the with date in sheet2 column E / denominator result)
the expression highlighted in red the the final value....for this particular date in sheet1 column E...
if the external feed populated only one date, thus column E had 1 date, this would be the final answer in cell S8... if we had four dates in column E in sheet2 the calculation is done four times and each result from the red expression is added, and that's the answer shown in cell S8
im not sure how to get started on this because the number of dates in column E is dynamic (i.e. depends on the update from external feed) and im not sure how the macro would be written that will use the correct dates and the associated values in sheet1 based on the date in sheet2 column E
thanks, any help is appreciated
in cell S8 of sheet2 im trying to do a calculation
in sheet1...
i have values in sheet 1 in column I, I2:I42
i have dates in sheet 1 in column J, J2:J42
i have values in sheet 1 in column K, K2:K42
in sheet2...
i have values in sheet 2 in column M, P, and G and dates in column E all of this data starts at row 5 going down X number of rows, however they will all go down the same number of rows, X because the external feed will update and I don't know how many rows it will populate
the dates in cell E5 of sheet2 could be entered anywhere between E5:EX depending on the update
i have a single value in cell R8 of sheet2
the final answer of the calculation would be in cell S8 of sheet2
the logic for the calculation is :
check date in column E of sheet2 with the dates in column J of sheet1, for each date in column J of sheet1 that is on or before the date in column E of sheet2 do the following calculation
(1 + ((value in sheet1 column I associated with date in column J - value in sheet2 R8) * (value in sheet1 column K associated with date in column J / 360))
depending on how many dates in column J of sheet1 that are on or before the date in column E of sheet2, this calculation above would be done multiple times
next... (1 + (value in sheet2 column P that is associated with the date in sheet2 column E - value in sheet2 R8) * (value in sheet2 column M associated with date in sheet2 column E/ 360))
multiply the result from the expression highlighted in green with each result from each of the blue expression, lets call this the denominator result...
so if four dates in column J of sheet1 are on or before the date in column E of sheet2 the "denominator result" would be
blue1 * blue2 * blue3 * blue4 * green
finally, (the value in sheet2 column G that is associated with the with date in sheet2 column E / denominator result)
the expression highlighted in red the the final value....for this particular date in sheet1 column E...
if the external feed populated only one date, thus column E had 1 date, this would be the final answer in cell S8... if we had four dates in column E in sheet2 the calculation is done four times and each result from the red expression is added, and that's the answer shown in cell S8
im not sure how to get started on this because the number of dates in column E is dynamic (i.e. depends on the update from external feed) and im not sure how the macro would be written that will use the correct dates and the associated values in sheet1 based on the date in sheet2 column E
thanks, any help is appreciated
