Too dificult to describe 2

[multisol]

Hallo,
I want to make a formula to solve the following problem:
I have a list of 100 numers with values ranging from 1 to 5. I want to seperate them up into batches of 10 with an average value of 2. The last batch will have what ever is left over in it.
The solution needs to be a formula and not VBA or Query
Ill be amazed if this one is do-able!
Good luck
Multisol

 

[sijpie]

this is simply not possible in a formula, as you cannot 'mark' wich numbers from the list have been used in the formula so far:

Say that the formula starts with checking the average of the first 10 numbers. The average is 2.2, so it then drops the 10 number and takes the 11th number in its place, now the average is 2.
How would the next part need to start? The formula would need to describe every combination possible, which would lead to more than 17 trillion (17 * 10^12) if statements

 

[multisol]

Sijpie, Its a huge shame that it doesnt appear to be possible but thanks very much for your speedy reply. Back to the drawing board for me then!

 

[brettdj]

There may be a workaround using solver (ie non VBA)

While the detail may be a little confusing here is a potential approach if you want to expand

- Setup a list of 10 columns of 100 cells
- Set the solver costraints so that
- these values must be integers
>=0
<=1
- the sum of each of these mapping clusters (at column bottom) must equal 10
- the sum of each mapping row is 1 (ie each of your 100 hunded numbers can only be "flagged" once
- when each mapping column is multiplied by your original field of 100 numbers the sumproduct of must equal 20

This method forces solver to try to pick 10 unique groups of numbers that total 20

Cheers

Dave

 

[multisol]

Dave, Thanks a lot for this unexpected era of New Hope! Actually I have never used Solver before and didnt even know it exists (which gives you a bit more background about my noob rating). Im going to study the vids on You Tube and then ill be able to report back what the results are.

Cheers

 

[multisol]

Test

 

[multisol]

Hi Dave, In the meantime Ive been messing about with Solver. Would have liked to post it with this message but it seems like thats not allowed and anyway it looks like Solver doent export withe the worksheet.
I have tried your solution (at least I think thats what I did) but it didnt work. As the average of the 100 values will almost never be = 2 I decided to change the last column to allow an unlimeted result so that the the other 9 kolumns can get their averages to 2.
In both situations Solver said that it was too difficult to solve or there were too many variable cells.
The example that I posted was an example. The real thing could have 500 rows and 50 columns. I suspect this means even more variables. Excel can only deal with 200 variables.
There is a 3rd part exe that can deal with unlimited variables unfortunately my wallet cannot.
Is there another way to configure the question which would reduce the variables or do you think I just bodged the programming because the number of variables shouldnt be a problem for Excel?
Whistling in the dark and trying to think of a happy place,
Multisol

 

[sijpie]

Why don't you solve this with a macro? Particularly if the rang is going to be a lot larger, then using a macro may be the business.

 

[multisol]

I dont know how to .

 

[sijpie]

Do you know VBA at all or absolutely nothing?