VLookup return value average

[indiemusicboy]

Looking for a search, match, return value forumla.

I have a worksheet that has CSAT survey data in it; company name (Column D) and rating (Column F). See below. The rating system is Awesome, Average, and Poor.

Survey Data Worksheet format:

I have hundreds of individual worksheets in the same workbook for each specific customer containing other unrelated data points about them.

On the individual customer worksheet I need to have a specific cell [example: 'Acme Company'!R10] show the average rating [example: 'Survey Data'!F:F] from the survey worksheet if the customer name matches. The survey worksheet may have the customer name once, several times, or not at all.

"Customer" Worksheet format:

Any ideas where to start here?

 

[Fluff]

If a company occurs multiple times will the csat score always be the same?

 

[indiemusicboy]

If a company occurs multiple times will the csat score always be the same?

No. It will be one of the 3 I mentioned. And every time an end user of that company takes the survey, I want to add it to the aggregate total for that particular company.

 

[Fluff]

So how do you determine which of the values it should return?

 

[indiemusicboy]

So how do you determine which of the values it should return?

Say customer ABC uses our support 3 times this month. They would have the opportunity to fill out a survey 3 times to rate the support. They may have a different experience each time. 2 times its Awesome. 1 time its Poor. I need to figure out a way that can aggregate those 3 ratings into an average. If I have to assign a number value to make that happen I can. But I'm lost at how to write the VLookup for this whole flow.

Did that answer your question?

 

[Fluff]

You can do it like

=SUM(COUNTIFS('Survey Data'!D2:D1000,F3,'Survey Data'!F2:F1000,{"Good","Ok","Poor"})*{3,2,1})/COUNTIFS('Survey Data'!D2:D1000,F3)

just change the {3,2,1} to whatever weight you want to apply

 

[indiemusicboy]

You can do it like

=SUM(COUNTIFS('Survey Data'!D2:D1000,F3,'Survey Data'!F2:F1000,{"Good","Ok","Poor"})*{3,2,1})/COUNTIFS('Survey Data'!D2:D1000,F3)

just change the {3,2,1} to whatever weight you want to apply

Thank you. But I'm looking for the AVERAGE. This looks like a SUM. I don't want it to add up all the instances. I want an aggregate.

 

[Fluff]

How exactly do you expect to "Average" text values?
If you describe exactly what you want I can help, otherwise I can only make suggestions, which you may "shoot down in flames" again.

 

[indiemusicboy]

How exactly do you expect to "Average" text values?
If you describe exactly what you want I can help, otherwise I can only make suggestions, which you may "shoot down in flames" again.

OH NO! I'm not shooting anything down in flames. That formula looks solid. I'll give Good/OK/Poor values for each. I just need to figure that out. But then how do I average it? Even if we stick with 3,2,1 model. I'd want to average those numbers, not add them up.

 

[indiemusicboy]

Sorry! 2 more things. One - thank you. Two, I'm looking to do something like this to get a number value between 0-100:

Customer Satisfaction (CSAT) is calculated by dividing all the positive (GOOD) responses by the total number of responses and multiplying by 100. This results in your CSAT percent.

For example, if you have 35 positive responses and a total of 50 responses, your CSAT would be 70%.

35 / 50 x 100 = 70%

....so the formula has to pull only that client's survey responses that are "GOOD" and divide it by the total number of surveys from that specific client and multiply it by 100.