List inserts auto formula with wrong range

**ahartman** · 06-13-2012, 05:35 PM

Hi all,

I'm trying to build a simple template for pareto charts in Excel 2003. I have a list with dummy data: Item, Value, Value %, and Cumulative %, in columns B, C, D, and E, respectively.

For my Cumulative % column, each cell has the following formula (example from row 8):

=SUM(C$2:C8)/SUM(C:C)

This works beautifully when I manually enter the formula and adjust the range to include the row it's entered on. However, when I use the list's feature to automatically format the next inserted row like the previous row, the formula range "skips ahead" one row and is then broken for the new line of data.

Put another way: If I start typing at the bottom of my list in row 9, the automatically generated formula in column E has the (incorrect) range (C$2:C10) when the one above it has the (correct) range (C$2:C8).

What's going on here?

I've attached my workbook for anyone who's willing to take a look and tell me what I'm doing wrong.

Thanks in advance to anyone who can help!

-Adam Hartman
Mechanical Engineer
Flextronics Medical

**ahartman** · 06-14-2012, 11:50 AM

Sorry to reply to my own thread, but I found a work-around for this strange list behavior and my template is working now.

Basically, I trick Excel by re-writing my formula with an offset function combined with a row function. The result looks like this:

=SUM(OFFSET(C$2:C$2,0,0,1+ROW()-2,1))/SUM(C:C)

It's a hack, I know, but it works. I've attached my updated template if some future person has a similar question and wants an answer.

**ahartman** · 06-19-2012, 10:07 AM

Acrobat, yes, the Pareto in my example file will update as long as you add data in the list on the "Data" tab. Feel free to download my file and play with it.

**acrobat153** · 06-18-2012, 09:08 PM

Hello, I am a new perticipant and my English is not excellent. This site is very interesting and important for me. I want to know is it possible to make autoupdate of Pareto diagram when I add new datas?