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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,092
Office Version
  1. 365
Platform
  1. Windows
It does average them out. ;)
+Fluff 1.xlsm
ABCDEFGH
1
2aGooda2.43
3aGood
4bOk
5bPoor
6aGood
7aPoor
8aOk
9bOk
10aOk
11bPoor
12bPoor
13aGood
14
Survey Data
Cell Formulas
RangeFormula
H2H2=SUM(COUNTIFS(D2:D1000,G2,F2:F1000,{"Good","Ok","Poor"})*{3,2,1})/COUNTIFS(D2:D1000,G2)
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,092
Office Version
  1. 365
Platform
  1. Windows
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.
That's a lot simpler. :)
Try
Excel Formula:
=COUNTIFS(D2:D1000,G2,F2:F1000,"Good")/COUNTIFS(D2:D1000,G2)
and format the cell as %
 

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
47
Office Version
  1. 365
Platform
  1. MacOS
Ok I think I am almost there. I used the following formula and it works as we intended. HOWEVER, if it can not match criteria, how do I have it return blank ("") instead?

So either it will work and a I get a number value. Or if the customer hasn't taken a survey it remains blank.

Here is what I did with your formula:
Excel Formula:
=COUNTIFS('Survey Data'!D2:D1000,F3,'Survey Data'!F2:F1000,"Awesome")/COUNTIFS('Survey Data'!D2:D1000,F3)*100
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,092
Office Version
  1. 365
Platform
  1. Windows
You can use iferror, like
Excel Formula:
=IFERROR(COUNTIFS('Survey Data'!D2:D1000,F3,'Survey Data'!F2:F1000,"Awesome")/COUNTIFS('Survey Data'!D2:D1000,F3)*100,"")
 
Solution

indiemusicboy

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

ADVERTISEMENT

@Fluff digging up this old gem you helped me with :).

In review we have three text values: Awesome (value =100), Ok (value =50), Bad (value =0).

I'm using the formula you provided:
=IFERROR(COUNTIFS('Survey Data'!D2:D1000,F3,'Survey Data'!F2:F1000,"Awesome")/COUNTIFS('Survey Data'!D2:D1000,F3)*100,"")

In the event that there is only (1) survey for it to count, and that survey value is "Ok", its not turning up as 50 (on a scale of 0-100). It's reporting back blank (no error - just blank). However if the survey value is "Bad", it reports back (0). If its value is "Awesome" it reports back (100). Both as it should.

Does this formula not take into account an "Ok" response? Just "Awesome" (100) or "Bad" (0)?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,092
Office Version
  1. 365
Platform
  1. Windows
It only takes Awesome into account, as it's not checking for any other value.
 

indiemusicboy

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

ADVERTISEMENT

It only takes Awesome into account, as it's not checking for any other value.
Thanks @Fluff . So the equation takes into account that if there is anything other than "Awesome" it is essentially a zero. But there needs to be an "Awesome" first in order for the formula to even state zero.

So two questions please and thank you again:

1. Is it even possible to fix that with Text as it is today?
2. If not, I could give the text values if that solves it; Awesome=100, Ok=50, Bad=0

I need to essentially take the number of satisfied customers (Awesome or 100), and divide by the total number of responses. For example, if 62 of my 100 responses have a rating of Awesome, my score would be 62.

Right now, if the customer does not have a survey in the system I have it default to 100. So I added to your formula at the end with "100":

=IFERROR(COUNTIFS('Survey Data'!D2:D1000,F3,'Survey Data'!F2:F1000,"Awesome")/COUNTIFS('Survey Data'!D2:D1000,F3)*100,"100")

I could instead have it blank. But if they have only one response, and its other than awesome, it would need to show "0" as a result instead.
 

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
47
Office Version
  1. 365
Platform
  1. MacOS
@Fluff Its not. Perhaps its something I'm doing wrong? See below. It's blank even though it should be 0.

Acme has (1) survey in the range listed and its value is "Ok". Its showing up blank here. By our formula shouldn't it be 0?

And in the event that Acme doesn't even exist in the range listed, it would simply show up blank instead?
Screen Shot 2021-08-18 at 3.36.11 PM.png


The cell format of R10 is set to Category "Custom" and Type "0;;;"
 

indiemusicboy

New Member
Joined
Apr 9, 2020
Messages
47
Office Version
  1. 365
Platform
  1. MacOS
@Fluff Its not. Perhaps its something I'm doing wrong? See below. It's blank even though it should be 0.

Acme has (1) survey in the range listed and its value is "Ok". Its showing up blank here. By our formula shouldn't it be 0?

And in the event that Acme doesn't even exist in the range listed, it would simply show up blank instead?
View attachment 45173

The cell format of R10 is set to Category "Custom" and Type "0;;;"
Ah - its the Type!!!!! It needs to be set to "0" instead of "0;;;" correct?
 

Forum statistics

Threads
1,144,575
Messages
5,725,088
Members
422,590
Latest member
Mikeyyy

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