jeudi 30 juillet 2020

Count the number of successively fulfilled conditions in Spreadsheet

Please help me to count the number of successively fulfilled conditions.

I have a table with different metrics and their values per month. I also have a target value and condition per each metric:

Metric  Month   Target  Value   Expected Result
Metric1 Jan18   3.00%   2.00%   1
Metric1 Feb18   3.00%   2.50%   2
Metric1 Mar18   3.00%   2.30%   3
Metric1 Apr18   3.00%   3.50%   0
Metric1 May18   3.00%   3.40%   0
Metric1 Jun18   3.00%   2.00%   1
Metric2 Jan18   90.00%  95.00%  1
Metric2 Feb18   90.00%  93.00%  2
Metric2 Mar18   90.00%  80.00%  0
Metric2 Apr18   90.00%  91.00%  1
Metric2 May18   90.00%  92.00%  2
Metric2 Jun18   90.00%  93.00%  3

I need to count the number of months the condition is continuously met. E.G. Metric 1 should be less than 3%, and metric 2 should be more than 90%. Please see the expected result in the table.

Aucun commentaire:

Enregistrer un commentaire