How to count Planilhagoogle

Asked

Viewed 57 times

1

I’m trying to count some data in the spreadsheet to make a statistic, however, it’s not working:

Input data, in cells of B1 to B6

4.75x
5.76x
1.66x
11.17x
20.76x

Result I want to get

<= 1.99x = 1
Greater than >1.99x and < 2.99x = 0
Greater than >2.99x and <> 3.99x = 0
Greater than > 4 = 4

Obs: I’m using the formulas in A8, A9, A10 e A11

=COUNTIF(A1:A5;"<1.99x") 
=COUNTIF(A1:A5;"<2.99x")-A8
=COUNTIF(S1:A1:A5;">2.99x")-A11
=COUNTIF(A1:A5;">4.99x")

Who can help I thank.

google-drive-api

  • It explains the input data better, and whatever comes back after the formula. From what is written, you are counting and not adding to check if you are in the condition, or would, assign a value to each statistic result as a rule?

  • I want to count how many results is less than 1.99x, is between 2.00x and 2.99x, is between 3.00x and 3.99x and Mair than 4.00x

  • I want to count how many results is less than 1.99x, is between 2.00x and 2.99x, is between 3.00x and 3.99x and Mair than 4.00x. In the example above the results above 9.99 falls among the statistics less than 4....=(

1 answer

1


To make it simpler, first let’s take the "x" of the number, so I converted the values to the column B, house line, of B1 to B5, using the formula:

=LEFT($A1;LEN($A1)-1)*1

Then we have to 4.75x has 5 characters, less 1, stays 4.75. Thus removing the X, and the *1 to convert to Number. And we also have to convert numbers with . for numbers with ,. I suggest using Find and Replace Ctrl+H of Google itself.

Instead of using the COUNTIF we will use the COUNTIFS, which returns the count of an interval, depending on several criteria.

So, according to your data lines B8 to B11, stay with the formulas:

=COUNTIF(B1:B5;"<=1,99")
=COUNTIFS(B1:B5;">1,99";B1:B5;"<2,99")
=COUNTIFS(B1:B5;">2,99";B1:B5;"<3,99")
=COUNTIF(B1:B5;">4")

Functions used:
LEN = Returns the size of a string.
LEFT = Returns a substring from the beginning of a specified string.
COUNTIFS = Returns the count of a range, depending on several criteria.

Following example: How to count Google Sheets

  • 1

    Thank you David, very well explained. I just changed the LEN to -4 because the result for statistics comes with "." instead of "," there was giving error, as I just need to do the count I needed to just pick up the decimal before the point. Success and Thanks!!!

  • 1

    @Rodrigoaraujo, you can mark the answer as accepted?

  • 1

    I don’t know where to score?

Browser other questions tagged google-drive-api

You are not signed in. Login or sign up in order to post.