grouping numbers together
 

Hi,

I really hope someone can help me with this.

I want to know if there is a formula which can group numbers together. I
will explain further:

In my spreadsheet, column T contains 'Ages' of individuals. There is a
different entry in each row between row 3 and 391.
What I would like to do is be able to group all the people within certain
age ranges together and count the number of them that there are, so for
example group all the people together from: 18-29, 30-39, 40-49, 50-59,
60-69, 70+

This will make it a lot easier for me to search through my spreadsheet and
compile this report.

If anybody can help please get back to me.

All the best.

Dominic.

grouping numbers together
 

I am sorry have answered my own question, but have another question to add to
what I asked below...

Firstly in response to my question:

use

= COUNTIF(rangeofdata,"="&18)-COUNTIF(rangeofdata,""&29)

This groups age ranges together so in this instance individual between 18
and 29.


I have done this which is all well and good but I need to now compare this
to another field of information.

so if I am using:

use

= COUNTIF(T3:T391,"="&18)-COUNTIF(T3:T391,""&29)

How can I adapt the formula so that it then searches another column of data
(column K) for the number 1 as well. I.e. So that the formula only provides
the number of individuals that are within a certain age range and also have a
1 in the other column.

I am very much stuck here...

Any help would be amazing.


Dominic

"Dominic_gates" wrote:

Hi,

I really hope someone can help me with this.

I want to know if there is a formula which can group numbers together. I
will explain further:

In my spreadsheet, column T contains 'Ages' of individuals. There is a
different entry in each row between row 3 and 391.
What I would like to do is be able to group all the people within certain
age ranges together and count the number of them that there are, so for
example group all the people together from: 18-29, 30-39, 40-49, 50-59,
60-69, 70+

This will make it a lot easier for me to search through my spreadsheet and
compile this report.

If anybody can help please get back to me.

All the best.

Dominic.

grouping numbers together
 

Something like this should work I believe:

For the 18 to 29 range:

=COUNTIFS(T3:T391,"17",T3:T391,"<30",K3:K391,1)

Adjust accordingly for other age ranges

"Dominic_gates" wrote:

I am sorry have answered my own question, but have another question to add to
what I asked below...

Firstly in response to my question:

use

= COUNTIF(rangeofdata,"="&18)-COUNTIF(rangeofdata,""&29)

This groups age ranges together so in this instance individual between 18
and 29.


I have done this which is all well and good but I need to now compare this
to another field of information.

so if I am using:

use

= COUNTIF(T3:T391,"="&18)-COUNTIF(T3:T391,""&29)

How can I adapt the formula so that it then searches another column of data
(column K) for the number 1 as well. I.e. So that the formula only provides
the number of individuals that are within a certain age range and also have a
1 in the other column.

I am very much stuck here...

Any help would be amazing.


Dominic

"Dominic_gates" wrote:

Hi,

I really hope someone can help me with this.

I want to know if there is a formula which can group numbers together. I
will explain further:

In my spreadsheet, column T contains 'Ages' of individuals. There is a
different entry in each row between row 3 and 391.
What I would like to do is be able to group all the people within certain
age ranges together and count the number of them that there are, so for
example group all the people together from: 18-29, 30-39, 40-49, 50-59,
60-69, 70+

This will make it a lot easier for me to search through my spreadsheet and
compile this report.

If anybody can help please get back to me.

All the best.

Dominic.

grouping numbers together
 

If you have the Analysis Tool Pack installed then you can create a Histogram
of the data. Type the upper value of each group in a list, eg. 29,39 This is
the Bin, select your data and either select a range or have the output on a
new sheet.

HTH
Peter

"Brad Autry" wrote:

Something like this should work I believe:

For the 18 to 29 range:

=COUNTIFS(T3:T391,"17",T3:T391,"<30",K3:K391,1)

Adjust accordingly for other age ranges

"Dominic_gates" wrote:

I am sorry have answered my own question, but have another question to add to
what I asked below...

Firstly in response to my question:

use

= COUNTIF(rangeofdata,"="&18)-COUNTIF(rangeofdata,""&29)

This groups age ranges together so in this instance individual between 18
and 29.


I have done this which is all well and good but I need to now compare this
to another field of information.

so if I am using:

use

= COUNTIF(T3:T391,"="&18)-COUNTIF(T3:T391,""&29)

How can I adapt the formula so that it then searches another column of data
(column K) for the number 1 as well. I.e. So that the formula only provides
the number of individuals that are within a certain age range and also have a
1 in the other column.

I am very much stuck here...

Any help would be amazing.


Dominic

"Dominic_gates" wrote:

Hi,

I really hope someone can help me with this.

I want to know if there is a formula which can group numbers together. I
will explain further:

In my spreadsheet, column T contains 'Ages' of individuals. There is a
different entry in each row between row 3 and 391.
What I would like to do is be able to group all the people within certain
age ranges together and count the number of them that there are, so for
example group all the people together from: 18-29, 30-39, 40-49, 50-59,
60-69, 70+

