VLookup return value average

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
47
Office Version
  1. 365
Platform
  1. MacOS
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:
Screen Shot 2021-05-20 at 8.55.21 AM.png


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:
Screen Shot 2021-05-20 at 8.59.55 AM.png


Any ideas where to start here?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,996
Office Version
  1. 365
Platform
  1. Windows
If a company occurs multiple times will the csat score always be the same?
 

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
47
Office Version
  1. 365
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,996
Office Version
  1. 365
Platform
  1. Windows
So how do you determine which of the values it should return?
 

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
47
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,996
Office Version
  1. 365
Platform
  1. Windows
You can do it like
Excel Formula:
=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

New Member
Joined
Apr 9, 2020
Messages
47
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

You can do it like
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,996
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 9, 2020
Messages
47
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Apr 9, 2020
Messages
47
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Forum statistics

Threads
1,148,244
Messages
5,745,606
Members
423,964
Latest member
Rayds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top