How to compare the values of one interval with another, following a formula?

Question

I have my championship leaderboard, which has a row "Number of Laps per Race" and, below, "Turns Completed".

I want to go through all the values of the line "Turns Completed" and compare with the "Number of laps per race", with the following logic:

se Voltas Completadas < (Voltas por corrida*0,35 ), 
    retorna 1(quantas vezes ocorrer) na célula, 
senão, 
    retorna 0

But I can’t make it work. I tried with CONT.SE, but it seems to only work with some specific value!

How to make this formula?

Answer 1

You can use the formula SOMA with the formula SE, using the vector resource.

In this case, to count the number of rows that meet the criterion "The number of complete laps must be less than 35% of the number of laps per race", we use the SE function comparing a range of cells with another range.

After typing the formula, press CTRL+SHIFT+ENTER to use as matrix formula.

Step by step, Excel will solve this function as follows:

1. A2:A5 = {3,2,1,0}
2. B2:B5 = {5,2,10,3}
3. B2:B5*0,35 = {5,2,10,3} * 0,35 = {1.75, 0.7, 3.5, 1.05}
4. A2:A5 < B2:B5*0,35 = {3,2,1,0} < {1.75, 0.7, 3.5, 1.05} = {3 < 1.75, 2 < 0.7, 1 < 3.5, 0 < 1.05}.
5. We put to return 1 when True and 0 to False. So:
   • {3 < 1.75, 2 < 0.7, 1 < 3.5, 0 < 1.05} = {0, 0, 1, 1}
6. In the end, you’ll have =SOMA({0, 0, 1, 1}), that will result in 2.

To learn more:

• https://support.office.com/pt-br/article/Diretrizes-e-exemplos-de-f%C3%B3rmulas-de-matriz-3be0c791-3f89-4644-a062-8e6e9ecee523

Answer 2

Bruno, if I understood you, I’d do the following:

In a cell you would use CONT.SE to know the total of completed laps, and compare this value with the number of laps. That’s it?

=CONT.SE(A1:Z1;">0")   para valores numéricos nas células (pode ser qualquer valor maior que zero)