Log In
Register
Home
July 11, 2017
Offline
Hi,
I'm trying to find the average rate of pay at each site for Supervision, there are 9 different Supervision roles so I have written an AVERAGEIFS formula to look at the key words Assistant, Supervisor & Floor, but the formula is not working and I'm not sure what I have done wrong. The data is from row 2 to 2382 and the 1st result should appear in cell D2384.
Supervisor
Assistant
Supervisor Extra Chargeable
Floor Supervisor - Xtr Chargeable
Floor Housekeeper
Floor Supervisor
PA Supervisor
Senior Supervisor
Night Supervisor
Any help would be greatly appreciated.
Regards
Paul
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Criteria entered in AVERAGEIFS work like an AND function. Only items for which all criteria are met will be included in the average calculation.
Obviously, no item will ever contain "assistent" AND *supervisor" AND *floor", unless there would an Assistent floor supervisor.
Better to create a pivot table (Site Name in the Rows area, Service in the Column area and Final Rate in the Values field, set to calculate Average.
Trusted Members
Moderators
November 1, 2018
Offline
If you have the FILTER function available, you could use:
=IFERROR(AVERAGE(FILTER($D$2:$D$2382,COUNT(SEARCH({"assistant","supervisor","floor"},$C$2:$C$2382)))),0)
If not, it's still achievable with formulas using a SUM(SUMIFS())/SUM(COUNTIFS()) construction.
July 11, 2017
Offline
Thanks Riny & Velouria
@Velouria: the formula works but gives me the average of all 2382 rows, I need to know the average for each of the 188 sites, so need to also lookup site in column A, I'm not sure how to add that to your formula
Thanks
Paul
Trusted Members
Moderators
November 1, 2018
Offline
You can add that in like this:
=IFERROR(AVERAGE(FILTER($D$2:$D$2382,COUNT(SEARCH({"assistant","supervisor","floor"},$C$2:$C$2382))*($a$2:$a$2382="site name"))),0)
July 11, 2017
Offline
Thanks Velouria,
I'm away at the moment, let me try this when I get back, will confirm all OK the,
Regards
Paul
July 11, 2017
Offline
Hi Velouria,
I have attached an updated the attached sheet,
When I add in all other services the old formula (cell C185) returns an average of all rows not the average of the requested rows which contains the words Assistant, Supervisor or Floor.
When I update with the new formula (Cell C184) to look up the average for a site, the new formula returns 0, I presume because it does not know which site to look for, it needs something that first looks at cell B184 then site name in column A
Thanks Paul
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Cahnge the formula to:
=FILTER($C$2:$C$181,($A$2:$A$181=B184)*BYROW(SEARCH({"Assistant","Supervisor","Floor"},$B$2:$B$181),LAMBDA(a,COUNT(a))))
and note that there are two you omitted the "Evening Supervisor" (rows 24 and 114) role in your example. That will change the outcome for Site 1 to 13.13 and 11.607 for Site 2.
1 Guest(s)
