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
Tabs and Dark Mode
cjc2112 in Discussions on
47 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies