Search code examples
google-sheets

Google Sheets calculate percentage of the values in the rightmost column of total values in that column

My table is as follows:

Ticker Wanted% Actual% April May June
AAPL 30 1$ 1$ 150$
META 50 10$ 5$ 15$
MSFT 20 100$ 10$ 120$

My goal is to calculate the actual percentage each ticker is taking from my whole portfolio. I'm looking for a formula to put in each cell in column C so it can calculate the whole sum of the porfolio in the rightmost column (a new column will be appended each month manually), and how much weight the current row takes out of the total.

For the example above, the Actual% would be:

| 52.6 |

| 5.2 |

| 42.1 |

Solution

  • You may try:

    =let(last_col,choosecols(D2:ZZ,index(match(,0/(D1:1<>"")))),
     map(last_col,lambda(Σ,if(Σ="",,Σ/sum(last_col)))))

    enter image description here

    • It will search n' pick the the last month header in row_1 (D1:1)