Right up front, let me say that I don't need the answer this formula is supposed to produce (I already know the answer) -- I am just looking for an explanation of why the formula doesn't work the way I think it should.

I took the CSV file for my empeg, put it into an Excel spreadsheet, stripped out the columns that didn't interest me, and was left with 14 columns, A through O.

The columns in question are column "M", which lists the filesize of each track in bytes; "N" which lists the duration of each track in milliseconds; and "O" which tells whether the track is encoded as WMA, MP3, or WAVE. The other columns for the purpose of this discussion don't enter into it.

The body of the spreadsheet is 9470 rows long, including row 1 which is the header row. All the playlists have been deleted -- only the 9469 "tune" tracks are left.

Row 9472 contains the sum totals for some of the columns, including the totals for Bytes and Milliseconds.

What I am trying to do is write a formula that will show the average Kilobits per Second for the entire collection, excluding WAVE files from the computation. Simple enough -- take the total number of BYTES and divide by the total number of MILLISECONDS (after subtracting out the WAVE files), and multiply that times 8 to go from Bytes to Bits. The only tricky part is that I have to do a conditional sum to subtract out the tracks that are listed in column "O" as being WAVE files.

So here's my formula:

=(M9472-(SUMIF(O2:O9470,"wave",M2:M9470))/N9472-(SUMIF(O2:O9470,"wave",N2:N9470)))*8

where M9472 is the total number of Bytes M2:M9470 (including the WAVE files);
where N9472 is the total number of Milliseconds N2:N9470 (including WAVE files);
where column "O" is the Codec information (wma, mp3, or wave)

The *8 at the end is to convert from Bytes to Bits

By manually subtracting the WAVE files from my Bytes and Milliseconds totals and then doing the division I have determined my overall average bitrate (which includes a lot of low bitrate audiobooks) is 81.90 KB/sec.

My super-duper formula gives a slightly different result: 523,139,822,134 KB/sec. This is great for sound quality, but I feel that accuracy should not be off by a factor of 6.39x10^9. Do you suppose this could just be rounding error?

As I said before, I'm not searching for an elegant way to get the answer (I already know the answer). I am just looking for an education on what I did wrong and why it doesn't work right.

tanstaafl.