Formulas

[mshawley]

I am having some issues with how my work sheet is pulling data. 

What I have on the far right:

Ergo Trained (number of jobs operators can do) there are 16 spaces to add operators. 

Ergo total (11 jobs online in rotation, has to reflect 11) 

Percentage Trained. (Percentage based on # of jobs known)

Then my Totals. 

The issue I am running into is I have 16 Total spots to fill on my line. 

I am only using 11 of the 16. That means I have 5 cells that are pulling data from the Ergo Total column as (11) when in the Ergo trained I have no data. It's not showing a true average of my operator's only full line.  

I do not want to count data in the Ergo Total Column when I have no data in the Ergo Trained Column. I have to keep the required job number data (11) in the Ergo Total Column even if 0 in the Ergo Trained Column. 

How can I formulate my work sheet to show the average percentage based on Only imputed data greater then 0 in the Ergo trained Column even though I have some unused rows with (11) in the Ergo Total Colum.   

Here are the Formulas I am using so far. 

I have conditional formatting also. 

Green for a "sign off date"

Yellow for "Training" 

Red for "N.A."

Thanks for any help.

Ergo Trained Formula (0-11 Max)
=COUNT(E118:O118)

Ergo Total (This will be constant 11 counting columns) 
=COUNTA($E$114:$O$114)

This is Percentage Trained (This is % of 11 Jobs learned)
=IFERROR(Q119/R119, "-")

Ergo Trained Totals (Counting all 16 spots)
=SUM(Q118:Q133)

Ergo Totals (Counting 16 spots but have 11 total with useable data)
=SUM(R118:R133)

Totals averaging 
=Q134/R134

Ergo Total I do not want to count data in this row(s) if there is a 0 in Ergo Trained.

3015L

3015R

3035L

3035R

3039L

3055R

3059R

3075L

3075R

3205L

3205R

Inspect

Ergo trained

Ergo total

Percentage trained

Frames

Frames

Headrests

Headrests

RSC Build

Pre-Marriage

RSC Build

Marriage

Marriage

Transfer

Transfer

Inspect

14-Jan-21

14-Jan-21

23-Oct-20

23-Oct-20

23-Oct-20

24-Aug-21

23-Oct-20

3-Jun-21

7-Dec-20

26-Jul-21

26-Jul-21

3-Sep-21

20-May-22

20-May-22

20-May-22

20-May-22

20-May-22

20-May-22

20-May-22

20-May-22

20-May-22

20-May-22

20-May-22

11

12

92%

16-Mar-21

2-Mar-22

16-Mar-21

16-Mar-21

16-Mar-21

8-Feb-22

16-Mar-21

8-Feb-22

16-Mar-21

2-Mar-22

2-Mar-22

11

12

92%

12

22-Oct-22

22-Oct-22

Training

22-Oct-22

Training

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

Training

8

11

73%

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

11

11

100%

20-May-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

11

11

100%

Training

9-Nov-22

Training

Training

Training

Training

Training

Training

Training

3-Nov-22

4-Nov-22

3

11

27%

22-Oct-22

22-Oct-22

Training

22-Oct-22

Training

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

9

11

82%

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

22-Oct-22

11

11

100%

1-Nov-22

2-Nov-22

2-Nov-22

3-Nov-22

4-Nov-22

5-Nov-22

6-Nov-22

7-Nov-22

8-Nov-22

2-Nov-22

2-Nov-22

11

11

100%

27-Oct-22

27-Oct-22

27-Oct-22

27-Oct-22

4

11

36%

9-Nov-22

2-Nov-22

9-Nov-22

9-Nov-22

9-Nov-22

5

11

45%

Training

Training

Training

Training

Training

Training

Training

Training

Training

Training

Training

11

Training

9-Nov-22

Training

Training

Training

Training

9-Nov-22

Training

Training

Training

Training

2

11

18%

11

11

11

11

11

8

11

6

8

7

9

11

8

8

11

10

1

75

176

42.61%