Creating an baseline Assessment

%3CLINGO-SUB%20id%3D%22lingo-sub-3250992%22%20slang%3D%22en-US%22%3ECreating%20an%20baseline%20Assessment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3250992%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20build%20a%20baseline%20people%20development%20tool.%20The%20intent%20is%20to%20have%20people%20do%20a%20self-assessment%20by%20choosing%20a%20drop-down%20menu%20where%20the%20answer%20is%20Y%2C%20N%2C%20or%20O.%20Y%20meaning%20that%20skill%20is%20done%2C%20N%20meaning%20not%20done%20and%20O%20meaning%20it%20works%20in%20progress.%20I%20need%20a%20separate%20tab%20with%20counts%20the%20Y%20and%20the%20N%20and%20O%20to%20flag%20green%2C%20red%20or%20orange%20with%20a%20percentage.%20Don't%20know%20where%20to%20start.%20The%20spreadsheets%20are%20built%20but%20I%20am%20missing%20how%20to%20create%20this%20link.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3250992%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3253920%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20baseline%20Assessment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3253920%22%20slang%3D%22en-US%22%3EI%20thought%20same.%20But%20I%20have%20struggled%20to%20replicate%20your%20formula%20and%20make%20it%20work.%20I%20sent%20you%20my%20worksheet%20if%20you%20could%20help%20with%20it%20please.%20This%20for%20you%20expertise%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3253914%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20baseline%20Assessment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3253914%22%20slang%3D%22en-US%22%3EIf%20it's%20left%20blank%20(i.e.%2C%20no%20response)%20then%20the%20conditional%20formatting%20leaves%20the%20cell%20white%20(or%20whatever%20you%20have%20set%20as%20default%2C%20normally%20white)....so%20you%20don't%20need%20to%20add%20a%20%22blank%22%20to%20the%20drop%20down.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3253072%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20baseline%20Assessment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3253072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3A%20This%20is%20what%20I%20have%20been%20looking%20for.%20Just%20struggling%20to%20implement%20on%20my%20sheet.%20Thanks%20a%20million%20you%20are%20a%20genius.%20I%20now%20need%20to%20add%20one%20more%20condition%20to%20the%20drop-down%20of%20selections%20(Y%2C%20O%2C%20N%2C%20and%20a%20blank).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3252003%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20baseline%20Assessment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3252003%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1327680%22%20target%3D%22_blank%22%3E%40DNS11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20what%20I%20think%20you're%20looking%20for.%20Now%20I%20see%20that%20it's%20one%20person%2C%20multiple%20competencies%2C%20multiple%20levels.%20%26nbsp%3B(But%20I%20don't%20see%20any%20reference%20anywhere%20to%20those%20percentages.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20see%20what's%20involved%20look%20at%20the%20Conditional%20Formatting%20dialog%20box%2C%20accessed%20two%20ways.%20Either%20from%20the%20Home%20tool%20bar%2C%20or%20the%20Format%20menu%20as%20shown%20below.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1646839999572.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F354485iADAC9C31E39DF02E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1646839999572.png%22%20alt%3D%22mathetes_0-1646839999572.png%22%20%2F%3E%3C%2FSPAN%3ESelect%20Conditional%20Formatting%20to%20see%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1646840071321.png%22%20style%3D%22width%3A%20538px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F354487iF5C17EE5207FDB50%2Fimage-dimensions%2F538x230%3Fv%3Dv2%22%20width%3D%22538%22%20height%3D%22230%22%20role%3D%22button%22%20title%3D%22mathetes_1-1646840071321.png%22%20alt%3D%22mathetes_1-1646840071321.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3251935%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20baseline%20Assessment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3251935%22%20slang%3D%22en-US%22%3EI%20sent%20you%20a%20sample%20using%20your%20Training%20Tally%20example%20as%20I%20could%20find%20where%20to%20attach%20it%20here.%20Please%20check%20your%20junk%20mail.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3251814%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20baseline%20Assessment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3251814%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1327680%22%20target%3D%22_blank%22%3E%40DNS11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOK%2C%20we%20need%20to%20back%20up%20here.%20With%20these%20percentage%20figures%20coming%20into%20the%20picture%20you%20are%20adding%20to%20the%20situation%20you%20first%20described.%20So%20could%20I%20ask%20you%20to%20start%20from%20scratch%20and%20explain%20what%20you're%20doing%20more%20fully.%20It's%20sounding%20now%20like%20you%20may%20have%20a%20person%20self-assessing%20on%20multiple%20skills%20(which%20wasn't%20explicit%20in%20your%20starting%20post)....%20So%20in%20that%20case%2C%20you're%20looking%20for%20a%20summary%20of%20multiple%20skills%20for%20each%20individual%20person%2C%20rather%20than%20the%20summary%20I%20gave%20you%20which%20is%20more%20of%20a%20summary%20of%20a%20single%20skill%20but%20multiple%20persons.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20post%20an%20image%20of%20your%20existing%20spreadsheet%2C%20the%20one%20you%20have%20developed.%20Or%20describe%20it%20more%20fully.%20Or%20post%20it%20in%20Google%20Sheets%20or%20OneDrive%20and%20give%20us%20the%20link%20to%20access%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3251270%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20baseline%20Assessment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3251270%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20interest%20in%20this.%20What%20I%20need%20to%20do%20is%20make%20the%20red%2C%20green%20and%20amba%20come%20up%20on%20the%20summary%20tab%20on%20the%20corresponding%20skill.%20If%20the%20person%20says%20Y%20against%20as%20a%20skill%20they%20are%20attributed%2080%25%20and%20that%20is%20a%20green.%20If%20they%20say%20N%20they%20are%20given%2025%25%20which%20is%20a%20red%20and%20when%20they%20say%20O%20which%20is%20for%20work%20in%20progress%20they%20are%20attributed%2050%25%20corresponding%20colour%20for%20that%20cell%20in%20the%20summary%20turns%20to%20amba.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3251009%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20an%20baseline%20Assessment%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3251009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1327680%22%20target%3D%22_blank%22%3E%40DNS11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I've%20done%20a%20partial%20solution%2C%20a%20separate%20tab%20with%20a%20count%20of%20how%20many%20have%20selected%20Y%2C%20N%20or%20O.%3C%2FP%3E%3CP%3EThe%20formula%20for%20that%20is%3C%2FP%3E%3CP%3E%3DCOUNTIF(Roster!%24B%244%3A%24B%2420%2CTally!C3)%3C%2FP%3E%3CP%3EI%20then%20calculate%20the%20percent%20that%20applies%20to%20each%20response.%3C%2FP%3E%3CP%3E%3DD3%2FSUM(%24D%243%3A%24D%245)%3C%2FP%3E%3CP%3EThe%20actual%20spreadsheet%20is%20attached%2C%20but%20here's%20an%20image%20of%20that%20portion%20that%20contains%20those%20summaries.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1646781550133.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F354059i257A2CEE786CA51D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1646781550133.png%22%20alt%3D%22mathetes_0-1646781550133.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhat%20is%20not%20clear%20from%20your%20first%20description%20is%20how%20you%20want%20those%20colors%20to%20be%20applied%20and%20where.%20Maybe%20you%20could%20spell%20that%20out%20a%20bit%20more.%20If%20it's%20just%20Y%3DGreen%2C%20N%3DRed%2C%20O%3DOrange%2C%20then%20all%20you%20need%20to%20do%20is%20set%20the%20background%20colors.%20But%20if%20you're%20asking%20for%20smarter%20%22Conditional%20Formatting%2C%22%20then%20you%20need%20to%20spell%20out%20a%20bit%20more%20fully%20what%20condition%20calls%20for%20what%20color%2C%20and%20where.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Dear all,

