Hello All,
Just over a year ago I stumbled upon this website. To this day I am still amazed with all of the knowledge and help that goes on here. You guys are incredible. I know Catalin personally built be a formula that I use at my job in finance literally every day. Today I'm back, and I'm stumped.
I've trying to sell my fractional shares of stock that has accumulated through dividends. Problem is, one does not have a cost basis. So, in order for the trading desk to sell it, it needs to be paired with any other of the shares to create an integer. The least I have to sell, the better for tax purposes. Is it possible to write a formula that can find the combinations of individual lots to produce the lowest whole number? I've tried using Solver to attempt to find the least path but I know the math for the constraints is going right over my head. Attached is a simple list of the numbers.
Thank you all a million times in advance.
Perry
Hi Perry,
Can we see a sample file, and a manual example of how the result should be?
Be generous with details, any information is important.
Thanks
Catalin
Hey Catalin,
Super sorry, I thought the file uploaded, Ill try here again with this post. Okay here's my best guess of what I'm looking for:
The fractional share that must be used is .814 in cell G1
That can be added to any of the other "lots" or shares in cells B2:B48, in the least amount needed to create one whole number.
So It would be like I'm asking excel to spit out / highlight/ True false or however it will show me which numbers can be added together to form a single whole number.
so if it was G1 +
B2 1.553 True
B3 2.02 False
B4 2.529 True
B5 2.585 False
B6:B24 False
B25 4.104 True
That would show me that If I add G1 (.814) + B2 + B4 + B25 that would equal 9.000
Im looking for the formula that can calculate all of the combinations and then spit out a lowest whole integer and represent it......
Unless there is a VBA code or some way of solving this that I don't know of deep in excel.
You guys are the best, thank you for your time and efforts!
Hi All,
I'm quite sure you cannot avoid VBA.
As I don't know Vba very well, I've adapted some codes I have in my toolbox. A big thanks to the creator of the macro.
In the attachment you have to choose:
in A1 number to be always used (0.814) in the combinations;
in A2 target number (the integer). Not sure if macro/pc processor can elaborate combinations for big number in A2;
in B1 "0" means please show all the combinations..."2" means " After the second combination stop searching" and so on.
In range B3:B... numbers to be combined.
Click on the blue circle (maybe it looks like a blue egg) to try the macro.
Have fun
