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

1

I have my championship leaderboard, which has a line " Number of laps per race " and below " laps completed ".

I want to go through all the values in the line " Completed laps" and compare with " 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 not make it work. I tried with CONT.SE , but it seems to only work with some specific value!

How to work out this formula?

    
asked by anonymous 30.03.2016 / 00:10

2 answers

2

You can use the SOMA formula with the SE formula using the vector feature.

In order 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 by comparing a range of cells with another range.

Aftertypingtheformula,typeCTRL+SHIFT+ENTERtouseasarrayformula.

Stepbystep,Excelwillsolvethisfunctionasfollows:

  • A2:A5={3,2,1,0}
  • B2:B5={5,2,10,3}
  • B2:B5*0.35={5,2,10,3}*0.35={1.75,0.7,3.5,1.05}
  • A2:A5
  • 30.03.2016 / 23:57
    0

    Bruno, if I understood, I would do the following:

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

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