Should I use AVERAGE or what to solve this?

[coverman]

I have a sheet with 3 stocks and I would like to calculate how many of each stock I can buy for a specific number.
Let's say I have a sheet and in cell A1 I have the amount that I can spend. Let's say $500.
In cell B1 I have a stock. The price is $10/stock.
In cell C1 I have another stock and it's price is $120/stock.
And finally in cell D1 I have a stock that cost $150.

How many stocks can I but for that $500. I would like to spend as much as possible to use that money.
I thought about calculating the average of the 3 stocks, but then I would probably end up with a lot of $10 stocks that haven't been used. So what is the best way to try to fill that amount with as many stocks as possible (no matter if there are more or less of one, but I would like to end up as close as possible to the $500.

And of course these numbers will change. Sometimes I would like to calculate other stocks or amount to spend.

Any ideas? I guess it should be possible to make a formula for this, but my mind can't figure this one out.

[TMS]

Well, you can always spend $500 exactly. 3 x $150 + 5 x $10 ( 8 shares ), 4 x $120 + 2 x $10 ( 6 shares ), or 50 x $10 ( 50 shares ). Or maybe 2 x $150 + 1 x $120 + 8 x $10 ( 11 shares ).

[Richard Buttrey]

Here's one way

B2:

Please Login or Register  to view this content.

B3:

Please Login or Register  to view this content.

B4:

Please Login or Register  to view this content.

[coverman]

Well Richard. I didn't understand that solution. Since I have the amount in A1. The stocks are in B1, C1 and D1.
And your solution is using A3, B3, B4 and A2. What were these used for?

[coverman]

Well TMS, that is no calculation to be used if I would fill the cells with other stocks. So I am looking for a way to use maths for this.

[coverman]

So, no one could solve this? :-(

[Richard Buttrey]

Well Richard. I didn't understand that solution. Since I have the amount in A1. The stocks are in B1, C1 and D1.
And your solution is using A3, B3, B4 and A2. What were these used for?

Sorry, since you hadn't uploaded a workbook I'd put the stocks in A2, A3 & A4 and hence my suggested solutions in B2, B3 & B4.

Just change my A2:A4 refs to your B1:D1 refs respectively

[coverman]

Ok. Almost, but it's not using all the cells to make a buy with all three stocks. A lot of calculations (with different prices) will leave 2 stocks untouched and it wants to buy several of just one.
I would like to find one formula that can spread the money evenly in the different stocks. At least as close as it can be.

[Richard Buttrey]

Ok. Almost, but it's not using all the cells to make a buy with all three stocks. A lot of calculations (with different prices) will leave 2 stocks untouched and it wants to buy several of just one.
I would like to find one formula that can spread the money evenly in the different stocks. At least as close as it can be.

Your OP didn't lay down a condition that all stocks should be used.
Nor indeed that the money needs to be spread evenly, whatever 'spread evenly' actually means.
The same amount for each stock? What then for any unspent money? Using that for buying another stock would breach the 'spread evenly rule'

Perhaps you could upload a few examples that show the sorts of results you would expect in different situations. Only when we know what you want to see can we be certain, otherwise we end up guessing or assuming.

[coverman]

Oh, I'm sorry. Hard to explain (English is not my mother tongue).
Well what I'm looking for is a formula that can help me if I have $500 so I can buy X of stock A, X of stock B and X of stock C.
Next month when I add more money and maybe the stocks have paid dividend then I might have $650 in my account. Then I would like the formula to use the money in these three stocks so I can use as much of money on these three without having too much money left.

What I can do with this formula if this works is to calculate how many stocks I can buy month after month if I save $500 every month. So I want the sheet to make the calculations for me and put the money I have in my account month after month in those three stocks depending on how much money I have. So it would be best if it can use as much money as possible into these three stocks. What I'm doing now is manually adjusting the numbers to see what it looks like. But I guess there would be some great formula that can do the math, but I haven't figured it out. And I think you are pretty close to a solution. At least it looks like that.

[Richard Buttrey]

Please upload a workbook and manually add the results you expect to see as you move to another month.
Your current description has moved a long way from the originally stated requirement and we really do need to see your data and the layout.

Make sure you clearly show in the workbook what you add/change for say three months and the results that want to see and where.

You may have to temporarily add copies of the results at the end of each month so that we can understand the overall progression

[coverman]

Well, here I made a simple example sheeet. It's the yellow cells I would like to be automated.


So those fields can put a weighted amount of shares depending on the total in the bottom cell (which is the monthly savings and the dividends added so the account is growing and more stocks can be added every month).

Is this making it clearer?

:-O

[Richard Buttrey]

It's still not clear why you choose the stock numbers you do. What determines how you vary the numbers?

And of course earlier you'd said you wanted the money spread evenly.

It seems to me a consistent way would be every month start with one of each share since they total less than the amount available, then using the balance, add another highest cost share you can and then the next highest.

When you get to a month where the amount available exceeds twice the total cost of 280, i.e. 560, start with two of each share and then add others as above. ...etc for other months.

The formulae I gave you earlier will apportion the remaining sum after you've added one, two, three ...etc of each share in succeeding months

[coverman]

Hmmm, not sure I understand what you mean. But I will think about it and see if my brain wakes up. ;-)

[coverman]

After a good night sleep I think I understand your solution. So I should start with 1 of each and then add the largest one and then the next largest (if it's possible and room enough) and finally fill up with the last one.
And when the account reach the double amount of the three, then I can start adding 2 of each, and so on.
Well, it's something like that I've been doing manually. But I thought it would be great to have some kind of formula in the sheet doing this for me. Then I can try different combination of stocks and maybe also more than three to have nice mix of the balance I can use as much as possible of.

Maybe the formula needs to calculate the total sum of the used stocks. Then sort them in order. Then try to buy one of each (starting with the highest). And then add new ones step by step until it reach the max number.

But I can continue to do parts manually. But I guess there is a solution somewhere to make this automatically. But my math skills are not good enough. I usually take the longer road to reach the final goal. ;-)
My first idea was to use AVERAGE to calculate how much I should try to reach with each stock (as close as possible). Maybe that would work. But then it has to work with the largest one in the correct order to make it work.
So, in my example the average of 280 would be 93.3. But then I can't even buy the one for 150 and not even the one for 120. So that didn't work. But maybe if there is a formula where it's possible to make more weight to the larger numbers (or whatever it is called in English). Well, it's not that easy to solve I guess. But I kinda like automated stuff in sheets. I just love seeing things work for me in the different cells just by changing a number in one cell.

But maybe this isn't something that can be solved to work like that.