Numbers:

Question

D_Stocks Author

Level 1

4 points

Numbers:

Please help me with this one.

There is $142,50 (B2) to spend.

With 50%(B5) of this amount I buy tokens for $0,08(B4).

With 50% (B8) of this amount I buy tokens for $0,09 (B8).

Under tokens it should return the amount. Here being 834,38 and 716,67.

How can I automate this in the cells?

And then is it possible to =max 100% B5 and B8 together so that both together never surpass 100%?

Posted on Jan 5, 2022 12:23 PM

Reply

Answer 1

Badunit

Level 7

31,002 points

Jan 5, 2022 6:08 PM in response to D_Stocks

You cannot do the last thing. The spreadsheet cannot adjust a % that you entered manually. A cell contains either a user-entered value or a formula and a formula in one cell cannot change the value in another.

As an alternative, you the following things:

Have a cell with a formula that displays "OVERSPENT" if you go over 100%. Personally I think this is the simplest approach.
Have a cell that calculates the % remaining as you are entering the allocations so you know how much you have remaining.
Add one more row in each "allocation" section that has a formula that determines the percentage remaining and limits the allocation to that amount. Use the formula-determined allocation percentage in your other formulas. See screenshot below.

Formula in cell B5 =IF(B4≠"",MIN(B4,100%−SUMIF(A$1:A4,"Actual Ratio",B$1:B4)),0%)

Copy/Paste it to B9, B13, etc.

You can create a highlighting rule to color in the allocation(s) that have been limited.

Reply