Calculated Columns in TABLES

Here is what happened today in class...(I thought I saw this happen in the 11am, and confirmed it in the 1230pm class).

When we created our TABLE and inserted the calculated column for Total/Revenue (price*quantity*(1-discount)+freight), as long as we left it in the TABLE format, we were fine.

Once we converted to a range, our formula still "seemed" okay

=CustSales!$H2*CustSales!$I2*(1-CustSales!$J2)+CustSales!$K2

This was the formula I copied from row 2...the columns are absolute referenced, which is fine, and the rows vary, again fine. The CustSales! refers to the worksheet.  Again, that is fine.

Normally, when you apply a sort, as long as all of the data is selected, all rows (called a record) should stay intact, and the rows should rearrange as specified.  However, for some reason, all the data is sorting correctly, EXCEPT the calculated column we added.  It is staying in the same location, and referencing the "new location" of the inputs.  For example, if I sort and row 2 has shifted to row 16, the formula in L2 is

=CustSales!$H16*CustSales!$I16*(1-CustSales!$J16)+CustSales!$K16

This results in having totals in the final row that don't correspond to the record it is "attached to".

I can figure out why that is happening.  To be honest, I have never used calculated fields before, but I saw in on the video and thought it was very cool.  BUT, to be safe, do NOT use calculated fields in either TABLES or PIVOTTABLES.  Calculate your column first, then add the TABLE functionality.  That will keep you in good shape. 

Sorry for the confusion, but just one more lesson on how you should NEVER trust Excel too much!!