Quality of Response Needed to Obtain a 3.00

[Building]

Hello,

I am trying to develop a spreadsheet that will help motivate employees. I would like to be able to show them how many responses on a survey are needed with a "4" responses that will get us to the goal of a 3.00 average given the number of surveys left and the current average.

I've attached a spreadsheet that gives some input in the type of data that I'm working with - essentially the below question is what I want answered by a formula of some sort.

"How many 4's do we need to obtain the goal of 3.00 given the current average?"

I can't seem to get any combination of formulas to work.

Can anyone please take a look at the attached spreadsheet?

Quality of Response Needed for Certain Score.xlsx

[Bernie Deitrick]

In E11:

=IF(F4<3,IF(ROUNDUP(F6*(3-F4),0)>(F5-F6),"Cannot get to 3",ROUNDUP(F6*(3-F4),0)),"Above 3 already")

But here is your file:

Quality of Response Needed for Certain Score.xlsx

[Building]

Thank you, I believe that solves part of the question, but I believe the formula may use irrelevant data.

How does the "surveys sent" function in the formula? Here's how the current score is calculated:

current score = (score of survey 1 + score of survey 2 + score of survey 3 + etc......)/responses to date

only a portion of the surveys sent actually generate responses

I don't know how to get the "responses with a 4" to adjust based on the current score and responses to date

I should add that the maximum score is 4.00

[Bernie Deitrick]

My formula does not contain irrelevant data, and it returns the mathematically correct required number of 4s. I know how an average is calculated, and I guessed that you could not score above a 4 (or else you would have wanted 5s not 4s), and that your scores are only integer values.

You can calculate how many extra points you need to improve your average to a 3 by calculating the "deficit" - the more responses, the bigger the deficit for a particular score. For example, if you have one response that is a 2, your deficit is 1, and a single 4 can get your average (2+4 /2 = 3) to a 3. But if you have 2 and 2 from 2 responses, your deficit is 2, and you need to get 2 4s to get your average to 3.

Basically, since you want to get to a 3.00, the deficit is (3-average) times the number of received responses. That is the F6*(3-F4) part of the formula. Since you can only get one extra point above 3.00 (4 - 3 is 1) per response, the number of responses that must be a 4 to make up the deficit is - wait for it - the same as the deficit: F6*(3-F4). But if you have too few responses left to make that up (which is the (F5-F6) part of the equation - surveys sent out - the number already received), then you can never reach 3, and my formula tells you that.

You could also calculate what the average score for all remaining surveys needs to be to get the average to 3.00 if all surveys are returned - it's just math.

[Building]

That makes sense, I think there may still be something going on with the responses to date and anticipated survey responses.

If I have 320 anticipated surveys to be completed (opportunities to get a "4") and my current score is 1 is it actually not possible to get to a 3.00 average with 320 possible "4"'s left?

I didn't mean to offend in my last post by the way, just trying to work my head around the formula.

[Bernie Deitrick]

No offense taken.

Whether you can get to 3.00 in any situtation depends on how many responses you have received - If you have 320 responses left, with an average of 1, then you could have received as many as 160 responses and still get to 3.00 (your deficit would be 2*160, or 320). As a I stated, the total deficit depends on the number of responses received, and the possibility of making up the total deficit depends on how many there are left to receive.

[kaplanj23]

Math fight!!

[Building]

I see, but shouldn't you need more 3's to make up a defecit than 4's?

It should take less 4's to get from 2.31 to 3.00 but right now the formula shows 60 4's needed or 31 3's needed to get there.

[Bernie Deitrick]

No - you can never get to a 3.00 average from an average less than 3, if all you get are 3s, so I changed the wording on the label of the second number. You can get up to 31 3's and still get to 3.00, but if you get more than that, you will never get to 3.00 as an average - close, but never there. Of course, any 2s or 1s will increase the number of 4s required and decrease the number of 3s allowed, so the whole thing is very fluid and has multiple possible "solutions".

[Bernie Deitrick]

Since you're in Boston, don't you have anything better to do, like dig a tunnel in the snow or something?

[Building]

Hand Calculation.pdf

I think I'm still missing something in your formula, I've attached a hand calculation showing what I believe should be correct- it shows 102 surveys needed with a score of 3 in order to reach a 3.00 average given 158 surveys left to be completed (as a percentage of total surveys; not all surveys are completed) and a current score of 2.31.

I may be completely off and maybe I need to go back to school but shouldn't I be able to reach an average score of 3.00 with only 3's?

[Bernie Deitrick]

I changed the wording next to the 3's calc to be:

"how many 3's can we get and still average 3.00?"

But that also means that you have to get the required number of 4s to get the 3 to start with, the value labeled "how many 4's do we need to get an average of 3.00?"

As to improving your score from an average below 3 to a 3.00 by only getting 3s:

Let's imagine that you have 100 surveys out and 50 responses so far, with 50 left to go. Of the current responses, 49 are 3 with just one is a 2. That means that your total is 149 points, and the average is 149/50 or 2.98. Which is close. So, if you get all 50 remaining responses and they are all 3s, you can improve your score (since your total will now be 299), and your average will improve to 2.99 (which means that is has not improved to 3.00).

At the least: You need one 4 to improve to a 3.00 since you have a deficit of 1 point, which can only be made up by getting score above 3 - that is, a 4. So getting 3s, even a large number of them, will not get your overall average up to a 3 if it is ever below 3 at any point.

(And for the math people out there, yes, I know that as the number of replies with a 3 tends to infinity the average will tend to 3, but let's only consider realistic situations.)