Hello,
I have the two rows:
26/09/2018 27/09/2018 28/09/2018 29/09/2018 30/09/2018 01/10/2018 02/10/2018 03/10/2018 04/10/2018 05/10/2018 06/10/2018 07/10/2018 08/10/2018
4 5 2 0 0 0 6 9 7 0 0 2 4
(copy/paste them in excel as I have no idea how to paste as table)
In a separate cell, I have:
01/10/2018
I want in the third row below the first two, to have this:
1) if the top date is before the separate date (01/10/2018), fill with zero
2) if the top date is after the separate date (01/10/2018), fill with the below:
divide the value of the cell above to the value of the sum of a cell range
that cell range starts from the second row cell that the first row date is the FIRST date that is after the separate cell date
and the cell range ends at the end of the row
I assume this needs some kind of lookup function to identify the right point where the cell range of the sum starts.
However, I would prefer to use index/match as I want to learn more about that function.
Thanks!
I have the two rows:
26/09/2018 27/09/2018 28/09/2018 29/09/2018 30/09/2018 01/10/2018 02/10/2018 03/10/2018 04/10/2018 05/10/2018 06/10/2018 07/10/2018 08/10/2018
4 5 2 0 0 0 6 9 7 0 0 2 4
(copy/paste them in excel as I have no idea how to paste as table)
In a separate cell, I have:
01/10/2018
I want in the third row below the first two, to have this:
1) if the top date is before the separate date (01/10/2018), fill with zero
2) if the top date is after the separate date (01/10/2018), fill with the below:
divide the value of the cell above to the value of the sum of a cell range
that cell range starts from the second row cell that the first row date is the FIRST date that is after the separate cell date
and the cell range ends at the end of the row
I assume this needs some kind of lookup function to identify the right point where the cell range of the sum starts.
However, I would prefer to use index/match as I want to learn more about that function.
Thanks!