This will make it a lot easier for me to search through my spreadsheet and
compile this report.

If anybody can help please get back to me.

All the best.

Dominic.

grouping numbers together
 

Hi,

You may also group inside a pivot. Therefore create a pivot and click on
any cell in the ages column of the pivot. On the pivot table toolbar, click
on the pivot table drop down arrow and click on Group and Show detail
Group. Enter the relevant inputs.

Please note that grouping can only be for equal buckets I.e. 20-29, 30-39,
40-49 etc.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dominic_gates" wrote in message
...
Hi,

I really hope someone can help me with this.

I want to know if there is a formula which can group numbers together. I
will explain further:

In my spreadsheet, column T contains 'Ages' of individuals. There is a
different entry in each row between row 3 and 391.
What I would like to do is be able to group all the people within certain
age ranges together and count the number of them that there are, so for
example group all the people together from: 18-29, 30-39, 40-49, 50-59,
60-69, 70+

This will make it a lot easier for me to search through my spreadsheet and
compile this report.

If anybody can help please get back to me.

All the best.

Dominic.

grouping numbers together
 

Hi,

This formula: =COUNTIFS(T3:T391,"17",T3:T391,"<30",K3:K391,1) is
unfortunately returning a: #NAME? error.

Nor can I get the Histogram to work...

Do you have any further suggestions of a possible formula I can use?

Many thanks

Dominic

"Brad Autry" wrote:

Something like this should work I believe:

For the 18 to 29 range:

=COUNTIFS(T3:T391,"17",T3:T391,"<30",K3:K391,1)

Adjust accordingly for other age ranges

"Dominic_gates" wrote:

I am sorry have answered my own question, but have another question to add to
what I asked below...

Firstly in response to my question:

use

= COUNTIF(rangeofdata,"="&18)-COUNTIF(rangeofdata,""&29)

This groups age ranges together so in this instance individual between 18
and 29.


I have done this which is all well and good but I need to now compare this
to another field of information.

so if I am using:

use

= COUNTIF(T3:T391,"="&18)-COUNTIF(T3:T391,""&29)

How can I adapt the formula so that it then searches another column of data
(column K) for the number 1 as well. I.e. So that the formula only provides
the number of individuals that are within a certain age range and also have a
1 in the other column.

I am very much stuck here...

Any help would be amazing.


Dominic

"Dominic_gates" wrote:

Hi,

I really hope someone can help me with this.

I want to know if there is a formula which can group numbers together. I
will explain further:

In my spreadsheet, column T contains 'Ages' of individuals. There is a
different entry in each row between row 3 and 391.
What I would like to do is be able to group all the people within certain
age ranges together and count the number of them that there are, so for
example group all the people together from: 18-29, 30-39, 40-49, 50-59,
60-69, 70+

This will make it a lot easier for me to search through my spreadsheet and
compile this report.

If anybody can help please get back to me.

All the best.

Dominic.

grouping numbers together
 

Hi,

I forgot to state that a Hostogram will not work because the column of ages
also contains "N/A" entries.

I am completely unfamiliar with pivot tables but will have a go with that
now to see if I can make it work.
Otherwise if anybody can offer me a formula or correct the formula suggested
by Brad I would greatly appreciate it.

Many Thanks

Dominic

"Dominic_gates" wrote:

Hi,

This formula: =COUNTIFS(T3:T391,"17",T3:T391,"<30",K3:K391,1) is
unfortunately returning a: #NAME? error.

Nor can I get the Histogram to work...

Do you have any further suggestions of a possible formula I can use?

Many thanks

Dominic

"Brad Autry" wrote:

Something like this should work I believe:

For the 18 to 29 range:

=COUNTIFS(T3:T391,"17",T3:T391,"<30",K3:K391,1)

Adjust accordingly for other age ranges

"Dominic_gates" wrote:

I am sorry have answered my own question, but have another question to add to
what I asked below...

Firstly in response to my question:

use

= COUNTIF(rangeofdata,"="&18)-COUNTIF(rangeofdata,""&29)

This groups age ranges together so in this instance individual between 18
and 29.


I have done this which is all well and good but I need to now compare this
to another field of information.

so if I am using:

use

= COUNTIF(T3:T391,"="&18)-COUNTIF(T3:T391,""&29)

How can I adapt the formula so that it then searches another column of data
(column K) for the number 1 as well. I.e. So that the formula only provides
the number of individuals that are within a certain age range and also have a
1 in the other column.

I am very much stuck here...

Any help would be amazing.


Dominic

"Dominic_gates" wrote:

Hi,

I really hope someone can help me with this.

I want to know if there is a formula which can group numbers together. I
will explain further:

In my spreadsheet, column T contains 'Ages' of individuals. There is a
different entry in each row between row 3 and 391.
What I would like to do is be able to group all the people within certain
age ranges together and count the number of them that there are, so for
example group all the people together from: 18-29, 30-39, 40-49, 50-59,
60-69, 70+

This will make it a lot easier for me to search through my spreadsheet and
compile this report.

If anybody can help please get back to me.

All the best.

Dominic.

grouping numbers together
 

COUNTIFS is only an Excel 2007 function.

For Excel 2003 & earlier, use
=SUMPRODUCT((T3:T39117)*(T3:T391<30)*(K3:K391=1)) or
=SUMPRODUCT(--(T3:T39117),--(T3:T391<30),--(K3:K391=1))
--
David Biddulph

"Dominic_gates" wrote in message
...
Hi,

This formula: =COUNTIFS(T3:T391,"17",T3:T391,"<30",K3:K391,1) is
unfortunately returning a: #NAME? error.

Nor can I get the Histogram to work...

Do you have any further suggestions of a possible formula I can use?

Many thanks

Dominic

"Brad Autry" wrote:

Something like this should work I believe:

For the 18 to 29 range:

=COUNTIFS(T3:T391,"17",T3:T391,"<30",K3:K391,1)

Adjust accordingly for other age ranges

"Dominic_gates" wrote:

I am sorry have answered my own question, but have another question to
add to
what I asked below...

Firstly in response to my question:

use

= COUNTIF(rangeofdata,"="&18)-COUNTIF(rangeofdata,""&29)

This groups age ranges together so in this instance individual between
18
and 29.


I have done this which is all well and good but I need to now compare
this
to another field of information.

so if I am using:

use

= COUNTIF(T3:T391,"="&18)-COUNTIF(T3:T391,""&29)

How can I adapt the formula so that it then searches another column of
data
(column K) for the number 1 as well. I.e. So that the formula only
provides
the number of individuals that are within a certain age range and also
have a
1 in the other column.

I am very much stuck here...

Any help would be amazing.


Dominic

"Dominic_gates" wrote:

Hi,

I really hope someone can help me with this.

I want to know if there is a formula which can group numbers
together. I
will explain further:

In my spreadsheet, column T contains 'Ages' of individuals. There is
a
different entry in each row between row 3 and 391.
What I would like to do is be able to group all the people within
certain
age ranges together and count the number of them that there are, so
for
example group all the people together from: 18-29, 30-39, 40-49,
50-59,
60-69, 70+

This will make it a lot easier for me to search through my
spreadsheet and
compile this report.

If anybody can help please get back to me.

All the best.

Dominic.

grouping numbers together
 

This has worked! :) I am using Excel 2002 so obviously the reason why the
previous forumla did not work.

Many Thanks

Dominic

"David Biddulph" wrote:

COUNTIFS is only an Excel 2007 function.

For Excel 2003 & earlier, use
=SUMPRODUCT((T3:T39117)*(T3:T391<30)*(K3:K391=1)) or
=SUMPRODUCT(--(T3:T39117),--(T3:T391<30),--(K3:K391=1))
--
David Biddulph

"Dominic_gates" wrote in message
...
Hi,

This formula: =COUNTIFS(T3:T391,"17",T3:T391,"<30",K3:K391,1) is
unfortunately returning a: #NAME? error.

Nor can I get the Histogram to work...

Do you have any further suggestions of a possible formula I can use?

Many thanks

Dominic

"Brad Autry" wrote:

Something like this should work I believe:

For the 18 to 29 range:

=COUNTIFS(T3:T391,"17",T3:T391,"<30",K3:K391,1)

Adjust accordingly for other age ranges

"Dominic_gates" wrote:

I am sorry have answered my own question, but have another question to
add to
what I asked below...

Firstly in response to my question:

use

= COUNTIF(rangeofdata,"="&18)-COUNTIF(rangeofdata,""&29)

This groups age ranges together so in this instance individual between
18
and 29.


I have done this which is all well and good but I need to now compare
this
to another field of information.

so if I am using:

use

= COUNTIF(T3:T391,"="&18)-COUNTIF(T3:T391,""&29)

How can I adapt the formula so that it then searches another column of
data
(column K) for the number 1 as well. I.e. So that the formula only
provides
the number of individuals that are within a certain age range and also
have a
1 in the other column.

I am very much stuck here...

Any help would be amazing.


Dominic

"Dominic_gates" wrote:

Hi,

I really hope someone can help me with this.

I want to know if there is a formula which can group numbers
together. I
will explain further:

In my spreadsheet, column T contains 'Ages' of individuals. There is
a
different entry in each row between row 3 and 391.
What I would like to do is be able to group all the people within
certain
age ranges together and count the number of them that there are, so
for
example group all the people together from: 18-29, 30-39, 40-49,
50-59,
60-69, 70+

This will make it a lot easier for me to search through my
spreadsheet and
compile this report.

If anybody can help please get back to me.

All the best.

Dominic.

grouping numbers together
 

Hello Dominic,

Another approach:
Select a sufficiently long area (7 rows, for example) and 2 columns
and array-enter:
=GSort(Pstat("count",NOT(ISERROR(T3:T391)),"="&LO OKUP(T3:T391,
{18,30,40,50,60,70})))

Pstat you will find he
http://sulprobil.com/html/pstat.html
GSort is he
http://sulprobil.com/html/sort_vba.html

Regards,
Bernd