Hi, I would like a sheet where you enter a number, and then you can set in another columns the %'s you want to get.
For example:
Number | % | Result
1000 | 80 | 800
| 10 | 100
| 10 | 100
And being able to add more values in the % column when needed. I hope the explanation makes sense.
Assuming Number is column A (and 1000 is cell A2), then select column B and Format it as percentage (you can use % icon on toolbar). Column C is Result and enter formula =A$2*B2 and drag-copy down as far as you want. Any number entered in column B will be automatically divided by 100 (for percentage) and multiplied by cell A2.
See, Addresses and References, Absolute and Relative
Sample spreadsheet
PercentsOfSameNumber.ods (9.5 KB)
Very nice, it works but what I would need now is a way to know that the number has gone beyond 1000. I cannot allocate more than 1000$ in each % for each stock for instance. See pic:
https://i.postimg.cc/W34LzzHs/allocation.png
The NFLX stock has allocated 5% which would be 50$, which is 50$ beyond what I can invest since I already spent the 1000$ on the 4 previous ones (80%, 10%, 5%, 5% is already 100%). So I would like that anything beyond that is shown in red in case I don’t realize I went beyond 100% asset allocation for the amount to invest.
You probably don’t want to be guessing how much more you can allocate, use a spreadsheet to do the calculations. I have added in some extra cells showing the Total Allocated so far, Remaining qty and Remaining %.
It is simpler to use conditional formatting based on the Total Allocated. In the attached spreadsheet the Result column numbers turn green (Good cell style) when exactly all the shares have been allocated, red (Bad cell style) if exceeded.
PercentsOfSameNumber2.ods (11.8 KB)
BTW you can add attachment here, use the Upload link. See This is the guide - How to use the Ask site? - #3 by Hrbrgr for more detail
It might be safer to also use a conditional format on the percentage allocations, depending on what “safety” means to you. If allocation amounts get rounded to currency hundredths in a slightly more complex scenario, then they may not add up to the allowance even if the percentages are 100%.
See the attached example.
PercentsOfSameNumber3.ods (12.5 KB)
Could you look at this thread? it got 0 replies
Basically now I would like to divide the % depending on the different categories of type of asset. With this I would be set.
That is exactly what I needed, thanks alot.
FYI: This is sort of backwards. The solutions are supposed to go under Answers and any thanks should be comments on the solutions. Here we all just commented on the Question, so it’s starting to get turned around.
