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.

Try using this:

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

It looks like you're doing this because of operator precedence:

         (SUMIF(O2:O9470,"wave",M2:M9470))

(M9472 - --------------------------------- - (SUMIF(O2:O9470,"wave",N2:N9470)))*8 

                N9472

Try using this:

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


Y E S !!

I'm a little embarassed at how long it took me to see it even when I had your solution for comparison..

It's amazing that moving one parenthesis can change the answer by a factor of more than six billion!

And, looking at it now, we can actually get rid of the first open and final closing parentheses as well:

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

I banged my head against my original (incorrect) formula for about three hours before finally coming here and asking for help. I'm impressed!

edit:

I plugged my actual numbers into your "what's actually going on" formula:

(I tried to paste that in here, but it didn't format correctly... how did you manage to write that post with the leading spaces in the lines?)

and the number came out to within 2 KB/Sec of the ridiculous number I was getting (the 523x10^9 number). I bet that really is a rounding error. I mean, a difference of one part in 260 billion is pretty negligible...

tanstaafl.

how did you manage to write that post with the leading spaces in the lines?

If you surround the stuff you want to preserve the formatting of (ie leading and/or multiple spaces on lines, typically indented code), you can use the [ code] or [ pre] tags. It also uses another (fixed width?) font.

/Michael

you can use the [ code] or [ pre] tags.

          Oh. 

                   Sort 

                         of 

                               like 

                                      this. 

tanstaafl.