I'm working to update a google spreadsheet workbook with some additional functionality.
I have various items that flow through departments. I want to gauge the progress based on their status, chosen from a drop-down menu. This way we can see how far along on a job we are.
I have an example file with the working version and the new version I'm looking to solve: Example File
The original version has status words and associated values. Choose the status in a drop-down - then calculate an average for both the item and the department based on values and number of entries.
Entries include: Done = 100%, Active = 50%, Queue = 0%, None = ignore the value
In the new version, I want to include more status words and values in a table so I can update them based on project needs.
The formula in the working version is:
=ARRAYFORMULA(TRANSPOSE(IF(LEN(TRANSPOSE(C10:10)),MMULT(IFERROR((N(REGEXMATCH(TRANSPOSE(C10:O), "Done"))+N(REGEXMATCH(TRANSPOSE(C10:O),"Active"))/2)/MMULT(N(REGEXMATCH(TRANSPOSE(C10:O),"[^None]")),(ROW(C10:C)^0)),0),(ROW(C10:C)^0)), )))
A screenshot of the working version:
The new version with additional table values:
Thank You @player0 for the original version of this.
If you change value for status None
to #N/A
, you can use following formulas:
for columns
=ArrayFormula(IFERROR(MMULT(COLUMN($A$1:$J$1)^0,IFERROR(VLOOKUP(C16:O25,$A$2:$B$11,2,false),0))/MMULT(COLUMN($A$1:$J$1)^0,--(ISNUMBER(VLOOKUP(C16:O25,$A$2:$B$11,2,false)))),""))
for rows
=ArrayFormula(IFERROR(MMULT(IFERROR(VLOOKUP(C16:O25,$A$2:$B$11,2,false),0),ROW(A1:A13)^0)/MMULT(--(ISNUMBER(VLOOKUP(C16:O25,$A$2:$B$11,2,false))),ROW(A1:A13)^0),""))