Count elements between two quantities

6

I am creating a dashboard demographic for a religious organization, which will include the age group of its members among other data.

The enrollment and promotion ages among your religious education classes will be used as criteria.

Then:

0 a 10 anos
11 a 17 anos
18 a 35 anos
36 a 50 anos
50 anos ou mais

In the spreadsheet with the demographic data I created a table named RolDeMembros , its column Idade returns the result of the formula below by line:

=CONCATENAR(SOMA(ANO(HOJE())-ANO([@[Dt. Nasc.]]));" anos")

OnathirdworksheetIkeepconsolidateddemographics.

Atageranges,I'vetriedusing=CONT.SE()tocalculateeachofthetracks,butI'mnotgettingtheexpecteddata.Foreachtrack,I'musingthefollowing:

=CONT.SE(RolDeMembros[Idade];"<=10")
=CONT.SE(RolDeMembros[Idade];">=11<=17")
=CONT.SE(RolDeMembros[Idade];">=18<=35")
=CONT.SE(RolDeMembros[Idade];">=36<=50")
=CONT.SE(RolDeMembros[Idade];">=51")

The result I get is the following:

What is totally wrong. 169 is the number of members of the organization, those over 50 count 62 and so on. All wrong.

Where am I going wrong?

    
asked by anonymous 13.11.2016 / 20:11

3 answers

2

Your project has two errors.

Problem 1

As already answered, you can not use more than one comparison criterion in the CONT.SE function. To do this, you need to use the CONT.SES function (which allows you to add multiple ranges and criteria). In your case the range is the same, so you repeat, and only add the "new" criterion. So, what did you envision as:

=CONT.SE(RolDeMembros[Idade];">=11<=17")

It looks like:

=CONT.SES(RolDeMembros[Idade];">=11";RolDeMembros[Idade];"<=17")

Problem 2

Issue 1 had already been answered by other colleagues, but I bet it still did not work for you. Is that still the problem (which I commented ) of that the data in your "Age" column are not numeric . The function you use to mount this column is:

=CONCATENAR(SOMA(ANO(HOJE())-ANO([@[Dt. Nasc.]]));" anos")

And the problem is precisely in the fact that it assembles a string (of type "35 anos" ) and returns in the column. Therefore, the column contains text and not numbers. So your comparison does not work. Ideally, you should keep only the same numbers in this column. What's simple, just change your above function to:

=SOMA(ANO(HOJE())-ANO([@[Dt. Nasc.]]))

The "problem" of not displaying the text "years" up front is simple to resolve in formatting. Access the cell formatting (right click, choose "Format cells"), go to custom formatting, and type 00 "anos" :

Ready!SotheformulasusingCONT.SESwillworkcorrectly:

    
14.11.2016 / 18:19
0

Of course, since there is text in your formula, it is ideal to create a new column with only the numbers (ages) to create the desired frame.

Then use the following formulas:

=CONT.SE(RolDeMembros[Idade];"<"&11)
=CONT.SES(RolDeMembros[Idade];">="&11;RolDeMembros[Idade];"<"&18)
=CONT.SES(RolDeMembros[Idade];">="&18;RolDeMembros[Idade];"<"&36)
=CONT.SES(RolDeMembros[Idade];">="&36;RolDeMembros[Idade];"<"&51)
=CONT.SE(RolDeMembros[Idade];">="&51)

You can use the number together as you did "", however I used & (Commercial E) so you can have an age group table .

See this example:

link

I used something similar in this worksheet with the price range of CAESB (DF Water Supply Service).

link

Another thing you can use is the type of formula for calculating age, here are a few more common examples available from Microsoft:

link

    
13.11.2016 / 21:18
0

To process the alphanumeric data presented in the worksheet, replace "age" with:

ESQUERDA(idade;PROCURAR(" ";idade)-1)

This formula takes the text "years" and only the numerical part of the age will be considered.

The formulas would look like this in my example, for the alphanumeric data you are using for age.

=CONT.SE(ESQUERDA(Idade;PROCURAR(" ";Idade)-1);"<=10")
=CONT.SES(ESQUERDA(Idade;PROCURAR(" ";Idade)-1);">=11";ESQUERDA(Idade;PROCURAR(" ";Idade)-1);"<18")
=CONT.SES(ESQUERDA(Idade;PROCURAR(" ";Idade)-1);">=18";ESQUERDA(Idade;PROCURAR(" ";Idade)-1);"<35")
=CONT.SES(ESQUERDA(Idade;PROCURAR(" ";Idade)-1);">=36";ESQUERDA(Idade;PROCURAR(" ";Idade)-1);"<50")
=CONT.SE(ESQUERDA(Idade;PROCURAR(" ";Idade)-1);">=51")

Just adapt your formulas to apply to the table.

The following example is without table application and with numeric data for the ages:

Select the column with the ages, and in the upper left of Excel , where the address of the active cell appears (column and row, for example B22), type the column name: strong> Age

I did this for the data below:

InanotherABAputthefollowingformulas,whichintheexamplebelowalreadyhavetheresulttotherightofthem:

=CONT.SE(Idade;"<=10")                    10
=CONT.SES(Idade;">=11";Idade;"<18")        7
=CONT.SES(Idade;">=18";Idade;"<35")       17
=CONT.SES(Idade;">=36";Idade;"<50")       14
=CONT.SE(Idade;">=51")                    10

Note that when naming the data range (same as the selected column in this case), Excel even understands that ABA

If the term "spreadsheet" indicates that you are using separate files ( XLSX ), just adapt what I have presented.

That's it.

    
14.11.2016 / 13:24