So for the table Shift Stats, I want to have that populated with the average wages made from that day, position and shift. Under the table Tips Made, I have a column "Wage," that column calculates my wage like this (hours * base wage)+tips.
This way I will be able to see on average how much money I make as an SA working PM shift on Fridays vs Saturdays, and everything else.
Here's one way:
I've added two columns to your data table (Table 1 in the example) These provide the condition values needed by the
two …IFS functions used in the formula on Table 2;
Column B of Table 1 is calculated by the formula below, entered in B2, and filled down:
B2: IFERROR(DAYNAME(WEEKDAY(A2,1)),"")
Column E is calculated by the formula below, entered in E2 and filled down:
E2: D2&"-"&C2
Values in columns F,G, H, and I are omitted, as they do not enter into the calculations discussed here.
Values in Column j are the ones shown in column H of your example. The formulas on your table will adjust to accommodate the two added columns.
Table 2 in the example is your Shift Stats table.
Table 2 contains one formula, entered in B2, then filled right to column G and down to row 9.
The formula in B2 is displayed below the two tables.
Table 2::B2: IF(COUNTIFS(Table 1::$B,$A3,Table 1::$E,B$2)<1,"",AVERAGEIFS(Table 1::$J,Table 1::$B,$A3,Table 1::$E,B$2))
The part of the formula in normal weight type is a switch that Counts the number of entries fitting each pair of conditions (Day AND position-shift. IF that count is less than one, IF inserts a null string in the celll containing the formula and Number passes on to the next cell.
IF the count is 1 or more, the core part of the formula (shown in bold) calculates the average of the entries matching each pair of conditions, and places that in the cell for that condition pair.
Regards,
Barry
Note: with the data set provided, each 'average' will be the same as one of the values in the last column of Table 1. No record in the supplied set involved working the same position on the same shift of the same day of the week.I did revise one of the entries to test this (successfully), but returned it to it's original state before taking the screen shot.
B.
Here's one way:
I've added two columns to your data table (Table 1 in the example) These provide the condition values needed by the
two …IFS functions used in the formula on Table 2;
Column B of Table 1 is calculated by the formula below, entered in B2, and filled down:
B2: IFERROR(DAYNAME(WEEKDAY(A2,1)),"")
Column E is calculated by the formula below, entered in E2 and filled down:
E2: D2&"-"&C2
Values in columns F,G, H, and I are omitted, as they do not enter into the calculations discussed here.
Values in Column j are the ones shown in column H of your example. The formulas on your table will adjust to accommodate the two added columns.
Table 2 in the example is your Shift Stats table.
Table 2 contains one formula, entered in B2, then filled right to column G and down to row 9.
The formula in B2 is displayed below the two tables.
Table 2::B2: IF(COUNTIFS(Table 1::$B,$A3,Table 1::$E,B$2)<1,"",AVERAGEIFS(Table 1::$J,Table 1::$B,$A3,Table 1::$E,B$2))
The part of the formula in normal weight type is a switch that Counts the number of entries fitting each pair of conditions (Day AND position-shift. IF that count is less than one, IF inserts a null string in the celll containing the formula and Number passes on to the next cell.
IF the count is 1 or more, the core part of the formula (shown in bold) calculates the average of the entries matching each pair of conditions, and places that in the cell for that condition pair.
Regards,
Barry
Note: with the data set provided, each 'average' will be the same as one of the values in the last column of Table 1. No record in the supplied set involved working the same position on the same shift of the same day of the week.I did revise one of the entries to test this (successfully), but returned it to it's original state before taking the screen shot.
B.
The "wages" column does not equal (hours*base wage)+tips. The "total" column does. The "Wages" column =total/hours. You might consider changing the name to "effective hourly wage" to distinguish it from your actual wages.
The function I think you are looking for is AVERAGEIFS. But if you want to get the average tip for a specific position on a specific shift (AM/PM) on a given day of the week, you need all four of those things in your table. You have the first three but not the day of the week. Add a new column for the day of the week. The formula in that column will be =DAYNAME(A). After you have that done, give the AVERAGIFS formula a shot.
How do I get average wage based on the day, shift, and position worked?
