Redistribution of weightage automate

%3CLINGO-SUB%20id%3D%22lingo-sub-2129089%22%20slang%3D%22en-US%22%3ERedistribution%20of%20weightage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2129089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CBR%20%2F%3EThanks%20to%20Sergei%20Baklan%20and%20mathetes%20for%20their%20help%20on%20this%20KPI%20tracking%20file.%20I%20have%20another%20problem.%20I%20would%20like%20to%20further%20automate%20the%20attached%20Excel%20KPI%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20whenever%20a%20person%20updates%20NA%20for%20month%20target%20and%20results%20(when%20there%20is%20no%20target%20and%20no%20result)%2C%20an%20NA%20is%20generated%20in%20both%20Month%20todate%20(MTD)%20and%20Year%20to%20Date%20(YTD)%20rating%20and%20Score.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20If%20this%20happens%20for%20a%20few%20KPIs%2C%20the%20total%20corresponding%20weightage%20has%20to%20be%20equally%20redistributed%20for%20the%20remaining%20KPIs%20including%20the%20Profitability%20(which%20have%20target%20and%20results)%2C%20which%20is%20shown%20in%20cell%20D22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3)%20This%20equal%20weightage%20is%20then%20added%20to%20the%20calculated%20weightage%20in%20both%20MTD%20Score%20and%20YTD%20Score.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuestion%3A%20I%20have%20manually%20included%20the%20formula%20in%20the%20attached%20Excel%20file.%20How%20do%20I%20automate%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2129089%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2129114%22%20slang%3D%22en-US%22%3ERe%3A%20Redistribution%20of%20weightage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2129114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928871%22%20target%3D%22_blank%22%3E%40Chan_Tze_Leong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor


Thanks to Sergei Baklan and mathetes for their help on this KPI tracking file. I have another problem. I would like to further automate the attached Excel KPI file.

 

1) whenever a person updates NA for month target and results (when there is no target and no result), an NA is generated in both Month todate (MTD) and Year to Date (YTD) rating and Score.

 

2) If this happens for a few KPIs, the total corresponding weightage has to be equally redistributed for the remaining KPIs including the Profitability (which have target and results), which is shown in cell D22.

 

3) This equal weightage is then added to the calculated weightage in both MTD Score and YTD Score.

 

Question: I have manually included the formula in the attached Excel file. How do I automate this?

2 Replies

Any suggestions/ideas?