Find how many values it takes to lower an average to a certain value

[1schuldb]

I'm working on a personal project and I've hit a wall.

I'm trying to find out how many values it takes to lower an average by a certain amount. Here is a simple example:

Let's say I have the values of:
6
6
7
3
10 to lower the average to below 5

The average of those numbers is 6.4. What I'm trying to figure out is how many more values at a certain value will it take to lower the average to 5.0?

Using the above example (5 values that average 6.4):
How many 0's to lower the average to below 5? (The answer is 2)
How many 1's to lower the average to below 5? (The answer is 2)
How many 2's to lower the average to below 5? (The answer is 3)
How many 3's to lower the average to below 5? (The answer is 4)
How many 4's to lower the average to below 5? (The answer is 8)

I only have the answers because I manually calculated it in excel. But what I'm hoping for is a formula that I can put in the original count (from my example it would be 5), the starting average (from my example it's 6.4). Then I would like to pass it how many of a certain value (0,1,2,3,4) and have it return how many values it will take to lower the average past a certain range (in my example the number is 5).

I hope this makes sense. I'm using Excel as my tool so any function from there is fair game. Thank you VERY much for any help.

Thanks,
David

 

[1schuldb]

Denis,

Thank you very much! This worked like a charm. Appreciate your help!!



David