Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1335650%22%20slang%3D%22en-US%22%3EFormula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1335650%22%20slang%3D%22en-US%22%3EHello%2C%20I%E2%80%99m%20developing%20a%20tool%20using%20excel%20that%20will%20facilitate%20the%20operations%20assessment%20process%20for%20a%20company%20and%20we%20are%20using%20a%20five%20point%20scale%3A%20Fully%20completed%20(FC)%2C%20partially%20completed%20PC%201%2C%20PC%202%2C%20PC%203%20and%20not%20completed.%20The%20assessments%20are%20done%20for%20the%20task%20level%2C%20but%20we%20need%20to%20get%20an%20average%20of%20the%20task%20assessments%20to%20determine%20the%20assessment%20of%20the%20overall%20project%2C%20which%20then%20will%20be%20averaged%20to%20determine%20the%20assessment%20of%20each%20unit.%20What%20I%20am%20asking%20is%20first%20what%20formula%20do%20I%20use%20to%20associate%20a%20numerical%20value%20to%20the%20task%20assessments.%20For%20example%20FC%3D5%2C%20PC1%3D4%2C%20PC2%3D3%2CPC3%3D2%2Cand%20NC%3D1.%20Secondly%20what%20formula%20can%20I%20use%20to%20get%20the%20average%20of%20the%20task%20assessments%20to%20assess%20the%20projects%20and%20then%20the%20formula%20to%20get%20the%20average%20of%20the%20project%20assessments%20to%20determine%20the%20unit%20assessment%20status.%20I%20know%20this%20is%20very%20confusing%2C%20but%20I%E2%80%99d%20like%20to%20get%20your%20suggestions%20if%20you%20could%20assist%2C%20thank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1335650%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1336330%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1336330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F635813%22%20target%3D%22_blank%22%3E%40Ddott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20assign%20a%20value%20based%20on%20the%20assessment%2C%20the%20attached%20workbook%20is%20using%20the%20SWITCH%20formula%20(but%20you%20could%20use%20nested%20IF%20statements).%3C%2FP%3E%3CP%3ETo%20average%20based%20on%20project%2C%20then%20by%20unit%2C%20you%20would%20use%20AVERAGEIFS%2C%20and%20AVERAGEIF.%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1336952%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1336952%22%20slang%3D%22en-US%22%3EThank%20you.%20It%20does%20help.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor
Hello, I’m developing a tool using excel that will facilitate the operations assessment process for a company and we are using a five point scale: Fully completed (FC), partially completed PC 1, PC 2, PC 3 and not completed. The assessments are done for the task level, but we need to get an average of the task assessments to determine the assessment of the overall project, which then will be averaged to determine the assessment of each unit. What I am asking is first what formula do I use to associate a numerical value to the task assessments. For example FC=5, PC1=4, PC2=3,PC3=2,and NC=1. Secondly what formula can I use to get the average of the task assessments to assess the projects and then the formula to get the average of the project assessments to determine the unit assessment status. I know this is very confusing, but I’d like to get your suggestions if you could assist, thank you.
2 Replies
Highlighted

@Ddott 

To assign a value based on the assessment, the attached workbook is using the SWITCH formula (but you could use nested IF statements).

To average based on project, then by unit, you would use AVERAGEIFS, and AVERAGEIF. 

Hope this helps.

Highlighted
Thank you. It does help.