I've created a very complex pivot table which I want to convert to formulas. I have everything completed except for the filters. If there is an option chosen in the filters there's not a problem, but if the user does not choose an option and wants to view all the data for that filter and maybe not for other filters I can't see how to write the if statement.
For instance if I have filters for City, Bedrooms, and Baths, and the user has the option of (All) for any of the filters or the option of choosing from the dropdown list how do I create the if statement?
I currently have:
=IF(C10=0,0,AVERAGEIFS(Data!I:I,Data!$T:$T,$B$9,Data!$V:$V,$B10,Data!$C:$C,$B$1,Data!$A:$A,$B$3,Data!$B:$B,$B$2))
C10 is the number of sales for the quarter so if there were no sales for the quarter do not try to average the list price
I:I is the list price from the data
T:T is the year from the data = B9 (the year on the table header)
V:V is the quarter from the data = B10 (the qtr on the table header)
C:C is the City from the data = B1 on the dropdown list
A:A is the Bathrooms from the data = B3 on the dropdown list
B:B is the Bedrooms from the data = B2 on the dropdown list
I understand how to create the if(and statement if the user chooses (All) for all the filters or if the user chooses specific data for all the filters but what if they say (All) for City and then 3 for Bedrooms and 2 for Baths OR Adger for City and then (All) for Bedrooms and 2 for Baths. The number of contingencies for the if statement seems endless but I keep thinking someone has had a similar problem before.
For instance if I have filters for City, Bedrooms, and Baths, and the user has the option of (All) for any of the filters or the option of choosing from the dropdown list how do I create the if statement?
I currently have:
=IF(C10=0,0,AVERAGEIFS(Data!I:I,Data!$T:$T,$B$9,Data!$V:$V,$B10,Data!$C:$C,$B$1,Data!$A:$A,$B$3,Data!$B:$B,$B$2))
C10 is the number of sales for the quarter so if there were no sales for the quarter do not try to average the list price
I:I is the list price from the data
T:T is the year from the data = B9 (the year on the table header)
V:V is the quarter from the data = B10 (the qtr on the table header)
C:C is the City from the data = B1 on the dropdown list
A:A is the Bathrooms from the data = B3 on the dropdown list
B:B is the Bedrooms from the data = B2 on the dropdown list
I understand how to create the if(and statement if the user chooses (All) for all the filters or if the user chooses specific data for all the filters but what if they say (All) for City and then 3 for Bedrooms and 2 for Baths OR Adger for City and then (All) for Bedrooms and 2 for Baths. The number of contingencies for the if statement seems endless but I keep thinking someone has had a similar problem before.