Excel 2016 getting statistics out of distribution of A B C

Question

If i have a list of value groups of A and B. (example ? was equal to 1.2 in the morning (A), which became 1.7 in the evening (B)

And I need to divide them in groups of :

X = A < B

Y = A > B

Z = A = B

How do I calculate the percentage of X Y and Z occurring out of the total?

And how do I make a chart out of it ?

Answer 1

We're going to use the same answer for X,Y and Z and then you only have to adjust the <,> or =. For X use this formula assuming your data is in A2:A20 for A and B2:B20 for B.

=SUM(IF(A2:A20<B2:B20,1,0))/COUNT(A2:A20)

And then press ctrl+shift+enter because it's an array formula.

How does it work? The if function produces a 1 when the condition you want is true and a 0 when it's false. You then count the 1's and divide it by the number of entries. You need to use an array function, because otherwise the if function only takes the first value of the array.