Charting formula results

**Philb1** · 09-01-2011, 12:39 AM

Hello people
I'm trying to chart a range of cells that are populated by the result of formulas.
Is there a setting or something special about how to go about it?
If I put numbers in a range of empty cells, select them & create a chart, it works. If I do the same with the cells that have formulas in, it returns a blank chart even though the data range is correct.
Any ideas please?
Thanks

**teylyn** · 09-01-2011, 12:52 AM

Hello,

never seen that before. Can you post a sample spreadsheet with the formulas you're using and the resulting chart?

Are the results of the formulas numbers?

**Philb1** · 09-01-2011, 01:34 AM

Hello teylyn

I attached the sheet. The blank chart is from G3:H16 & the chart that works is from P6:R16
I want to be able to create a chart, after selecting the data, via a macro. I haven't got that far yet. It's stopped dead lol. I'll try to figure that bit out when this is sorted.
Thanks

**Andy Pope** · 09-01-2011, 05:33 AM

If you check the actual data being used by the chart you will see it thinks the first columns and rows are the series name.

Name: ='Sheet2 (2)'!$G$16:$H$16
Values: ='Sheet2 (2)'!$I$16
XValues: ='Sheet2 (2)'!$I$3:$I$15

Either manual adjust and add the series via the select data dialog.
Or alter the formula to use NA() instead of "" as "" is treated as text and because the topleft of the range is text excel assumes they are labels.

**Philb1** · 09-01-2011, 07:43 AM

Thanks Andy.. That was a bit of a performance to get a chart. I went the NA() way & conditional formatted the cells, so the text on "cell contains an error" is the same colour as the cell fill colour.
Cheers
Phil