Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    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

  2. #2
    2 Star Lounger
    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?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

  4. #4
    2 Star Lounger
    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    2 Star Lounger
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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 "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. #9
    2 Star Lounger
    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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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 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. #11
    2 Star Lounger
    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

Posting Permissions

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