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.

 

[sai85]

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

<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))))))))))))[ php]

=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))))))))))))

Try this

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))))))))))))[>

 

[RasGhul]

Hi Sai

You can paste your formula into a reply with the # button

 

[sai85]

Hi Sai

You can paste your formula into a reply with the # button

Thanks Ras. I will do that from next time.

 

[Meliphagous]

Hi Sai

You can paste your formula into a reply with the # button

Thank you a lot, RasGhul.
I'm messing with the whole mess of my Techgara site.
It helps me a lot.

 

[Firasath]

Dear Sai,

I don't think above formula is a feasible solution for my requirement as every month we need to modify the formula by adding one more month's cell reference in to it. I think the solution should be there in VBA.

Request anybody who can support in developing cede for the same.