Forum Discussion
Find optimal percent
JoeUser2004 I think we are getting close. I plugged in your calculations for another group of ten stocks and it didn't quite give me the optimal sell. As you will see in the attached file the Ideal sell comes out as 1.851852, while in reality, the ideal sell is 6.382979. Is there a reason it gave me the wrong sell? I have put the equations I use for total % per highest % and Ideal sell so you can double-check those are right. Thanks again for the amazing help. Also for clarification, column R is the gain or loss each stock sells for and Column S is the total gain or loss. Columns Q, T, and K don't matter in this calculation.
@Joe User Also there should never be an option for the ideal sell to be 0 because that would mean I never buy the stocks and that messes up my other calculations
@Joe User actually, never mind, I figured out the solver solution. Thank you so much for all the help you have given me. If there is anything I can do for you don't hesitate to ask. Thanks
JoeUser2004 Thank you for your suggestions. I think I figured out the problems with the $'s. The issue now, as you mentioned is that the answer becomes 0 if there is no way to sell the stocks for a gain. I would much rather the formula produce a solution that sells them for the least loss rather then 0. You mentioned that the solver approach could do this. Could you explain that further? I have never used solver before. Thanks. Attached is a copy of my worksheet.
NoaHomeRun wrote: ``there should never be an option for the ideal sell to be 0``
With the SUMPRODUCT approach to finding the optimum, it is implicit in the fact that you will never have 0% for "highest%", presumably. Right?
OTOH, if you do allow for 0% for "highest%", the SUMPRODUCT approach will choose that only if that does indeed maximize the total% return. Any alternative would result in a "greater" loss (more negative). Why would you prefer that?
-----
With the Solver approach, a constraint like B1>=0.01 could be added.
No matter: As I already explained, Solver does not always find the optimum "sell at" percentage based on your algorithm.
