Apr 09 2019 11:41 AM
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?
Apr 09 2019 02:21 PM
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.
Apr 09 2019 03:27 PM
@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!
Apr 10 2019 03:56 AM
@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
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.
Apr 09 2019 02:21 PM
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.