Referencing a range within a pivot table field(s)

[allodiba]

Hi all.

I am working on a spreadsheet of survey data. The survey asks respondents a number of questions to gauge their retirement readiness and confidence in health and finance. The end goal is to see which questions were answered the most confidently and least confidently. In the attached screenshot, you will notice I have lines to the right of the pivot pulling the % of people who responded negatively and positively to a question. I then sort these out to the far right. 

Two Questions:

1. Is it possible to reference an array under a specific pivot field?

Example: in the screenshot, I'm looking to pull the % for those who answered 'no' and 'yes' with =VLOOKUP("No",A3:B5,2,FALSE))

I'm currently referencing a range of absolute cells (A3:B5). The issue is that in the future when I paste new raw data as more respondents take the survey, I will likely need to manually edit some cells for other questions below to fit the formulas looking for yes and no answer percentages. Is there a way to reference the pivot table fields Questions and Responses with =GETPIVOTDATA?

In plain English, my formula would be =VLOOKUP("No" in Pivot Table Range Q1:Responses"). This way, no matter how many rows Q1 responses may or may not contain in the future, it will provide me with the percentage of No's or Yes's.

2. My team has decided that a non confident answer (for now) will be the responses containing "no" and "sort of". There are some Questions that don't contain any of a particular response option. In my screenshot, I have a column summing the Sort Ofs and Nos. But I am getting a #NA because of the lack of an If OR statement. When the cell in the sort of + no column tries to sum the two, the formula fails if one of them doesn't exist in the responses for that particular question. Currently the formula reads:

=SUM(VLOOKUP("Sort of",A812:B814,2,FALSE),VLOOKUP("No",A812:B814,2,FALSE))

What would this look like as an If OR statement? 

Happy to clarify anything. Many thanks in advance!

 

[#2]

Hi @allodiba 

Before everything else: please always provide the version of Excel you run + OS (Windows / MacOS)

#1. Is it possible to reference an array under a specific pivot field? Example: in the screenshot, I'm looking to pull the % for those who answered 'no' and 'yes' with =VLOOKUP("No",A3:B5,2,FALSE))

Basically the answer is no. Depending on your version of Excel there's probably a +/- complex solution though

#1.1 Is there a way to reference the pivot table fields Questions and Responses with =GETPIVOTDATA?

No problem. However as you will notice with the below example things are very hard-coded between double-quotes:

=GETPIVOTDATA("[Measures].[Answer%]",$B$2,"[Table1].[Question]",
	"[Table1].[Question].&[Q1. I feel I am in good health currently.]",
	"[Table1].[Answer]","[Table1].[Answer].&[No]"
)

So, if on a next Refresh of your PivotTable a Question has changed the above formula will need to be adjusted

#2 My team has decided that a non confident answer (for now) will be the responses containing "no" and "sort of"

 IMHO your team already has the required "tool": build a new PivotTable > Put the Answer field in the Filters area. Then filter on no and sort of only:

If later your team change their mind they will only have to play with the filter. Makes sense? 

[#3]

@allodiba 

Given you use the Excel Data Model, an alternative to the Answer field in the Filters area of the PivotTable is a Slicer:

[#4 · Accepted solution]

@allodiba Looking at your screenshots, it seems like you could just move the Answers field into the Columns Area of the pivot table report.

Having said that, the entire report could simply be built with the COUNTIFS function, rather than having to mess around with Power Query, Power Pivot and/or pivot tables.

Please see the attached file, which is setup as an "all-in-one" report, where you can generate results by Marital Status, Retirement Timeline or Age, or any combination of the three. You can also use Filters on the results range to sort by % Yes or % No/Sort of, as well as to filter by Number Filters > Top 10...

COUNTIFS Report Sample

EDIT: in the attached file I've also included a second worksheet called "Report with Blanks", which has been modified to accommodate any records in the data table containing blank values (ie: if the Age field is left blank, or if an answer to any question is left blank). If this is not a concern, the "Report" worksheet will be sufficient. Cheers!

[#5]

@allodiba 

Hope you're doing well. In case any of the replies you got did it, there's a Mark as answer link at the bottom that helps people who Search - Thanks

[#6]

Hi there,

Ended up just using COUNTIFS

Example:
=COUNTIFS(Table1[Score],"<91",Table1[Score],">46", Table1[Retirement Timeline], "Retiring 3-5 Years")

Thanks for your assistance!