Show target missed for last how many months

[Firasath]

Hi,

I have a KPI dashboard where I have data from 2016 onwards. Each KPI data is organized in each row. KPI name is placed in A column and it's target in B column followed by monthly achievements.

I would like to know for how many consecutive previous months the target is not achieved for each KPI. Suppose I have a data up to Apr-19 and for one of the KPI the target was not achieved from Nov-18 onwards, then the value should return as 6 and for another KPI target is achieved in Apr-19 then the result should be 0.

Is there any formula or VBA Code I can use to achieve this. Kindly advise.

 

[RasGhul]

Can you post some sample data?

 

[Firasath]

https://1drv.ms/x/s!AiGStKw9OQ0GoAnSRIbwffqTVPsT

Please find above the link to the sample file. In the last column I worked manually to calculate number of consecutive months target has been missed for the KPI. I want to automate same.

I have the data for around 40 months now (from 2016 onwards) and every month data is added. The formula/code should take this aspect in to consideration. It should skip blank columns.

Regards.

 

[Firasath]

Just wondering, whether there is no solution for this.

 

[RasGhul]

In cell O2;

=COUNTIFS(C2:N2,"<"&B2)

 

[Firasath]

In cell O2;

=COUNTIFS(C2:N2,"<"&B2)

Dear RasGul,

It seems you didn't understand the requirement properly. Kindly read my question properly and refer the attched file and observe the O column results in it.

The requirement is not just to calculate the number month target is missed rather the requirement is to to see whether target is achieved in last month, if achieved then result would be 0 times otherwise it should calculate for how many last consecutive months the target is missed.

Hope this time I am to articulate the question properly. Kindly advise.

 

[RasGhul]

I cant seem to get a frequency formula to work for this problem. Hopefully someone can work it out for you.

On research there is a FREQUENCY(IF( combination that should be able to give you a solution although I cant replicate your suggested values.

 

[sai85]

Dear RasGul,

It seems you didn't understand the requirement properly. Kindly read my question properly and refer the attched file and observe the O column results in it.

The requirement is not just to calculate the number month target is missed rather the requirement is to to see whether target is achieved in last month, if achieved then result would be 0 times otherwise it should calculate for how many last consecutive months the target is missed.

Hope this time I am to articulate the question properly. Kindly advise.

Hi Firasath,

Kindly try the below solution:

=IF(N2>=B2,0,IF(M2<=B2,1,IF(L2<=B2,2,IF(K2<=B2,3,IF(J2<=B2,4,IF(I2<b2,5,if(h2<b2,6,if(g2<b2,7,if(f2<b2,8,if(e2<b2,9,if(d2<b2,10,if(c2<b2,11,12))))))))))))<b2,5,if(h2<b2,6,if(g2<b2,7,if(f2<b2,8,if(e2<b2,9,if(d2<b2,10,if(c2<b2,11,12))))))))))))<b2,5,if(h2<b2,6,if(g2<b2,7,if(f2<b2,8,if(e2<b2,9,if(d2<b2,10,if(c2<b2,11,12))))))))))))<b2,5,if(h2<b2,6,if(g2<b2,7,if(f2<b2,8,if(e2<b2,9,if(d2<b2,10,if(c2<b2,11,12))))))))))))<strong>

<b2,5,if(h2<b2,6,if(g2<b2,7,if(f2<b2,8,if(e2<b2,9,if(d2<b2,10,if(c2<b2,11,12))))))))))))
And let me know if this works for you as per your requirement.

Regards,
Sai</b2,5,if(h2<b2,6,if(g2<b2,7,if(f2<b2,8,if(e2<b2,9,if(d2<b2,10,if(c2<b2,11,12))))))))))))
</b2,5,if(h2<b2,6,if(g2<b2,7,if(f2<b2,8,if(e2<b2,9,if(d2<b2,10,if(c2<b2,11,12))))))))))))<b2,5,if(h2<b2,6,if(g2<b2,7,if(f2<b2,8,if(e2<b2,9,if(d2<b2,10,if(c2<b2,11,12))))))))))))<b2,5,if(h2<b2,6,if(g2<b2,7,if(f2<b2,8,if(e2<b2,9,if(d2<b2,10,if(c2<b2,11,12))))))))))))<b2,5,if(h2<b2,6,if(g2<b2,7,if(f2<b2,8,if(e2<b2,9,if(d2<b2,10,if(c2<b2,11,12))))))))))))<strong>

 

[Firasath]

Thanks RasGhul, even I tried to implement the Frequency formula but couldn't succeed. But I'm sure there would be solution in VBA for the same.

Request anyone who can support on the same.

 

[sai85]

I am sorry i dont know how to paste the entire formula here. Only half is coming here.