Hi,
I have an enormous amount of data (again) that I am currently using filters to disseminate. It basically is a ton of raw surveys, with responses that could be categorized as either A - text (like guest name), B - information data (like arrival date, whether they used mobile check in, etc), and C - survey responses (which are simply a 1 to 10 score on the question asked) We determine our results on the % of 9s and 10s of the survey results, so I have a countif formula that looks like this: =1-(COUNTIFS($A$8:$A$3000,1,R$8:R$3000,"<=8",R$8:R$3000,">=1"))/(COUNTIFS($A$8:$A$3000,1,R$8:R$3000,">=1"))
A8:A3000 is a column I use that has a simple formula, =SUBTOTAL(102, B8), that allows me to use filters to determine the effects of certain columns on each other. An example; I can filter the column for guests reporting a billing issue (yes/no) to see the effect that has on the satisfaction score in another column.
What I'd like to do is not rely on these filters and instead perhaps use IF statements to come to the same conclusion. I'm not sure exactly what the best approach to this would be as my countifs formulas are already huge, and there are multiple different types of responses in the various columns.
I'm not necessarily looking for the exact answer to how to do this as much as for someone to point me in the right direction on how to do this efficiently as possible.
Here's an example of what I'm using now:
And the simple solution I'd like to move towards:
I have an enormous amount of data (again) that I am currently using filters to disseminate. It basically is a ton of raw surveys, with responses that could be categorized as either A - text (like guest name), B - information data (like arrival date, whether they used mobile check in, etc), and C - survey responses (which are simply a 1 to 10 score on the question asked) We determine our results on the % of 9s and 10s of the survey results, so I have a countif formula that looks like this: =1-(COUNTIFS($A$8:$A$3000,1,R$8:R$3000,"<=8",R$8:R$3000,">=1"))/(COUNTIFS($A$8:$A$3000,1,R$8:R$3000,">=1"))
A8:A3000 is a column I use that has a simple formula, =SUBTOTAL(102, B8), that allows me to use filters to determine the effects of certain columns on each other. An example; I can filter the column for guests reporting a billing issue (yes/no) to see the effect that has on the satisfaction score in another column.
What I'd like to do is not rely on these filters and instead perhaps use IF statements to come to the same conclusion. I'm not sure exactly what the best approach to this would be as my countifs formulas are already huge, and there are multiple different types of responses in the various columns.
I'm not necessarily looking for the exact answer to how to do this as much as for someone to point me in the right direction on how to do this efficiently as possible.
Here's an example of what I'm using now:
And the simple solution I'd like to move towards:
