MAX, MIN, MEAN, AVERAGE, MODE for multiple groups...how?

CLARIFICATION: ?

Formula needs to
I am looking to create several formulas which would be applied to a range of
cells. ($G$2:$G$13) where the row numbers would change. I need a second
formula which would (lookup?) column A (will be sorted already) to discover
how many rows pertain to to a particular value (area#).
Must return the beginning row number and ending row number corresponding to
each set of area #'s.

I am hoping to thenbe able to have these discovered ranges applied to my
other formulas
$G$2:$G$13, $G14:$G22, $G23:$G49, $G50:$G87 etc.
(Say my sheet had column A with 4 different values for "Area#" Rows 2-13
value 1, Rows 14-22 value 2, Rows 23-49 value 3, Rows 50-87 value 4.)

Is there any hope to accomplish this? I am will ing to reformat, use
different number of cells, sort, or what ever. I am trying to avoid
downloading 576 separate excel files, and rather DL one HUGE file to perform
all the calculations on.

Thank You!



"|| cypher ||" <remove.cypher_ftp@not.this.part.yahoo.com> wrote in message
news:vbidnRheTMLE8UjfRVn-2w@wideopenwest.com...
>I am wondering if there is an easy way to find the MIN, MAX, MEDIAN,
>AVERAGE, MODE for housing prices in different areas. I am able to export
>data to EXCEL format and currently, I laboriously have to DL excel sheets
>for 1 "AREA" at a time and paste a 5 cell template of formulas on row 1
>to caculate those values successfully.
>
> Instead of setting up the software and downloading 1 area at a time, I am
> able to select multiple areas and then export them to an excel sheet. one
> of the columns in the sheet contains the AREA NUMBER.
>
> It would be exponentially easier to export MULTIPLE AREAS, and somehow
> perform these calculations (which I don't know how to set up) which would
> lookup a column, say column d, and then calculate MIN, MAX, MEDIAN,
> AVERAGE, MODE for each set of areas.
>
> small sample data: (TAB SEPARATED)
>
> AREA LIST PRICE
> 101 100,000
> 101 125,000
> 101 212,000
> 101 225,000
> 101 232,500
> 235 199,900
> 235 212,500
> 235 229,000
> 235 249,000
> 322 89,500
> 322 99,999
> 322 102,850
>
>
> I wonder if there is a way to examine column A, detect how many different
> values there are (EXAMPLE 3) and then detect how many rows correspond to
> each value (EXAMPLE: 101=5, 235=4, 322=3) and then use that information to
> perform the MIN, MAX, MEDIAN, AVERAGE, MODE to the corresponding column
> which in the example would be column B.
>
> I apollogize as I am a novice with more complicated formulas, I am hoping
> the solution will pop right out for some of you here in this forum.
>
> Thanks!
> -cypher
>

Assuming that Columns A and B contain your data and the first row
contains your headers/labels, enter your list of 'Areas' in a column,
let's say Column D, starting at D2, then try...

E2, copied down:

=MIN(IF($A$2:$A$13=D2,$B$2:$B$13))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Replace MIN with
the other functions you want, such as MAX, MEDIAN, etc.

Hope this helps!

In article <vbidnRheTMLE8UjfRVn-2w@wideopenwest.com>,
"|| cypher ||" <remove.cypher_ftp@not.this.part.yahoo.com> wrote:

> I am wondering if there is an easy way to find the MIN, MAX, MEDIAN,
> AVERAGE, MODE for housing prices in different areas. I am able to export
> data to EXCEL format and currently, I laboriously have to DL excel sheets
> for 1 "AREA" at a time and paste a 5 cell template of formulas on row 1
> to caculate those values successfully.
>
> Instead of setting up the software and downloading 1 area at a time, I am
> able to select multiple areas and then export them to an excel sheet. one
> of the columns in the sheet contains the AREA NUMBER.
>
> It would be exponentially easier to export MULTIPLE AREAS, and somehow
> perform these calculations (which I don't know how to set up) which would
> lookup a column, say column d, and then calculate MIN, MAX, MEDIAN, AVERAGE,
> MODE for each set of areas.
>
> small sample data: (TAB SEPARATED)
>
> AREA LIST PRICE
> 101 100,000
> 101 125,000
> 101 212,000
> 101 225,000
> 101 232,500
> 235 199,900
> 235 212,500
> 235 229,000
> 235 249,000
> 322 89,500
> 322 99,999
> 322 102,850
>
>
> I wonder if there is a way to examine column A, detect how many different
> values there are (EXAMPLE 3) and then detect how many rows correspond to
> each value (EXAMPLE: 101=5, 235=4, 322=3) and then use that information to
> perform the MIN, MAX, MEDIAN, AVERAGE, MODE to the corresponding column
> which in the example would be column B.
>
> I apollogize as I am a novice with more complicated formulas, I am hoping
> the solution will pop right out for some of you here in this forum.
>
> Thanks!
> -cypher

CLARIFICATION: ?

Formula needs to
I am looking to create several formulas which would be applied to a range of
cells. ($G$2:$G$13) where the row numbers would change. I need a second
formula which would (lookup?) column A (will be sorted already) to discover
how many rows pertain to to a particular value (area#).
Must return the beginning row number and ending row number corresponding to
each set of area #'s.

I am hoping to thenbe able to have these discovered ranges applied to my
other formulas
$G$2:$G$13, $G14:$G22, $G23:$G49, $G50:$G87 etc.
(Say my sheet had column A with 4 different values for "Area#" Rows 2-13
value 1, Rows 14-22 value 2, Rows 23-49 value 3, Rows 50-87 value 4.)

Is there any hope to accomplish this? I am will ing to reformat, use
different number of cells, sort, or what ever. I am trying to avoid
downloading 576 separate excel files, and rather DL one HUGE file to perform
all the calculations on.

Thank You!



"|| cypher ||" <remove.cypher_ftp@not.this.part.yahoo.com> wrote in message
news:vbidnRheTMLE8UjfRVn-2w@wideopenwest.com...
>I am wondering if there is an easy way to find the MIN, MAX, MEDIAN,
>AVERAGE, MODE for housing prices in different areas. I am able to export
>data to EXCEL format and currently, I laboriously have to DL excel sheets
>for 1 "AREA" at a time and paste a 5 cell template of formulas on row 1
>to caculate those values successfully.
>
> Instead of setting up the software and downloading 1 area at a time, I am
> able to select multiple areas and then export them to an excel sheet. one
> of the columns in the sheet contains the AREA NUMBER.
>
> It would be exponentially easier to export MULTIPLE AREAS, and somehow
> perform these calculations (which I don't know how to set up) which would
> lookup a column, say column d, and then calculate MIN, MAX, MEDIAN,
> AVERAGE, MODE for each set of areas.
>
> small sample data: (TAB SEPARATED)
>
> AREA LIST PRICE
> 101 100,000
> 101 125,000
> 101 212,000
> 101 225,000
> 101 232,500
> 235 199,900
> 235 212,500
> 235 229,000
> 235 249,000
> 322 89,500
> 322 99,999
> 322 102,850
>
>
> I wonder if there is a way to examine column A, detect how many different
> values there are (EXAMPLE 3) and then detect how many rows correspond to
> each value (EXAMPLE: 101=5, 235=4, 322=3) and then use that information to
> perform the MIN, MAX, MEDIAN, AVERAGE, MODE to the corresponding column
> which in the example would be column B.
>
> I apollogize as I am a novice with more complicated formulas, I am hoping
> the solution will pop right out for some of you here in this forum.
>
> Thanks!
> -cypher
>

Assuming that Columns A and B contain your data and the first row
contains your headers/labels, enter your list of 'Areas' in a column,
let's say Column D, starting at D2, then try...

E2, copied down:

=MIN(IF($A$2:$A$13=D2,$B$2:$B$13))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Replace MIN with
the other functions you want, such as MAX, MEDIAN, etc.

Hope this helps!

In article <vbidnRheTMLE8UjfRVn-2w@wideopenwest.com>,
"|| cypher ||" <remove.cypher_ftp@not.this.part.yahoo.com> wrote:

> I am wondering if there is an easy way to find the MIN, MAX, MEDIAN,
> AVERAGE, MODE for housing prices in different areas. I am able to export
> data to EXCEL format and currently, I laboriously have to DL excel sheets
> for 1 "AREA" at a time and paste a 5 cell template of formulas on row 1
> to caculate those values successfully.
>
> Instead of setting up the software and downloading 1 area at a time, I am
> able to select multiple areas and then export them to an excel sheet. one
> of the columns in the sheet contains the AREA NUMBER.
>
> It would be exponentially easier to export MULTIPLE AREAS, and somehow
> perform these calculations (which I don't know how to set up) which would
> lookup a column, say column d, and then calculate MIN, MAX, MEDIAN, AVERAGE,
> MODE for each set of areas.
>
> small sample data: (TAB SEPARATED)
>
> AREA LIST PRICE
> 101 100,000
> 101 125,000
> 101 212,000
> 101 225,000
> 101 232,500
> 235 199,900
> 235 212,500
> 235 229,000
> 235 249,000
> 322 89,500
> 322 99,999
> 322 102,850
>
>
> I wonder if there is a way to examine column A, detect how many different
> values there are (EXAMPLE 3) and then detect how many rows correspond to
> each value (EXAMPLE: 101=5, 235=4, 322=3) and then use that information to
> perform the MIN, MAX, MEDIAN, AVERAGE, MODE to the corresponding column
> which in the example would be column B.
>
> I apollogize as I am a novice with more complicated formulas, I am hoping
> the solution will pop right out for some of you here in this forum.
>
> Thanks!
> -cypher

I am wondering if there is an easy way to find the MIN, MAX, MEDIAN,
AVERAGE, MODE for housing prices in different areas. I am able to export
data to EXCEL format and currently, I laboriously have to DL excel sheets
for 1 "AREA" at a time and paste a 5 cell template of formulas on row 1
to caculate those values successfully.

Instead of setting up the software and downloading 1 area at a time, I am
able to select multiple areas and then export them to an excel sheet. one
of the columns in the sheet contains the AREA NUMBER.

It would be exponentially easier to export MULTIPLE AREAS, and somehow
perform these calculations (which I don't know how to set up) which would
lookup a column, say column d, and then calculate MIN, MAX, MEDIAN, AVERAGE,
MODE for each set of areas.

small sample data: (TAB SEPARATED)

AREA LIST PRICE
101 100,000
101 125,000
101 212,000
101 225,000
101 232,500
235 199,900
235 212,500
235 229,000
235 249,000
322 89,500
322 99,999
322 102,850


I wonder if there is a way to examine column A, detect how many different
values there are (EXAMPLE 3) and then detect how many rows correspond to
each value (EXAMPLE: 101=5, 235=4, 322=3) and then use that information to
perform the MIN, MAX, MEDIAN, AVERAGE, MODE to the corresponding column
which in the example would be column B.

I apollogize as I am a novice with more complicated formulas, I am hoping
the solution will pop right out for some of you here in this forum.

Thanks!
-cypher

CLARIFICATION: ?

Formula needs to
I am looking to create several formulas which would be applied to a range of
cells. ($G$2:$G$13) where the row numbers would change. I need a second
formula which would (lookup?) column A (will be sorted already) to discover
how many rows pertain to to a particular value (area#).
Must return the beginning row number and ending row number corresponding to
each set of area #'s.

I am hoping to thenbe able to have these discovered ranges applied to my
other formulas
$G$2:$G$13, $G14:$G22, $G23:$G49, $G50:$G87 etc.
(Say my sheet had column A with 4 different values for "Area#" Rows 2-13
value 1, Rows 14-22 value 2, Rows 23-49 value 3, Rows 50-87 value 4.)

Is there any hope to accomplish this? I am will ing to reformat, use
different number of cells, sort, or what ever. I am trying to avoid
downloading 576 separate excel files, and rather DL one HUGE file to perform
all the calculations on.

Thank You!



"|| cypher ||" <remove.cypher_ftp@not.this.part.yahoo.com> wrote in message
news:vbidnRheTMLE8UjfRVn-2w@wideopenwest.com...
>I am wondering if there is an easy way to find the MIN, MAX, MEDIAN,
>AVERAGE, MODE for housing prices in different areas. I am able to export
>data to EXCEL format and currently, I laboriously have to DL excel sheets
>for 1 "AREA" at a time and paste a 5 cell template of formulas on row 1
>to caculate those values successfully.
>
> Instead of setting up the software and downloading 1 area at a time, I am
> able to select multiple areas and then export them to an excel sheet. one
> of the columns in the sheet contains the AREA NUMBER.
>
> It would be exponentially easier to export MULTIPLE AREAS, and somehow
> perform these calculations (which I don't know how to set up) which would
> lookup a column, say column d, and then calculate MIN, MAX, MEDIAN,
> AVERAGE, MODE for each set of areas.
>
> small sample data: (TAB SEPARATED)
>
> AREA LIST PRICE
> 101 100,000
> 101 125,000
> 101 212,000
> 101 225,000
> 101 232,500
> 235 199,900
> 235 212,500
> 235 229,000
> 235 249,000
> 322 89,500
> 322 99,999
> 322 102,850
>
>
> I wonder if there is a way to examine column A, detect how many different
> values there are (EXAMPLE 3) and then detect how many rows correspond to
> each value (EXAMPLE: 101=5, 235=4, 322=3) and then use that information to
> perform the MIN, MAX, MEDIAN, AVERAGE, MODE to the corresponding column
> which in the example would be column B.
>
> I apollogize as I am a novice with more complicated formulas, I am hoping
> the solution will pop right out for some of you here in this forum.
>
> Thanks!
> -cypher
>

Assuming that Columns A and B contain your data and the first row
contains your headers/labels, enter your list of 'Areas' in a column,
let's say Column D, starting at D2, then try...

E2, copied down:

=MIN(IF($A$2:$A$13=D2,$B$2:$B$13))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Replace MIN with
the other functions you want, such as MAX, MEDIAN, etc.

Hope this helps!

In article <vbidnRheTMLE8UjfRVn-2w@wideopenwest.com>,
"|| cypher ||" <remove.cypher_ftp@not.this.part.yahoo.com> wrote:

> I am wondering if there is an easy way to find the MIN, MAX, MEDIAN,
> AVERAGE, MODE for housing prices in different areas. I am able to export
> data to EXCEL format and currently, I laboriously have to DL excel sheets
> for 1 "AREA" at a time and paste a 5 cell template of formulas on row 1
> to caculate those values successfully.
>
> Instead of setting up the software and downloading 1 area at a time, I am
> able to select multiple areas and then export them to an excel sheet. one
> of the columns in the sheet contains the AREA NUMBER.
>
> It would be exponentially easier to export MULTIPLE AREAS, and somehow
> perform these calculations (which I don't know how to set up) which would
> lookup a column, say column d, and then calculate MIN, MAX, MEDIAN, AVERAGE,
> MODE for each set of areas.
>
> small sample data: (TAB SEPARATED)
>
> AREA LIST PRICE
> 101 100,000
> 101 125,000
> 101 212,000
> 101 225,000
> 101 232,500
> 235 199,900
> 235 212,500
> 235 229,000
> 235 249,000
> 322 89,500
> 322 99,999
> 322 102,850
>
>
> I wonder if there is a way to examine column A, detect how many different
> values there are (EXAMPLE 3) and then detect how many rows correspond to
> each value (EXAMPLE: 101=5, 235=4, 322=3) and then use that information to
> perform the MIN, MAX, MEDIAN, AVERAGE, MODE to the corresponding column
> which in the example would be column B.
>
> I apollogize as I am a novice with more complicated formulas, I am hoping
> the solution will pop right out for some of you here in this forum.
>
> Thanks!
> -cypher