Calculate the Lowest Value

[jsolder]

I'm not sure if it is possible to do this with a formula in excel, but I'm hoping you guys might be able to help me... I have attached a sample workbook for reference.

I need somehow to create a formula/function (or something!!) which will calculate what the lowest possible price is in cell F9. This will need to take into account that certain people cannot be more than their maximum percentage (Column C) and that they cannot be lower than their minimum percentages (Column D) – e.g. John may not have >20%, or <15%.

Column E must always total 100%.

Any help directions on how to go about doing this would be greatly appreciated!

Price Example.xlsx

[XOR LX]

Hi,

Have you tried using the Add-In Solver?

Regards

[AlKey]

The lowest possible price would be the minimum.

[jsolder]

Wasn't aware of this function, I'll take a look and try it out.

Hi,

Have you tried using the Add-In Solver?

Regards

[jsolder]

And no, the lowest possible price wouldn't be the minimum because that wouldn't total 100%...

The lowest possible price would be the minimum.

[shg]

Sort ascending by price, and in E2 and copy down

=MIN(C2, 1 - SUM(E$1:E1))

Please Login or Register  to view this content.

[jsolder]

shg, useful formula, however you'll notice that for Paul the Pct in Column E is lower than the minimum (D8)

[shg]

Missed that.

Please Login or Register  to view this content.

[jsolder]

The problem is that Column F, Tot Pct now totals more than 100%. I can't quite figure out how to modify the formula to ensure that it doesn't exceed 100%...

[XOR LX]

I had a quick go with Solver on the Evolutionary Method (not an expert here, so not sure if that's best for this problem) and stopped it after a few minutes when it had reached the below (though leaving it running for longer will no doubt improve its solution):

	Price	Maximum	Minimum	Percentage to allocate	Final Price
	15.56	20%	15%	17.57%	2.734272487
	18.65	15%	15%	15.00%	2.7975
	19.9	20%	20%	20.00%	3.98
	14.78	10%	10%	10.00%	1.478
	15.23	7.50%	5.00%	7.27%	1.106620287
	15.86	20%	20%	20.00%	3.172
	16.65	15%	10%	10.00%	1.665000002
				100%	16.93339278

Regards

[shg]

The problem is that Column F, Tot Pct now totals more than 100%.

Pardon? Looks to me like exactly 100%.

[Alf]

Solver setup.


Alf

[jsolder]

XOR LX I've found Solver really useful at getting me to an answer since I've been p-laying around with it, my only issue is the fact that you have to change the constraints if the Maximum and Minimum Percentages change. But other than that its working well for me at the moment!

[jsolder]

Pardon? Looks to me like exactly 100%.

shg, here's my results using your formula...

Price Example shg.xlsx

[XOR LX]

"XOR LX I've found Solver really useful at getting me to an answer since I've been p-laying around with it, my only issue is the fact that you have to change the constraints if the Maximum and Minimum Percentages change. But other than that its working well for me at the moment!"

You don't need to enter static values for the constraints! You can reference the cells containing them!

Solver Example.png

[jsolder]

That solves it!!

[shg]

shg, here's my results using your formula...

You're missing the SUM formula in D9.

[jsolder]

My mistake shg. Thanks for all your help guys, can safely mark this one as solved now