Hello everyone,
First of all, I would like to thank you for taking the time to help me out. Here is what I am looking to do:
-I have a table with categories that have a rate value for certain periods (dates) (Given)
-Then I have another(second table) table that have the same categories, start date input , end date input , value cell (formula cell)
-The formula will need to look at the input of the start date and end date (in the second table) and populate the correct value from the first table
-If the duration falls between two period it will need to populate the average of the two.
here is an example below, I hope my explanation made sense.
[TABLE="width: 1256"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD="colspan: 4"]GIVEN VALUES/LOOK UP TALBE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY[/TD]
[TD]RATES GOOD UP TO 6/30/2017[/TD]
[TD]RATES GOOD UP TO 6/30/2018[/TD]
[TD]RATES GOOD UP TO 6/30/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20[/TD]
[TD]22[/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]30[/TD]
[TD]35[/TD]
[TD]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]40[/TD]
[TD]45[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]50[/TD]
[TD]55[/TD]
[TD]60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]60[/TD]
[TD]65[/TD]
[TD]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]70[/TD]
[TD]75[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]80[/TD]
[TD]90[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Formula under the value cells will look at the start date and end date then will populate the correct value based on the period it falls in. If the duration falls in two periods it will take the average of the two period [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY [/TD]
[TD]VALUE [/TD]
[TD] START DATE[/TD]
[TD] END DATE[/TD]
[TD]Formula under the value cells will do the following[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20[/TD]
[TD="align: right"]4/30/2017[/TD]
[TD="align: right"]5/30/2017 [/TD]
[TD]since the period falls in 6/30/2017 rates it will populate the values for that period[/TD]
[/TR]
[TR]
[TD]
B[/TD]
[TD]
32.5[/TD]
[TD="align: right"]
3/30/2017[/TD]
[TD="align: right"]
5/30/2018 [/TD]
[TD]
since period falls in two periods 6/30/2017&5/30/2018 formula will average two values [/TD]
[/TR]
[TR]
[TD]
C[/TD]
[TD]
50[/TD]
[TD="align: right"]
7/30/2018[/TD]
[TD="align: right"]
4/30/2019[/TD]
[TD]
since the period falls in 6/30/2019 it will populate the values for that period[/TD]
[/TR]
</tbody>[/TABLE]
Thank you very much
First of all, I would like to thank you for taking the time to help me out. Here is what I am looking to do:
-I have a table with categories that have a rate value for certain periods (dates) (Given)
-Then I have another(second table) table that have the same categories, start date input , end date input , value cell (formula cell)
-The formula will need to look at the input of the start date and end date (in the second table) and populate the correct value from the first table
-If the duration falls between two period it will need to populate the average of the two.
here is an example below, I hope my explanation made sense.
[TABLE="width: 1256"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD="colspan: 4"]GIVEN VALUES/LOOK UP TALBE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY[/TD]
[TD]RATES GOOD UP TO 6/30/2017[/TD]
[TD]RATES GOOD UP TO 6/30/2018[/TD]
[TD]RATES GOOD UP TO 6/30/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20[/TD]
[TD]22[/TD]
[TD]26[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]30[/TD]
[TD]35[/TD]
[TD]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]40[/TD]
[TD]45[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]50[/TD]
[TD]55[/TD]
[TD]60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]60[/TD]
[TD]65[/TD]
[TD]70[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]70[/TD]
[TD]75[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]80[/TD]
[TD]90[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Formula under the value cells will look at the start date and end date then will populate the correct value based on the period it falls in. If the duration falls in two periods it will take the average of the two period [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CATEGORY [/TD]
[TD]VALUE [/TD]
[TD] START DATE[/TD]
[TD] END DATE[/TD]
[TD]Formula under the value cells will do the following[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20[/TD]
[TD="align: right"]4/30/2017[/TD]
[TD="align: right"]5/30/2017 [/TD]
[TD]since the period falls in 6/30/2017 rates it will populate the values for that period[/TD]
[/TR]
[TR]
[TD]
B[/TD]
[TD]
32.5[/TD]
[TD="align: right"]
3/30/2017[/TD]
[TD="align: right"]
5/30/2018 [/TD]
[TD]
since period falls in two periods 6/30/2017&5/30/2018 formula will average two values [/TD]
[/TR]
[TR]
[TD]
C[/TD]
[TD]
50[/TD]
[TD="align: right"]
7/30/2018[/TD]
[TD="align: right"]
4/30/2019[/TD]
[TD]
since the period falls in 6/30/2019 it will populate the values for that period[/TD]
[/TR]
</tbody>[/TABLE]
Thank you very much