Home

Need help with a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1019703%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019703%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3EAt%20work%2C%20the%20people%20are%20working%20on%20their%20orders%20in%20teams%2C%20so%20me%20and%20my%20co-worker%20want%20to%20keep%20track%20of%20those%20teams%20to%20see%20if%20we%20can%20mix%20them%20up%20and%20make%20it%20more%20efficient.%3C%2FP%3E%3CP%3ESo%20my%20problem%20is.%3C%2FP%3E%3CP%3EEx%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CU%3ETeam%3C%2FU%3E%3C%2FTD%3E%3CTD%3E%3CU%3EStart%20time%3C%2FU%3E%3C%2FTD%3E%3CTD%3E%3CU%3EStop%20time%3C%2FU%3E%3C%2FTD%3E%3CTD%3E%3CU%3ETotal%20Time%3C%2FU%3E%3C%2FTD%3E%3CTD%3E%3CU%3EMin%3C%2FU%3E%3C%2FTD%3E%3CTD%3E%3CU%3ERows%3C%2FU%3E%3C%2FTD%3E%3CTD%3E%3CU%3EMin%2FRow%3C%2FU%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3E18%3A20%3A00%3C%2FTD%3E%3CTD%3E19%3A20%3A00%3C%2FTD%3E%3CTD%3E01%3A00%3A00%3C%2FTD%3E%3CTD%3E60%3C%2FTD%3E%3CTD%3E120%3C%2FTD%3E%3CTD%3E0%2C50%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET2%3C%2FTD%3E%3CTD%3E15%3A20%3A00%3C%2FTD%3E%3CTD%3E19%3A24%3A00%3C%2FTD%3E%3CTD%3E04%3A04%3A00%3C%2FTD%3E%3CTD%3E244%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E1%2C22%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET1%3C%2FTD%3E%3CTD%3E11%3A11%3A00%3C%2FTD%3E%3CTD%3E15%3A45%3A00%3C%2FTD%3E%3CTD%3E04%3A34%3A00%3C%2FTD%3E%3CTD%3E274%3C%2FTD%3E%3CTD%3E150%3C%2FTD%3E%3CTD%3E1%2C83%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET3%3C%2FTD%3E%3CTD%3E09%3A00%3A00%3C%2FTD%3E%3CTD%3E17%3A00%3A00%3C%2FTD%3E%3CTD%3E08%3A00%3A00%3C%2FTD%3E%3CTD%3E480%3C%2FTD%3E%3CTD%3E420%3C%2FTD%3E%3CTD%3E1%2C14%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20bottom%20of%20the%20document%20I%20want%20a%20function%20that%20looks%20for%20%22T1%22%20in%20the%20A-collumn%20and%20then%20creates%20an%20average%20on%20every%20%22min%2Frow%22%20of%20T1%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1019703%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1019809%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019809%22%20slang%3D%22en-US%22%3EThat%20is%20something%20that%20is%20easliy%20done%20using%20a%20pivot%20table.%3CBR%20%2F%3EInsert%2C%20Pivot%20table%3CBR%20%2F%3EDrag%20the%20Team%20field%20to%20the%20rows%20area%3CBR%20%2F%3EDrag%20the%20Min%2FRow%20field%20to%20the%20sigma%20area%3CBR%20%2F%3ERight-click%20the%20min%2Frow%20column%20in%20the%20pivot%20table%20and%20choose%20%22Summarize%20Values%20By%22%20and%20select%20%22Average%22.%20Done!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1019830%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1019830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396185%22%20target%3D%22_blank%22%3E%40Fredirk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20simply%20insert%20a%20Pivot%20Table%20and%20drag%20the%20Team%20field%20in%20the%20Rows%20area%20and%20the%20Min%2FRow%20field%20in%20the%20Values%20area%20and%20change%20the%20Value%20Field%20Settings%20to%20show%20the%20Average.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20go%20for%20the%20Pivot%20Table%20approach%2C%20it%20would%20be%20better%20if%20you%20convert%20your%20data%20into%20an%20Excel%20Table%20and%20then%20insert%20a%20Pivot%20Table%20based%20on%20this%20table%20so%20that%20when%20you%20add%2Fdelete%20data%20in%20the%20table%2C%20once%20you%20Refresh%20the%20Pivot%20Table%20it%20will%20always%20show%20you%20the%20updated%20report.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20refer%20to%20the%20attached%20which%20contains%20a%20Pivot%20Table%20solution%20and%20the%20formula%20solution%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Fredirk
New Contributor

Hello!

At work, the people are working on their orders in teams, so me and my co-worker want to keep track of those teams to see if we can mix them up and make it more efficient.

So my problem is.

Ex

TeamStart timeStop timeTotal TimeMinRowsMin/Row
T118:20:0019:20:0001:00:00601200,50
T215:20:0019:24:0004:04:002442001,22
T111:11:0015:45:0004:34:002741501,83
T309:00:0017:00:0008:00:004804201,14

 

In the bottom of the document I want a function that looks for "T1" in the A-collumn and then creates an average on every "min/row" of T1

2 Replies
That is something that is easliy done using a pivot table.
Insert, Pivot table
Drag the Team field to the rows area
Drag the Min/Row field to the sigma area
Right-click the min/row column in the pivot table and choose "Summarize Values By" and select "Average". Done!

@Fredirk 

You may simply insert a Pivot Table and drag the Team field in the Rows area and the Min/Row field in the Values area and change the Value Field Settings to show the Average.

 

If you go for the Pivot Table approach, it would be better if you convert your data into an Excel Table and then insert a Pivot Table based on this table so that when you add/delete data in the table, once you Refresh the Pivot Table it will always show you the updated report.

 

Please refer to the attached which contains a Pivot Table solution and the formula solution as well.

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 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