# Formulas

Occasional Visitor

# Formulas

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%

0 Replies