Home |
Search |
Today's Posts |
#1
|
|||
|
|||
MAX, MIN, MEAN, AVERAGE, MODE for multiple groups...how?
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 |
#2
|
|||
|
|||
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 ||" wrote in message ... 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 |
#3
|
|||
|
|||
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 , "|| cypher ||" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
What is this kind of average called? | Excel Worksheet Functions | |||
average function in Excel 2002 | New Users to Excel | |||
Combo Box goes to edit mode even if design mode is in OFF position | Excel Discussion (Misc queries) | |||
coverting answer from Radian mode to degree mode | Excel Worksheet Functions |