Return average from a range of cells, Excel 2003 1

[JohnB98121]

My spreadsheet has two columns with approx. 4,500 rows.

First column shows a filename beginning with a string to indicate firstly the date (in dd/mm/yyyy format) and then other details e.g. 26102016_abc.wav, 26102016_xyz.wav
There are about 250 rows for each date i.e. the sheet covers approx. 18 days data.

Second column shows a value, and there could be 250 different values for each date.

I am looking for a quick easy way to display the average value from the second column for each day , so the result displays 18 dates, each with their average value.

Thanks for your help.

JohnB

 

[SkipVought]

Hi,

Welcome to Tek-Tips.

Would be helpful if you posted 10 or 15 rows of data with headings.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Okay, so I created a test table...
[pre]
File Num
26102016_abc.wav 2
26102016_xyz.wav 3
27102016_xyz.wav 3
27102016_xyz.wav 4
27102016_xyz.wav 5
[/pre]

Used Named Ranges based on the Names in TOP ROW:

Then There's the aggregation Table that I set up starting in E1
[pre]
DTE AVG
26102016 2.5
27102016 4
[/pre]
...where the formula in F2 is...
[tt]
F2: =SUMPRODUCT((LEFT(File,8)=E2)*(Num))/SUMPRODUCT(--(LEFT(File,8)=E2))
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JohnB98121]

Thanks Skip,

I'm not sure of your suggestion, but here's a sample of the data.

JohnB

 

[JohnB98121]

Sorry, I see now that wont work.
I'll prepare another sample with an extra column - in a minute!

 

[JohnB98121]

OK, I need to include a third column.
Each day has a series of 15 minute files (to cover 4 hours) which have been given a value RMS for every 60 seconds with count of the seconds appearing in the new middle column.
I need to be able to select the days with the lowest average RMS value.
Hope this makes better sense - sorry for confusion

John B

 

[SkipVought]

To Create Names in TOP row in Excel 2003, 1) SELECT your entire table, 2) Insert → Name → Create to open the Create Names dialog box, 3) check names in TOP row.

The data did not match your examples in your original post. The "date" portion of your Filename is only 6 characters.

I COPIED the Filenames to column E and used the Text to columns wizard to parse on the UNDERSCORE character, importing the first column as TEXT and NO NO IMPORT the secnd column. This results in a long list of your 6 character "date". Now use the Advanced Filter to generate a UNIQUE LIST of "dates."

The formula in F2
[tt]
F2: =SUMPRODUCT((LEFT(Filename,6)=E2)*(RMS))/SUMPRODUCT(--(LEFT(Filename,6)=E2))
[/tt]

My result...
[pre]
DTE PCT
081115 0.005907357
091115 0.020265444
[/pre]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

And here's your file back with the results.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Oh, yet another change! Are we done before I address the last version?

AND you have some corrupted data dow around row 92 like...
[pre]
071115_203002.wav 9840 0.359151
071115_204502.wav 10800 [highlight #FCE94F]0 0.346985[/highlight][highlight #EF2929][highlight #EF2929][/highlight][/highlight]
071115_204502.wav 10860 [highlight #EF2929]0 0.184905[/highlight][highlight #EF2929][/highlight]
071115_204502.wav 10920 [highlight #EF2929]0 0.160145[/highlight]
[/pre]

BTW, the formula is the same solution as I posted previously.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

I corrected your data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JohnB98121]

I'm very sorry for my errors and inexperience, skip.

I hope the second much larger sample attachment will give you a better idea of my problem.

I do appreciate your help

JohnB

 

[SkipVought]

See my previous post.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JohnB98121]

Thanks Skip.
That corrupt data has sent me into a spin!
I'll try and solve that problem before I try your formula, as there are several folders to be analyzed and I need to be sure there's no more corruption before going further.
.

 

[SkipVought]

Simply SELECT [highlight #FCE94F]0 0[/highlight].12345 and COPY from one cell.

Open Find and Replace

PASTE into the Find box and enter 0 in the Replace box.

Then hit Replace All.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JohnB98121]

Thanks Skip,
That would fix the corrupt data in one set of data, but I wanted to find the cause of error and avoid it happening in all further operations.

I think it was due to an error in my first Text to Columns action, and hopefully I've solved that problem.

As you can see, I'm not very clever with Excel!!

Could we try again please with this new sample, and a more simple explanation of how you entered column E.

Thanks in advance

JohnB

 

[SkipVought]

COPY column A and PASTE in column E

SELECT column E

Text to Columns DELIMITED using UNDERSCORE > Column 1-Import TEXT, Column 2-DO NOT IMPORT -- this results in the 6 character "date" as TEXT values.

Then use the Advanced Filter to generate a UNIQUE list of "date" values. You'll need to replace the full list with the unique list in column E.

I'm done for the night, as its 1:39 here in North Texas.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JohnB98121]

Got there !!!

Many Thanks for your patience and help.

Cheers
JohnB

 

[Andrzejek]

Welcome to TT, JohnB98121

it is customary to 'award' a Star for helpful posts. Not only it says: "Thank you" to the person who helped, it also indicates to others which post was helpful so others can benefit as well. Just click on "[blue]Great Post![/blue]" link in the helpful post.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[James Wallace]

Very good info, many thanks.

 

[SkipVought]

@James, welcome to Tek-Tips.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]