This project has moved. For the latest updates, please go here.

Inserting a formula in a TotalsRow

May 11, 2015 at 8:56 AM
Hi,

I have a simple Excel sales report, with 4 columns: customer, turnover, margin, and margin percentage.
I have added a TotalsRow (awesome functionality by the way), with sums of the turnover and margin columns, and the average of the margin percentage. Sounds straightforward, but this is not correct, because obviously the margin percentages are not weighted by the turnover and margin in the total average.

For example:
Customer Turnover Margin Margin %
Customer A 10,000 5,000 50 %
Customer B 100,000 25,000 25 %
Total: 110,000 30,000 37.5 %

The total margin % is actually 27.3 % (30K / 110K), not 37.5 % (75 / 2)

So in my code, I add the totalsRow, and then I tried to simply replace the content of the total margin percentage cell with a formula (Total margin / total turnover).

The problem is, this always results in Excel claiming that there is unreadable content, after which it empties the cell, and also the cell to the left. So I end up with:
Customer Turnover Margin Margin %
Customer A 10,000 5,000 50 %
Customer B 100,000 25,000 25 %
Total: 110,000

Can I, or how should I write formulas like this to cells in the TotalsRow ?

Regards,
Johan