# Thread: making a ramp chart (excel 2003)

1. ## making a ramp chart (excel 2003)

I have an Idea of making a chart which seems to be above my excel skills so I come to look for help.

please refer to my example worksheet,

I have listed at the top 6 columns
one for the stage description 1 to 10,
total fluid
stage fluid amount
stage amount
stage weight
cum Weight
concentration

the concentration is the green stairs graph line
the Red is dashed line is the fluid slopes which change at the blue dot. which is the inflection point or concentration average.

the yellow part is not needed i drew it to show that x and x1 are the same fluid amounts

below the graph pic is some math to make the point work

I have done the math to figure out how to make this work but cant do it on excel can someone lend a hand to build a graph which can automate my picture.

Thanks

2. ## Re: making a ramp chart (excel 2003)

i kinda got to a point where i want to be but would love to get some suggestions?

3. ## Re: making a ramp chart (excel 2003)

Change E23 to:
=E22

Why do you want this to be zero? You want it to plot at the same value as the one above. If nothing else, you can format it to hide it (format -cells Custom (no quotes): ";;;")

Then select the dotted series and enter in the formula bar:
<pre>=SERIES(,'sheet 1'!\$E\$19:\$E\$23,'sheet 1'!\$I\$19:\$I\$23,2)</pre>

In an unused portion ( I started in P21, move as desired after setting up)
P21: 1
P22:1.5

P23:
<pre>=P21+1</pre>

Copy P23 to P24 to P50

In Q21:
<pre>=INDEX(\$J\$30:\$J\$45,P22)</pre>

In R21:
<pre>=INDEX(\$L\$30:\$L\$45,P22+0.5)</pre>

Copy Q21:R21 to Q22:R50

Select the green "steps" and in the formula bar enter:
<pre>=SERIES(,'sheet 1'!\$Q\$22:\$Q\$51,'sheet 1'!\$R\$22:\$R\$51,1)</pre>

Steve

4. ## Re: making a ramp chart (excel 2003)

HI Steve,

I have attached an example of what i am trying to do with this workbook,

e23 is a seperate value to the graph just basically there for information

I am trying to get the graph to look like the pic to the right of my graph, I am missing the green line that starts the at 5.4 or the prior value making the conc come up from 0 to 600 kg/m3???

The fluid line wont drop of to zero either like in the picture.

I like what you did in columns p , Q , R seems to simplify it alot more then what i had.

I quess i am asking for the most simply way to do a graph like this, I am learning from the web and seem to be getting the most sloppest way out of it

5. ## Re: making a ramp chart (excel 2003)

You mean like this?

Steve

6. ## Re: making a ramp chart (excel 2003)

yes that is perfect,

I am sorry but i played with it converting the units to Imperial US and I cant figure out why that change would be so different?
I am not sure about the 9.99E+307 in the match cell e31:e32?

thank you steve

7. ## Re: making a ramp chart (excel 2003)

9.99E+307 is just a "big number".

Using it in match will find the last number in a list of numbers. It was a similar approach to what you were doing with "0" only "0" does not works since the early values were also "0"

Steve

8. ## Re: making a ramp chart (excel 2003)

Why have you entered zeroes in G14:G20? Aren't they left blank (I thought these were entered not calculated)?

If you are going to fill in the values with zeroes, you will have to use a different technique to get the last "non-zero" value.

Here is an alternate technique?

In J1 enter the array (confirm with ctrl-shift-enter):
<pre>=MAX(IF(\$F\$4:\$F\$20<>0,ROW(\$F\$4:\$F\$20)))</pre>

Replace the following formulas:
K5: <pre>=INDEX(\$D\$1:\$H\$20,J1-1,1)</pre>

K6: <pre>=INDEX(\$D\$4:\$H\$20,J1,1)</pre>

M4:<pre>=INDEX(\$D\$1:\$H\$20,J1-1,5)/(INDEX(\$D\$1:\$H\$20,J1-1,1)-K3)</pre>

M5: <pre>=INDEX(\$D\$1:\$H\$20,J1,3)</pre>

Steve

9. ## Re: making a ramp chart (excel 2003)

with the J1 formula i have noticed that i cant move the tables around because it offsets it, is there a way to make everything movable?

10. ## Re: making a ramp chart (excel 2003)

J1 always gives a "row number" . All the ranges you refer to must start with index using this, must refer to row 1 as the start of the range.

If you want to have the INDEX refer to the range itself, then adjust J1 to subract the row and make it an index.

In J1 enter the array (confirm with ctrl-shift-enter):
=MAX(IF(\$F\$4:\$F\$20<>0,ROW(\$F\$4:\$F\$20)))-ROW(\$F\$4)+1

Replace the following formulas:
K5:=INDEX(\$D\$4:\$H\$20,J1-1,1)

K6: =INDEX(\$D\$4:\$H\$20,J1,1)

M4:=INDEX(\$D\$4\$H\$20,J1-1,5)/(INDEX(\$D\$4:\$H\$20,J1-1,1)-K3)

M5: =INDEX(\$D\$4:\$H\$20,J1,3)

Steve

11. ## Re: making a ramp chart (excel 2003)

Thanks Works great

Markus

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•