optimum repartition ! solver constraint ?

[philbill]

hi all, just turned on solver functionality few weeks ago and already 1 problem !

statement: I do print different vouchers 150@25 euros; 85 @25.5; 60@25.2; 124@24.80; 55@24.40; 71@23.30 meaning my total is 545 vouchers with an average at 24.77248 let's say I do have 5 players winning the total and I need to find the best allocation trying to be the closet to the initial average .
player 1 gets 70 2 gets 85 ;3 get 102 ;4 gets 95 and 5 gets 193

my problem is to find a correct constraint to get the optimum allocation matching the initial average!! is that possible ?

 

[Faseeh]

Hello,

Optimization problems comes with a statement of whether you want to minimize or maximize certain quantity. Can you elaborate your problem in this context. What do you want to minimize/maximize and how your vouchers and other quantities are related.

 

[philbill]

Hello,

Optimization problems comes with a statement of whether you want to minimize or maximize certain quantity. Can you elaborate your problem in this context. What do you want to minimize/maximize and how your vouchers and other quantities are related.

Hi Faseeh.
First of all ty for your reply.
In the present example : 545 vouchers are printed and redistributed over 5 winners . my only concern is the allocation to match the initial average the closest as possible.My solver already runs( all different kind of vouchers are distributes ,all winners with the right qty, BUT the solution given is not the best possible )I get the feeling that one formula or constraint kind of "be the closet to "is needed somewhere . wherever you are Faseeh ,have a good day . Phil from Amsterdam

 

[NARAYANK991]

Hi Phil ,

Let me state your problem as I have understood it , and you can clarify if I am wrong or confirm if I am right.

1. You have a total of 545 vouchers of 6 different denominations ; these 6 denominations are :

25 , 25.5 , 25.2 , 24.80 , 24.40 , 23.30 euros

2. These 545 different vouchers have to be distributed among 5 people , with the following constraints :

• each person should get an integer number of vouchers
• the weighted average of all the vouchers , after they have been distributed among the 5 winners , should be as close to 24.77248 as possible

I think you need to specify one more constraint viz.

What is the minimum number of vouchers a winner can be allotted , and what is the maximum ?

Narayan

 

[philbill]

Hi Phil ,

Let me state your problem as I have understood it , and you can clarify if I am wrong or confirm if I am right.

1. You have a total of 545 vouchers of 6 different denominations ; these 6 denominations are :

25 , 25.5 , 25.2 , 24.80 , 24.40 , 23.30 euros

2. These 545 different vouchers have to be distributed among 5 people , with the following constraints :

• each person should get an integer number of vouchers
• the weighted average of all the vouchers , after they have been distributed among the 5 winners , should be as close to 24.77248 as possible

I think you need to specify one more constraint viz.

What is the minimum number of vouchers a winner can be allotted , and what is the maximum ?

Narayan

 

[philbill]

you get the probem perfectly . as mentioned on the first post player 1 gets 70 ; 2 gets 85 ;3 get 102 ;4 gets 95 and 5 gets 193 the qty per player can't be changed.

 

[NARAYANK991]

Hi ,

OK. So it is also decided what is the total number of vouchers each player gets ; what is required is only the distribution of these totals amongst the individual denominations.

Narayan

 

[NARAYANK991]

Hi ,

I am still not very sure I have understood your requirement , but I have used certain constraints in the uploaded file ; see if they are correct.

Narayan

 

[philbill]

Hi ,

I am still not very sure I have understood your requirement , but I have used certain constraints in the uploaded file ; see if they are correct.

Narayan

Hi Narayan . Thank you for the try . my requirement is simple ; the average for each winner as to be the closest as possible to the average of the 545.
i tried your solver . it works,like mine. i get an allocation for all vouchers but unfortunately(like mine ! ) this is still not the best . using your results :If you switch from player 5 to player 2 25 vouchers at 25 against 25 vouchers at 23.30 you already improve both the averages . Also there no requirement for each player to get at least 1 voucher of each category. Only the closest to the Initial average matters . I do appreciate a lot your efforts guys . Phil from Amsterdam

 

[Faseeh]

Hi @philbill

Good evening from Karachi, Pakistan.

I have changed the layout a little to a more concise one. Please see attached sheet. It gives value of 27.77 with given criteria (vouchers, weighted avg, voucher per person) this is just a try please point if i have missed something.

 

[philbill]

Hi @philbill

Good evening from Karachi, Pakistan.

I have changed the layout a little to a more concise one. Please see attached sheet. It gives value of 27.77 with given criteria (vouchers, weighted avg, voucher per person) this is just a try please point if i have missed something.

Good evening Karachi . thank you Faseeh.
I used vouchers to present the problem. so if a winner has 24.87 ore 24.75 euro doesn't make a lot of diff. but in fact i'm working at portfolios allocation with shares !! meaning the split for each client has to be extremely precise. i worked out the problem today!. In fact the solution is to square the difference between the average of each "winner "compare to the initial average .Kind of variance calculation , and then minimizing that value . your solver works but the solution was not optimal. Have a nice weekend . Phil on its way to London for the weekend !