Formulas

Occasional Visitor

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.

 

 

3015L3015R3035L3035R3039L3055R3059R3075L3075R3205L3205RInspectErgo trainedErgo totalPercentage trained
FramesFramesHeadrestsHeadrestsRSC BuildPre-MarriageRSC BuildMarriageMarriageTransferTransferInspect
14-Jan-2114-Jan-2123-Oct-2023-Oct-2023-Oct-2024-Aug-2123-Oct-203-Jun-217-Dec-2026-Jul-2126-Jul-213-Sep-21
20-May-2220-May-2220-May-2220-May-2220-May-2220-May-2220-May-2220-May-2220-May-2220-May-2220-May-22 111292%
16-Mar-212-Mar-2216-Mar-2116-Mar-2116-Mar-218-Feb-2216-Mar-218-Feb-2216-Mar-212-Mar-222-Mar-22 111292%
             12 
22-Oct-2222-Oct-22Training22-Oct-22Training22-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-22Training 81173%
22-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-221111100%
20-May-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-22 1111100%
Training9-Nov-22TrainingTrainingTrainingTrainingTrainingTrainingTraining3-Nov-224-Nov-22 31127%
22-Oct-2222-Oct-22Training22-Oct-22Training22-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-22 91182%
22-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-2222-Oct-22 1111100%
1-Nov-222-Nov-222-Nov-223-Nov-224-Nov-225-Nov-226-Nov-227-Nov-228-Nov-222-Nov-222-Nov-22 1111100%
 27-Oct-22    27-Oct-22  27-Oct-2227-Oct-22 41136%
    9-Nov-222-Nov-229-Nov-22  9-Nov-229-Nov-22 51145%
TrainingTrainingTrainingTrainingTrainingTrainingTrainingTrainingTrainingTrainingTraining  11 
Training9-Nov-22TrainingTrainingTrainingTraining9-Nov-22TrainingTrainingTrainingTraining 21118%
             11 
             11 
             11 
             11 
             11 
81168791188111017517642.61%

 

0 Replies