Description: I have a pivot table based off a table with 3 columns: Date, Value, Is_Weekday. Is_Weekday is a boolean column. Value is just an arbitrary number. The pivot table groups the data in rows by Date and separates it in columns by Is_Weekday and uses SUM(Value) as the value. When I take the average of the Is_Weekday column, instead of computing the average of all the Values, it considers the Is_Weekday value as a value as well so the average gets thrown off by 1 element. Ex. column has values 9, 2 for Is_Weekday = FALSE, SUM is 11 which is good but AVERAGE is 3.67 which is wrong, it should be 5.5. Steps to Reproduce: 1. Create a table with 3 columns: Date, Value, Is_Weekday. Assign arbitrary values to Value 2. Create a pivot table which groups the data in rows by Date and separates it in columns by Is_Weekday and uses SUM(Value) as the value 3. Compute the AVERAGE of the Is_Weekday = FALSE column (or the Is_Weekday = TRUE column) and see that it is off by 1 element Actual Results: AVERAGE includes boolean value as an element, so instead of taking the SUM of X elements and dividing by X, it takes the SUM of X elements and divides by X + 1 Expected Results: AVERAGE should include boolean value as an element, it should equal SUM of X elements divided by X Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36
Created attachment 136173 [details] Sheet with table and pivot table demonstrating average bug See F3 and F5 in Sheet 2
Minor mistake: labels in pivot table sheet for SUM/AVERAGE calculations (column E) should be flipped. SUM WEEKEND should be SUM WEEKDAY, AVERAGE WEEEKEND should be AVERAGE WEEKDAY, ...
(In reply to Michael Viveros from comment #0) > Description: > I have a pivot table based off a table with 3 columns: Date, Value, > Is_Weekday. Is_Weekday is a boolean column. Value is just an arbitrary > number. The pivot table groups the data in rows by Date and separates it in > columns by Is_Weekday and uses SUM(Value) as the value. When I take the > average of the Is_Weekday column, instead of computing the average of all > the Values, it considers the Is_Weekday value as a value as well so the > average gets thrown off by 1 element. > > Ex. column has values 9, 2 for Is_Weekday = FALSE, SUM is 11 which is good > but AVERAGE is 3.67 which is wrong, it should be 5.5. > > Steps to Reproduce: > 1. Create a table with 3 columns: Date, Value, Is_Weekday. Assign arbitrary > values to Value > 2. Create a pivot table which groups the data in rows by Date and separates > it in columns by Is_Weekday and uses SUM(Value) as the value > 3. Compute the AVERAGE of the Is_Weekday = FALSE column (or the Is_Weekday = > TRUE column) and see that it is off by 1 element > > Actual Results: > AVERAGE includes boolean value as an element, so instead of taking the SUM > of X elements and dividing by X, it takes the SUM of X elements and divides > by X + 1 > > Expected Results: > AVERAGE should include boolean value as an element, it should equal SUM of X > elements divided by X > > > Reproducible: Always > > User Profile Reset: No > > Additional Info: > > > User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) > AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36 (In reply to Michael Viveros from comment #2) > Minor mistake: labels in pivot table sheet for SUM/AVERAGE calculations > (column E) should be flipped. SUM WEEKEND should be SUM WEEKDAY, AVERAGE > WEEEKEND should be AVERAGE WEEKDAY, ... SUM gets thrown off too when boolean value = True. Ex. column has values 15, 1, 10, 3, 4 for Is_Weekday = TRUE, SUM = 34 but it should be 33. I can file another bug for SUM being throw off too if necessary (I just changed the title of this bug for now).
Boolean values are just numbers formatted differently, with FALSE = 0 and TRUE = any other value (usually 1 if returned by a function returning boolean). Functions like SUM, AVERAGE, ... or other taking a cell range as argument do *not* ignore such "boolean" values. Solution: do not include the header values in row 2 in your calculations, but use the actual data range, eg. =SUM(B3:B9) or =AVERAGE(B3:B9), or =SUM(B3:B1048576) if you want to cover all the rest of the column as well.