I am trying to create a simple way for anyone using my spreadsheet to see an average calculation using data disaggregated by one or more demographics. Using an array, I have been able to calculate the average if there is a value selected for each of the demographics, but I cannot figure out how to make it work if the the user does not want to disaggregate the data by one or more of the demographics. I'm assuming I would need to add some addition If statements, but I'm not sure where to start. Any help is much appreciated!
Screenshot 2020-01-08 08.55.33.png
The formula I am using is: =AVERAGE(IF(AllResponses_Edited!A:A=B2,IF(AllResponses_Edited!B:B=B3,IF(AllResponses_Edited!C:C=B4,IF(AllResponses_Edited!D:D=B5,IF(AllResponses_Edited!E:E=B6,IF(AllResponses_Edited!F:F=B7,IF(AllResponses_Edited!G:G=B8,IF(AllResponses_Edited!H:L<>"",AllResponses_Edited!H:L)))))))))
Note that this spreadsheet is using all dummy data; it contains no private information.
Bookmarks