How many stocks can I buy (formula)

[coverman]

I have a sheet with 3 stocks in Google Sheets (or Excel) 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.

 

[Deleted member 4993]

I have a sheet with 3 stocks in Google Sheets (or Excel) 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.

Why not buy 50 stocks @ $10/stock - and be done.

 

[coverman]

Why not buy 50 stocks @ $10/stock - and be done.

Well, I need a sheet that can calculate different scenarios. I just need this first one to make the rest work. I need to spread the account to make the best investment for the money available. Then I will make another calculation for the next period to calculate the same thing. But I just need to figure out how to make Google Sheets calculate the best investment to place as much money into the three available.

 

[coverman]

It seems like no one could solve this. :-(

 

[Deleted member 4993]

I have a sheet with 3 stocks in Google Sheets (or Excel) 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.

It looks like - this is NOT a class-assignment, but a commercial problem. At this point, at least I do not want to "volunteer" my time for this venture.

 

[coverman]

It looks like - this is NOT a class-assignment, but a commercial problem. At this point, at least I do not want to "volunteer" my time for this venture.

Well, it's just my little private sheet. I wanted to make a formula like this. Not sure if I ever will use it, but when I can't figure it out... I guess there should be some kind of smart thing to make it work. So it's not a commercial problem... just a tricky one.