I am trying to build a baseline people development tool. The intent is to have people do a self-assessment by choosing a drop-down menu where the answer is Y, N, or O. Y meaning that skill is done, N meaning not done and O meaning it works in progress. I need a separate tab with counts the Y and the N and O to flag green, red or orange with a percentage. Don't know where to start. The spreadsheets are built but I am missing how to create this link.

8 Replies

@DNS11 

 

So I've done a partial solution, a separate tab with a count of how many have selected Y, N or O.

The formula for that is

=COUNTIF(Roster!$B$4:$B$20,Tally!C3)

I then calculate the percent that applies to each response.

=D3/SUM($D$3:$D$5)

The actual spreadsheet is attached, but here's an image of that portion that contains those summaries.

mathetes_0-1646781550133.png

What is not clear from your first description is how you want those colors to be applied and where. Maybe you could spell that out a bit more. If it's just Y=Green, N=Red, O=Orange, then all you need to do is set the background colors. But if you're asking for smarter "Conditional Formatting," then you need to spell out a bit more fully what condition calls for what color, and where.

 

@mathetes 

Thanks for the interest in this. What I need to do is make the red, green and amba come up on the summary tab on the corresponding skill. If the person says Y against as a skill they are attributed 80% and that is a green. If they say N they are given 25% which is a red and when they say O which is for work in progress they are attributed 50% corresponding colour for that cell in the summary turns to amba. 

@DNS11 

 

OK, we need to back up here. With these percentage figures coming into the picture you are adding to the situation you first described. So could I ask you to start from scratch and explain what you're doing more fully. It's sounding now like you may have a person self-assessing on multiple skills (which wasn't explicit in your starting post).... So in that case, you're looking for a summary of multiple skills for each individual person, rather than the summary I gave you which is more of a summary of a single skill but multiple persons.

 

Could you post an image of your existing spreadsheet, the one you have developed. Or describe it more fully. Or post it in Google Sheets or OneDrive and give us the link to access it.

I sent you a sample using your Training Tally example as I could find where to attach it here. Please check your junk mail.

@DNS11 

 

Here's what I think you're looking for. Now I see that it's one person, multiple competencies, multiple levels.  (But I don't see any reference anywhere to those percentages.)

 

To see what's involved look at the Conditional Formatting dialog box, accessed two ways. Either from the Home tool bar, or the Format menu as shown below.

mathetes_0-1646839999572.pngSelect Conditional Formatting to see this:

mathetes_1-1646840071321.png

 

 

@mathetes : This is what I have been looking for. Just struggling to implement on my sheet. Thanks a million you are a genius. I now need to add one more condition to the drop-down of selections (Y, O, N, and a blank). 

 

 

If it's left blank (i.e., no response) then the conditional formatting leaves the cell white (or whatever you have set as default, normally white)....so you don't need to add a "blank" to the drop down.
I thought same. But I have struggled to replicate your formula and make it work. I sent you my worksheet if you could help with it please. This for you expertise