Results 1 to 11 of 11
Thread: making a ramp chart (excel 2003)

20060203, 23:16 #1
 Join Date
 Oct 2005
 Location
 Calgary, Alberta
 Posts
 205
 Thanks
 0
 Thanked 1 Time in 1 Post
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

20060204, 02:38 #2
 Join Date
 Oct 2005
 Location
 Calgary, Alberta
 Posts
 205
 Thanks
 0
 Thanked 1 Time in 1 Post
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?

20060204, 11:24 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20060204, 16:58 #4
 Join Date
 Oct 2005
 Location
 Calgary, Alberta
 Posts
 205
 Thanks
 0
 Thanked 1 Time in 1 Post
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
thanks for your reply

20060204, 18:14 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: making a ramp chart (excel 2003)
You mean like this?
Steve

20060204, 21:14 #6
 Join Date
 Oct 2005
 Location
 Calgary, Alberta
 Posts
 205
 Thanks
 0
 Thanked 1 Time in 1 Post
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

20060204, 22:03 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20060204, 22:18 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 "nonzero" value.
Here is an alternate technique?
In J1 enter the array (confirm with ctrlshiftenter):
<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,J11,1)</pre>
K6: <pre>=INDEX($D$4:$H$20,J1,1)</pre>
M4:<pre>=INDEX($D$1:$H$20,J11,5)/(INDEX($D$1:$H$20,J11,1)K3)</pre>
M5: <pre>=INDEX($D$1:$H$20,J1,3)</pre>
Steve

20060205, 14:47 #9
 Join Date
 Oct 2005
 Location
 Calgary, Alberta
 Posts
 205
 Thanks
 0
 Thanked 1 Time in 1 Post
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?
thanks steve for your help

20060205, 19:56 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 ctrlshiftenter):
=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,J11,1)
K6: =INDEX($D$4:$H$20,J1,1)
M4:=INDEX($D$4$H$20,J11,5)/(INDEX($D$4:$H$20,J11,1)K3)
M5: =INDEX($D$4:$H$20,J1,3)
Steve

20060205, 22:09 #11
 Join Date
 Oct 2005
 Location
 Calgary, Alberta
 Posts
 205
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: making a ramp chart (excel 2003)
Thanks Works great
Markus