I
I
Ilya2021-04-17 17:48:33
Google Apps Script
Ilya, 2021-04-17 17:48:33

How to calculate share without intermediate column?

There is a scoreboard of players. In each event, players score points. You need to calculate the average percentage of each player per month. That is, the contribution of each player for each event is divided by the number of events. Columns with events will be added as events appear, their final number is unknown.

The percentage (contribution of each player to a particular event) is calculated in separate columns:
=ArrayFormula(C2:C/SUM(C2:C)) = ArrayFormula
(D2:D/SUM(D2:D))

percentage columns. (E1:1)
=ARRAYFORMULA(SUMIF(IF(COLUMN(E1:1),ROW(E2:E)),ROW(E2:E),E2:E)/COLUMNS(E1:1))

Task: get rid of intermediate calculations (columns with percentages) so that in the array formula in cell A2, not columns with percentages E:F, but columns with initial data C:D are used for calculation.

Table example

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander Ivanov, 2021-04-28
@kinen

6089a3d8e7bd2069729294.png
Well, it's probably the MMULTresultsMMULT

=INDEX(
  MMULT(
    N(ARRAY_CONSTRAIN(C2:Z/(MMULT(TRANSPOSE(ROW(C2:Z)^0);N(C2:Z)));
    MATCH(2;1/(B2:B<>"");1);
    MATCH(2;1/(C1:1<>"");1)));
    SEQUENCE(MATCH(2;1/(C1:1<>"");1);1)^0)/MATCH(2;1/(C1:1<>"");1)
)

An example and useful links in the Table.
The technique MATCH(2;1/(B2:B<>"");1)may be redundant in the sense that it is a search for the last valid value to trim. If you contain sequences of event titles and players without spaces, then the following is valid
=INDEX(
  MMULT(
    N(ARRAY_CONSTRAIN(C2:Z/(MMULT(TRANSPOSE(ROW(C2:Z)^0);N(C2:Z)));
    COUNTA(B2:B);
    COUNTA(C1:1)));
    SEQUENCE(COUNTA(C1:1);1)^0)/COUNTA(C1:1)
)

The diagram on the screen is not accidental. It shows that the total calculation weight is 100%, as a test.
I didn’t bother with the script, but, trusting Grigory Boev , I think that the custom function, if properly developed, will be more flexible. The choice depends on the end goal and cycles of results development and future improvements to the Table.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question