How to Generate a Range of Values between 2 Points with a Given Average

So, I'm trying to figure out a way for excel to generate a range of values (given the amount of values/points you want to have) scattered relatively evenly (if you were to view it visually plotted on a linear graph) between a high & low value or range, with a given average (meaning the average is set beforehand, and the entire list of values will end up averaging out to that number).

So for instance, say you have:
- High End: 100
- Low End: 10
- Average: 70
- Number of data points: 5

This means that there will be 5 values total (that need to be scattered relatively evenly) between 10 and 100, to where they will average out to 70. So the results may look like this:
1) 85
2) 77.5
3) 70
4) 62.5
5) 55

This is just a rough example, and it may not end up with those exact values. But I hope it gives the idea of what I'm looking for.

I have attached a workbook with a similar scenario with different values. Each of the 4 conditions I listed above are variable and will change when the scenario changes, but once they are all entered, I need excel to calculate each of the points/values in the way described. Is there any possible way to do this in excel?

Thank you for your help.

Here is a formula that will provide the maximum possible span between the first and last outputted values while providing exact (within Excel's rounding capabilities) differences between steps and while also averaging to exactly (again, within Excel's rounding capabilities) the value you specified as the average. Put this formula in cell C9 and copy it down for as many rows as you think you will ever have steps for...

Formula:

Please Login or Register  to view this content.

Hey Bsalv, thank you for what you provided for me. That is a great solution when I need it to do this at with some randomness thrown in. And it is done in a way that I was not expecting. Thank you so much.

Rick Rothstein -- thank you also. You helped make it so that the points were spread out as much as they could be, and as evenly as they could be. This is what I was thinking, and I think both your solution and Bsalv's will be useful for what I'm trying to do. Thank you both for all your help!!