Show the presence of FALSE/TRUE in the row cells and determine the proportion of the row in the row range
Please tell me how to create a formula for fulfilling the condition:
- If the cell value is "True", then output "1", if "False", then "0". Thus, you need to check several cells to get a value (for example, "101" when checking three cells).
- The second formula should calculate the percentage of each of these values, for example: "000" - 15%, "001" - 20% and td.
Result #1 is the 4th column. Result #2 is essentially a separate table with percentages for each type of the 4th column from result #1
2 answers
There are three tasks in the question.
- Show the "code" that displays the position FALSE/TRUE in the range (column D in the figure).
If the range is small, a simple formula is sufficient:
=ТЕКСТ(A2*100+B2*10+C2;"000")
Or
=--A2&--B2&--C2
In the first formula, we form a number and convert it to text, in the second-we convert logical expressions to 1/0 and combine it into the text
For large is applicable formula:
=ТЕКСТ(СУММПРОИЗВ(A2:C2*10^(СЧЁТЗ(A2:C2)-СТОЛБЕЦ(A2:C2)));ПОВТОР(0;СЧЁТЗ(A2:C2)))
- Get unique values.
2.1. apply the add. calculations:
== defining the unique (column E in the figure)
=ЕСЛИ(СЧЁТЕСЛИ($D$2:D2;D2)=1;МАКС($E$1:E1)+1)
= = get the list (column F in the figure):
=ЕСЛИ(СТРОКА(A1)>МАКС($E$2:$E$6);"";ИНДЕКС($D$2:$D$6;ПОИСКПОЗ(СТРОКА(A1);$E$2:$E$6);))
2.2. extract the unique single formula (column F in the figure):
=ЕСЛИОШИБКА(ИНДЕКС($D$2:$D$6;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($F$1:F1;$D$2:$D$6)=0;СТРОКА($D$2:$D$6)-1);1));"")
The array formula is entered by simultaneously pressing Ctrl+Shift+Enter, the formula should be framed by curly brackets. after entering the formula in one cell, copy the cell to the range.
- Determining the weight (fraction) of each value (column G in the figure).
It's simple here:
=ЕСЛИ(F2="";"";СЧЁТЕСЛИ($D$2:$D$6;F2)/СЧЁТЗ($D$2:$D$6))
The logic is as follows:
1) Multiply each of the three cells by 4, 2, and 1, respectively, and add the resulting numbers. Then you apply the conversion to the binary system, there is such a function, I do not remember the exact name.
2) Apply СЧЁТЕСЛИ (COUNTIF) for each option, then calculate the percentage of each of the obtained values.