Home

How to get an average of non-adjacent cells from one column based on data in another column

%3CLINGO-SUB%20id%3D%22lingo-sub-410732%22%20slang%3D%22en-US%22%3EHow%20to%20get%20an%20average%20of%20non-adjacent%20cells%20from%20one%20column%20based%20on%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-410732%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20my%20first%20time%20posting%20and%20I%20hope%20I'm%20in%20the%20right%20area....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20have%20is%20two%20columns%20of%20data%20for%20a%20given%20month.%26nbsp%3B%20One%20column%20contains%20employees%20initials%20and%20the%20other%20contains%20the%20amount%20of%20time%20(in%20minutes)%20that%20it%20took%20them%20to%20perform%20a%20specific%20job.%26nbsp%3B%20Is%20there%20a%20function%20available%20that%20would%20allow%20me%20to%20easily%20get%20the%20averages%20for%20each%20employee%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20screen%20shot%20of%20what%20the%20columns%20look%20like%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%2022px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F106195iC826FE142795999C%2Fimage-size%2Fsmall%3Fv%3D1.0%26amp%3Bpx%3D200%22%20alt%3D%22Screen%20Shot%202019-04-08%20at%2011.59.22%20AM.png%22%20title%3D%22Screen%20Shot%202019-04-08%20at%2011.59.22%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20prefer%20to%20keep%20the%20data%20in%20its%20original%20order%20(as%20sorting%20effects%20the%20dates%20that%20the%20data%20is%20assigned%20to)%20and%20would%20like%20something%20where%20under%20the%20column%20each%20employee's%20initials%20are%20listed%20next%20to%20their%20average%20during%20the%20period.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20something%20that%20can%20happen%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-410732%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-411514%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20an%20average%20of%20non-adjacent%20cells%20from%20one%20column%20based%20on%20data%20in%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-411514%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F316504%22%20target%3D%22_blank%22%3E%40Tom_Clean_Sweep_4187%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20I%20catched%20all%20your%20requirements.%20As%20variant%20that%20could%20be%20PivotTable.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20537px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F106218iD0CB81620AE89B0C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ETo%20keep%20sorting%20in%20order%20as%20initial%20appears%20it's%20better%20to%20create%20helper%20column%20with%20ID%3As%20for%20initials%2C%20I%20took%20that%20pattern%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.automateexcel.com%2Fadvanced-excel-skills%2Fcreate-unique-identifiers%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.automateexcel.com%2Fadvanced-excel-skills%2Fcreate-unique-identifiers%2F%3C%2FA%3E%20for%20it.%20After%20that%20you%20pivot%20your%20data%20selecting%20Average%20for%20aggregating%20the%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Tom_Clean_Sweep_4187
New Contributor

This is my first time posting and I hope I'm in the right area....

 

What I have is two columns of data for a given month.  One column contains employees initials and the other contains the amount of time (in minutes) that it took them to perform a specific job.  Is there a function available that would allow me to easily get the averages for each employee?

 

Here's a screen shot of what the columns look like:

Screen Shot 2019-04-08 at 11.59.22 AM.png

 

I would prefer to keep the data in its original order (as sorting effects the dates that the data is assigned to) and would like something where under the column each employee's initials are listed next to their average during the period.

 

Is this something that can happen?

1 Reply

Hi @Tom_Clean_Sweep_4187 ,

 

Not sure I catched all your requirements. As variant that could be PivotTable.

image.png

To keep sorting in order as initial appears it's better to create helper column with ID:s for initials, I took that pattern https://www.automateexcel.com/advanced-excel-skills/create-unique-identifiers/ for it. After that you pivot your data selecting Average for aggregating the time.

Related Conversations