# Graphing Question - Area Graph with 2 variables

#### sooroo

##### New Member
Hello,

I am trying to create an Area graph in Excel 2000.

Here is my data series:

Average Turnaround Time for Phase 1, 2, 3: 35, 65, 70
Total Projects in Phase 1, 2, 3: 19, 32, 17

I want Turnaround time to be on the X axis and Total Projects to be on the Y axis. Then I want the 3 Phases to flow into each other, i.e. the first phase is 35 units wide, the 2nd phase picks up where the last one dropped off and is 65 units wide and so on.

I want to be able to tell from the width which phases take longer and from the height, which phase is the most populous. I've been searching for a while, and I can't find a good way to do this. Ideally it would be an area graph, but columns would work too if their widths could be manipulated.

Thanks so much for the sounding board.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### sooroo

##### New Member
Thanks for pointing me to this. I would never have found this myself.

This will really help me to understand how to structure everything.

This board is the best!

#### tusharm

##### MrExcel MVP
You are welcome. Glad to be of help.

I hope to have a charting utilities available soon. One of the things it will do is take a dataset and reorganize it so that it can be used in a side-by-side stacked chart. You will be able to specify how many columns go in each stack.

#### sooroo

##### New Member

That sounds really interesting... I will definitely be interested to see that!

How can I make sure to hear about it when it comes out?

#### sooroo

##### New Member
I have been trying to figure out the formulas in this example, and I am having real problems with the OFFSET function.

The general description says that you will get an error message if you don't refer to a cell or cell range as the first argument. I am referring to a cell, but it's still giving me this error message. I tried doing my own formula, and when that wasn't working, I tried to replicate the formula in the example, and I'm still getting a #VALUE! error. I am using Excel 2000 on an XP machine. Is there a limitation in the version I'm using? Or is it a user error that I'm not seeing?

=OFFSET(Sheet1!\$B\$5,0,0,COUNTA(Sheet1!\$B:\$B)-1,1)

Thanks so much!

#### tusharm

##### MrExcel MVP

On each page of my web site is a third party service that notifies you whenever the page changes. Consider using that.

That sounds really interesting... I will definitely be interested to see that!

How can I make sure to hear about it when it comes out?

#### tusharm

##### MrExcel MVP
No, OFFSET has been supported going back...well, forever.

Do you have data in column B? At least one value in B5 with a header in B4?

I have been trying to figure out the formulas in this example, and I am having real problems with the OFFSET function.

The general description says that you will get an error message if you don't refer to a cell or cell range as the first argument. I am referring to a cell, but it's still giving me this error message. I tried doing my own formula, and when that wasn't working, I tried to replicate the formula in the example, and I'm still getting a #VALUE! error. I am using Excel 2000 on an XP machine. Is there a limitation in the version I'm using? Or is it a user error that I'm not seeing?

=OFFSET(Sheet1!\$B\$5,0,0,COUNTA(Sheet1!\$B:\$B)-1,1)

Thanks so much!

#### sooroo

##### New Member
Right, that's exactly what I have. B5 is the number 10, and B4 is the header for this column.
A B
4 PLANT VOLUME
5 A 10

It says that the hard work in this graph is done by using defined names. Do I have to name the cells in order for it to work?

#### tusharm

##### MrExcel MVP
The defined name refers to the OFFSET formula. See
Named Formulas
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/named_formulas.html

and
Names in Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/names_in_charts.html

Right, that's exactly what I have. B5 is the number 10, and B4 is the header for this column.
A B
4 PLANT VOLUME
5 A 10

It says that the hard work in this graph is done by using defined names. Do I have to name the cells in order for it to work?

Replies
12
Views
215
Replies
3
Views
185
Replies
3
Views
81
Replies
5
Views
108
Replies
7
Views
82