0

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: Example1

The new version with additional table values: Example2

Thank You @player0 for the original version of this.

Jeremiah Thies
  • 35
  • 6

1 Answers1

2

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),""))

enter image description here

basic
  • 8,639
  • 2
  • 5
  • 23