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.
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.