Tough one, for me anyway....

4

43fan

For the great people here though? Probably a piece of cake. :)

I have a series of data, something like this:
A B C D E
HTW-C59 289 CAW 399.29 3.73
HTW-C59 289 MPR 397.51 4.04
HTW-C59 289 REM 397.27 4.07
HTW-C59 289 REM 397.23 4.08

HTW-170 4D2042 MPR 406.96 3.84
HTW-170 4D2042 REM 408.17 4.07
HTW-167 4D2042 BKM 392.74 4.11
HTW-167 4D2042 MPR 379.42 4.11
HTW-169 6D4012 VLC 394.53 3.96
HTW-169 6D4012 HAB 395.99 4.73


I need to perform standard deviation and averages for column D, for all the
same values in column B. I need the actual value(not the calculation)
stored in the last row of each "set" where column B is equal, say in columns
E and F.

There are more of each values than listed above, just wanted to keep the
list short if possible, and there are differing numbers of each value in B.
Meaning, say from above, there might be 35 289's, but 48 6D4012's.

The next thing would be to delete all the rest of the rows for that "set",
keeping just the row that has the average and std dev values in them, but
that can be done manually if necessary. Again, as long as the value is in
columns E and F and not the calc.

Any help???

Thanks!!
Shawn
PS - I hope just the text values came through from what I pasted above, and
not something in HTML. If it did come thru in HTML though, PLEASE accept my
apology.
 
G

Guest

First of all why don't you try to perform an import so the structure is as
much clear as possible?.
 
R

Ron Rosenfeld

For the great people here though? Probably a piece of cake. :)

I have a series of data, something like this:
A B C D E
HTW-C59 289 CAW 399.29 3.73
HTW-C59 289 MPR 397.51 4.04
HTW-C59 289 REM 397.27 4.07
HTW-C59 289 REM 397.23 4.08

HTW-170 4D2042 MPR 406.96 3.84
HTW-170 4D2042 REM 408.17 4.07
HTW-167 4D2042 BKM 392.74 4.11
HTW-167 4D2042 MPR 379.42 4.11
HTW-169 6D4012 VLC 394.53 3.96
HTW-169 6D4012 HAB 395.99 4.73


I need to perform standard deviation and averages for column D, for all the
same values in column B. I need the actual value(not the calculation)
stored in the last row of each "set" where column B is equal, say in columns
E and F.

There are more of each values than listed above, just wanted to keep the
list short if possible, and there are differing numbers of each value in B.
Meaning, say from above, there might be 35 289's, but 48 6D4012's.

The next thing would be to delete all the rest of the rows for that "set",
keeping just the row that has the average and std dev values in them, but
that can be done manually if necessary. Again, as long as the value is in
columns E and F and not the calc.

Any help???

Thanks!!
Shawn
PS - I hope just the text values came through from what I pasted above, and
not something in HTML. If it did come thru in HTML though, PLEASE accept my
apology.
Click to expand...

I think the SUBTOTAL wizard will help.

1. Ensure you have Column Labels
2. Sort your data by column B
3. Select some cell in the data table.
4. Data/Subtotals
At each change in: B
Use function: Average
Add subtotal to: D

<OK>

5. Data/Subtotals
At each change in: B
Use function: Average
Add subtotal to: D

***ENSURE -- Replace Current Subtotals -- IS DESLECTED

<OK>

6. You can then collapse the results and copy just the visible cells to your
report:
Select the cells
Edit/GoTo/Special and select Visible Cells
Copy
Paste Special: Values



--ron
 
4

43fan

Subtotal wizard?????


Ron Rosenfeld said:
I think the SUBTOTAL wizard will help.

1. Ensure you have Column Labels
2. Sort your data by column B
3. Select some cell in the data table.
4. Data/Subtotals
At each change in: B
Use function: Average
Add subtotal to: D

<OK>

5. Data/Subtotals
At each change in: B
Use function: Average
Add subtotal to: D

***ENSURE -- Replace Current Subtotals -- IS DESLECTED

<OK>

6. You can then collapse the results and copy just the visible cells to your
report:
Select the cells
Edit/GoTo/Special and select Visible Cells
Copy
Paste Special: Values



--ron
Click to expand...
 
R

Ron Rosenfeld

Oops... found it, trying that out now. Thanks Ron!
Click to expand...

Here's a result I got using your posted data. But there would be many ways to
format this?


Code / Parameter Value

289 StdDev 0.9845
289 Average 397.8250
4D2042 StdDev 13.5530
4D2042 Average 396.8225
6D4012 StdDev 1.0324
6D4012 Average 395.2600

--ron
 
Top