Automatically work out percentage increase to balance figure

[Marvo]

In the attached workbook I need to balance the figures (or as near as dammit) in I34 and J34. I've done this by increasing the figures in Column J (based on Column D) by a percentage, shown in P2. I arrived at this percentage by trial and error until I got I34 and J34 to match (nearly). Is there a way of getting the percentage to change automatically or maybe there's a better option?

All help appreciated.

[AliGW]

Have a look at the SOLVER add-in:

https://support.microsoft.com/en-us/...b-f63e45925040

[Marvo]

Thank you. Interesting.

[Alf]

I've setup a solver model in your uploaded file. Solver must be installed as Excel will not install it by default. You will find "Solver" in the "Data" tag.

Click on "Solver" and then "Solve" button. As I have not analyzed how calculation is done on you sheet I can't guarantee that solver will always work for you but in
this case it works i.e. no difference between value I34 and J34.

Alf

[Marvo]

Thanks Alf. I cant get it to work on my copy, its says "Solver cannot find a feasible solution. I'd made some changes, added a column, change a formula, maybe that's where the problem lies or I didn't copy your example correctly.

[Marvo]

Now I've changed the formula in Column J to + instead of * the percentage number is far smaller so will not take up as much time to solve the problem. I just need the differential to be as small as possible, I'm not going to get it exact. It's basically a balancing act, whatever is plus on one side has to be minus on the other.

[Alf]

Looking at your uploaded file you have changed the original cell to change from cell P2 to cell Q2. So change the solver setup as shown in uploaded image and run solver.

A_solvchang.jpg

If you have problem seeing the text right click on the image and click on "Open image in new tab". Then do a new solver run and the difference this time will -0.000001.


Alf

[Marvo]

Changed that but it still can't be solved, it may be that it is unsolvable. Can the perimeters be changed so that it finds the nearest solution rather than exact?

It's actually columns I and K I need to match up as close as possible.

[Alf]

What determine the setting is the line says cell I34 should be equal to J34

Please Login or Register  to view this content.

another option is this line where the cell I34 is equal to or greater than J34

Please Login or Register  to view this content.

and finally this line where I34 is less or equal to J34

Please Login or Register  to view this content.

As the cell Q2 is found in the J column as part of the formula a change in cell Q2 will make a change in the J values (J2 - J33) and the constraint will determine the Q2 value should change.

Perhaps you could upload your last file so I could take a look at it?

And I'll upload the file you added to #post 5. Clear range Q2 and do a solver run to see if you get a solution.

Alf

[Bo_Ry]

Please try at Q2

=(I34-SUMIFS(D2:D33,E2:E33,"<"&E36))/COUNTIFS(E2:E33,"<"&E36)

[Marvo]

Although the figure says column J adds up to 9.000001, I can't use that as the figures as handicaps only have one decimal point so need to be rounded. (Column K) That actually adds up to 8.2. That's why I canged the two comparisons to I & K. Its quite possible that using a percentage increase means I can't get closer to 9 than that. That's why I asked if there was a way to set the parameters to give you the closest result rather than exact which may indeed be 8.2

The attachment on post 8 is my latest version.

[Marvo]

Thank you Bo-ry, that's works.

It looks like 8.2 in column K is the closest I will get to 9, any further percentage increase will move me above 9 by a greater margin than 0.8

That's okay. I can keep track of future changes and these tiny fractions will quite possibly go either way and even themselves out. You can't make something add up that doesn't.

Many thanks to you and Alf, I started this thread by asking if there was a way of automatically working out a percentage increase to balance a figure and now I have one.

[Alf]

Glad your problem is solved

Thanks for feedback and rep.

It seems to me that I didn't understand your problem properly but then I'm a biker not a golfer but thanks to Bo your problem is solved

Alf

[Alf]

Occasionally I have problems with not solving a problem. It seems you perhaps could use "Goal Seek". Tab "Data" -> "What-if Analysis" -> "Goal Seek"

