Calculation based on number of empty cells

murthybhs

Hello everyone,

I have a data set like this:

I want an average of the cells going back 5 cells from the most recent occurrence and multiply the average to the number of empty cells after the most recent occurrence.

For example, let's consider company A: Here the most recent occurrence is NOV_2020, so I want an average of JUL_2020 to NOV_2020. As shown in the image, it is 1500. So I want 1500 multiplied by 1 since there is only one empty cell after NOV_2020 i.e. 1000 (most recent occurance)

For example, in company B: The most recent occurrence is SEP_2020. Going back to 5 cells, the average from MAY_2020 to SEP_2020 is 10800. But there are 3 empty cells after SEP_2020. So, I want the average to be multiplied by 3.

Any help in this would be highly appreciated. Thank you for being amazing!! 

AbhilashR

Hi @murthybhs, I started making an attempt to build my logic but got sidetracked and now see that there are others who have already answered your post. Below is my solution just in case you cared for another way of achieving the same objective.

Thank you for posting your question and we hope this helps!

sample.yxmd