Formulas in charts?

[jen_FPS]

Hello, I'm creating a workbook that has all of the current sales guys' total sales & profit for 2007 vs 2006. Each guy has their own chart with the dollar totals, and I'm trying to make a chart that has each guys' growth over last year.

I have the formula as a simple "=P34-B34" where as P34 is 2007 profit %, and B34 is 2006 profit%., to find the difference between the 2 years. Since August - December 2007 hasn't happened yet, it is taking 0 (blank cell) and subtracting 31, to get a -31% change over last year, only because the cell is blank. How can I chart them without the values being negative?

Is there any way to make this work?

 

[MarkVMcCullagh]

I would use Dynamic charting involving the offset function. The best explanation of this which I've found is at http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html