a_goalseek.jpg

b_goalseek.jpg


Alf

[Marvo]

Thanks Alf. I've only just seen this latest post by chance, didn't get an email notification. Funnily enough I have hit a snag now, sadly the Goal seek didn't solve the problem on this occasion, maybe Bo_Ry will be able to help as it was his formula I entered which works perfectly to share the increase between all the players on my list, however only those that played should get an increase so I'm flummoxed.

Anyway in the attached workbook on the sheet playing, I need AF77 and AH77 to come as close to a match as they can. The cell AH2 will do this IF all the players played but they never do. I've put some figures in and created a result with 14 players. The players that need to share the increase are all those those that played (Column A) and whose points are a minimum of 7 points behind the winning score. This is what we call a buffer zone. I know this is complex but I'm keeping my fingers crossed someone can help. I think it just needs the formula in AH2 changed but how??????

Many thanks

[Alf]

I think the problem for me is that I don't understand the how and the why of your file's setup. Removing the formula in cell AH2 and testing with "Goal Seek" specifying that AH77 should be 10.8 by changing AH2 I got a value of 84.00%. I also tested with a solver setup and then I got 84,34%.

As I said before I really don't know if these results are a sensible.

Let's hope Bo can fix it.

Alf

[Marvo]

Yes, 84% is the answer. That's works. I think I can use that. Obviously it doesn't work automatically but its not a long process to do and certainly better than trial and error. Why I couldn't get it to work was I was putting AF77 into the seek and not the result. Oh well, you learn something new every day. Many thanks Alf. I'll mark this solved, give Bo_Ry a well deserved break.

[Alf]

Good to hear the problem is solved.
Thanks for feedback and rep

As you are aware of Goal Seek don't run automatically. Still if one wish perhaps a macro could be made that could fire Goal Seek whenever a specified cell is changed.

Alf

[Marvo]

It's an idea Alf but I'm not too good with Macros. It's not a problem to use Goal seek, only a few seconds each time and the matches are only once every three weeks.

Thanks again.

[Alf]

Yes you are right as one should never forget the KISS principles (I.e. Keep It Simple Stupid). A thing I often forget.

Alf

[Bo_Ry]

Please try at AH2

=(AF77-SUMIFS(AJ4:AJ75,AC4:AC75,"<"&AD3,A4:A75,1))/COUNTIFS(AC4:AC75,"<"&AD3,A4:A75,1)

or

=(AF77-SUMIFS(AJ4:AJ75,AC4:AC75,"<"&AD3,AC4:AC75,">0"))/COUNTIFS(AC4:AC75,"<"&AD3,AC4:AC75,">0")

[Marvo]

Both work Bo_Ry, I've chosen the second option. Once again your acknowledge and the ease with with you do these things amaze me.

Can I ask one further question? Can I change the formula in AF4 to only work when AC4 has more than zero? I tried putting =IF(AC=0,0, at the start but it didn't work.

Many thanks.

[janmorris]

try =IF(AC=<0,0,formula)
or =IF(AC>0,formula,0)

[Marvo]

Thanks Jan. The first didn't work, the second produces name. It's probably me setting it up improperly.

=IF(AC>0,IF(AE4=1,Handicaps!F4*15%,IF(AE4=2,Handicaps!F4*10%,IF(AE4=3,Handicaps!F4*5%,0))))

Maybe need an & in the first two pieces?

[Marvo]

Got it!

=IF(AC4=0,0,IF(AE4=1,Handicaps!F4*15%,IF(AE4=2,Handicaps!F4*10%,IF(AE4=3,Handicaps!F4*5%,0))))

[Bo_Ry]

Try this

=Handicaps!F4*MAX(0,20*(AC4>0)-AE4*5)%

[Marvo]

As usual Bo_Ry that work a treat. (I just wish I knew how it works) I can see the 5 but where is the 10 and 15? Amazing stuff.