SOLVED
Home

Suggestions for organizing spreadsheet needed

%3CLINGO-SUB%20id%3D%22lingo-sub-420042%22%20slang%3D%22en-US%22%3ESuggestions%20for%20organizing%20spreadsheet%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-420042%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%20a%20spreadsheet%20to%20track%20the%20amount%20of%20time%20our%20technicians%20are%20at%20certain%20properties.%26nbsp%3B%20This%20spreadsheet%20is%20currently%20able%20to%20give%20me%20an%20overall%20average%20of%20time%20spent%20by%20all%20technicians%20at%20a%20property%20during%20the%20month%2C%20but%20I%20need%20a%20way%20to%20show%20the%20averages%20of%20each%20technician%20for%20each%20property.%26nbsp%3B%20Pivot%20tables%20have%20been%20suggested%2C%20but%20I%20can't%20seem%20to%20get%20it%20to%20work%20with%20the%20current%20layout.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-420042%22%20slang%3D%22en-US%22%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-420633%22%20slang%3D%22en-US%22%3ERe%3A%20Suggestions%20for%20organizing%20spreadsheet%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-420633%22%20slang%3D%22en-US%22%3E%3CP%3E%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%20%2C%20yes%2C%20that's%20hard%20to%20work%20with%20the%20data%20structured%20such%20way.%20Formula%20could%20be%20(in%20C43)%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(AVERAGEIF(D%2412%3AD%2442%2C%24B43%2CC%2412%3AC%2442)%2C0)%3C%2FPRE%3E%0A%3CP%3Edrag%20it%20down%20and%20copy%20column%20on%20other%20properties.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-421323%22%20slang%3D%22en-US%22%3ERe%3A%20Suggestions%20for%20organizing%20spreadsheet%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-421323%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20very%20much%20for%20your%20assistance.%26nbsp%3B%20The%20organization%20of%20my%20spreadsheet%20made%20getting%20this%20information%20tough%20and%20I%20didn't%20even%20know%20where%20to%20start.%26nbsp%3B%20Your%20formula%20got%20the%20result%20I%20needed!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-425509%22%20slang%3D%22en-US%22%3ERe%3A%20Suggestions%20for%20organizing%20spreadsheet%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-425509%22%20slang%3D%22en-US%22%3E%3CP%3E%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%20first%20thing%20is%20to%20avoid%20cells%20merging%20which%20gives%20a%20lot%20of%20side%20effects.%20Alternative%20is%20to%20format%20cells%20with%20Center%20Across%20Selection%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20219px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F107230i3DC98D401E46D576%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%3EAnother%20point%20it's%20always%20better%20to%20separate%20row%20input%20data%20and%20aggregations.%20As%20variant%20that%20could%20be%204%20columns%20with%20date%2C%20hours%2C%20name%20and%20property%20(two%20latest%20could%20be%20picked-up%20from%20drop-down%20lists)%2C%20another%20table%20with%20properties%20and%20goals%2C%20after%20that%20combine%20results%20in%20separate%20PivotTables%20or%20like.%20But%20concrete%20organization%20depends%20on%20your%20business%20rules%20as%20well%2C%20main%20idea%20is%20separation.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Tom_Clean_Sweep_4187
New Contributor

I have created a spreadsheet to track the amount of time our technicians are at certain properties.  This spreadsheet is currently able to give me an overall average of time spent by all technicians at a property during the month, but I need a way to show the averages of each technician for each property.  Pivot tables have been suggested, but I can't seem to get it to work with the current layout.

 

Any suggestions?

3 Replies
Solution

@Tom_Clean_Sweep_4187 , yes, that's hard to work with the data structured such way. Formula could be (in C43) 

=IFERROR(AVERAGEIF(D$12:D$42,$B43,C$12:C$42),0)

drag it down and copy column on other properties.

@Sergei Baklan Thank you very much for your assistance.  The organization of my spreadsheet made getting this information tough and I didn't even know where to start.  Your formula got the result I needed!

@Tom_Clean_Sweep_4187 , first thing is to avoid cells merging which gives a lot of side effects. Alternative is to format cells with Center Across Selection

image.png

Another point it's always better to separate row input data and aggregations. As variant that could be 4 columns with date, hours, name and property (two latest could be picked-up from drop-down lists), another table with properties and goals, after that combine results in separate PivotTables or like. But concrete organization depends on your business rules as well, main idea is separation.

Related Conversations