search

w3c / maturity-model

Maturity Model
https://w3c.github.io/maturity-model/
Other
12 stars 7 forks source link

Excel Spreadsheet calculations incorrect #173

Closed clapierre closed 10 months ago

clapierre commented 11 months ago

When I had a couple teams fill out the latest Excel Spreadsheet A11yMaturityTemplate-20230719.xlsx

I was getting some bizarre results. Digging into the calculations I found a few problems.

Issue 1 All Sheets: "Not Applicable Total" expected "not applicable" however I put in "N/A". (Should switch the N/A dropdown to "not applicable"

Issue 2 Sheet 3.4 ICT Dev Life Cycle had 4 headings that were included in the "Yet to be Rated" total did not reduce the total by 4

Issue 3 All Dimensions Total # of Points available did not take into account Headings within the proof points. In the 3.4 ICT sheet this equates to 4 headings. (i.e. "Development", "User Experience", "Quality Review through Release", and "ICT Development Training"

Suggest we have a "Headings" data cell that will be used in both Issue 2&3 the "Yet to be Rated" and in the "Total # of Points Available"

Issue 4 "Total number of points available" Since this is a total over all the Stages this should be multiplied by 4 which will fix the Final Score which was previously above 100%

Issue 5 The Final Score should be formatted as a %

clapierre commented 11 months ago

Issue 3 is actually not a problem since COUNTA doesn't include those headings since there is no data within that cell range. However it doesn't take into account "not applicable" which should also be excluded.

clapierre commented 11 months ago

Sheet 3.4 Dev Lifecycle ICT "Yet to be Rated" totals on Launch, Integrated, Optimized Stage has incorrect Ranges Should be: D12:D40, F12:F40, and H12:H40

clapierre commented 11 months ago

Sheet 3.3 Support, all total ranges are incorrect should be B13-B48.

clapierre commented 11 months ago

Sheet 3.2 Knowledge&Skills "Total number of points available" incorrect range, should be: B12-B27 (not C12-C27)

clapierre commented 11 months ago

To fix not having a Div by 0 error suggest including =IF(C38>0,SUM(C39/C38),0) Also don't need to multiply by 100 just format the cell as a % with 1 decimal place.

clapierre commented 11 months ago

Sheet 3.5 Personnel Total Ranges incorrect should be B13:B26 (not B28) same for other stages

clapierre commented 11 months ago

3.6 Procurement Total Ranges incorrect should be B11:B31 (not B1) same for other stages Points Received total missing should be =SUM(C39,E39, G39, I39)

clapierre commented 10 months ago

With the above merge this addresses all issues found above, as I was fixing them as I found them.

clapierre commented 10 months ago

All has been addressed with latest template in github.