• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Bar chart of overlapping values, -> producing cumulative values

N

Niki

New Member
What I need to do is a bit hard to describe so I'm going to explain it as best as possible...


If i explain the project I'm working with this will probably be a bit easier to digest. I'm working on a fluid mechanics end-of-year design project and what i have is a system of aerial sprinklers that are dropping a volume of water down on a certain distance (from position x1 to x2, say) and watering the ground to a certain depth. I have data for the positions of the coverage and the depth of my soak into the ground.


My problem is this: the sprinklers are dropping water over overlapping distances.


What I need: is some sort of chart/graph that will actually ADD up the depths at the union of the distances they cover.


for example: (I tried to format this a bit more legible but no luck, sorry)


depth_soak upper_x lower_x

0.239532077 23.19230485 2.407695155

0.159688052 29.59230485 8.807695155

0.119766039 35.99230485 15.20769515


this is just a very small subset of the data, but you can see from the data what i need...

the first data row (for my first sprinkler) puts out water to soak into the ground a total depth of 0.23" across x values 2.4 thru 23.19

BUT the second sprinkler OVERLAPS a portion of this section also, penetrating 0.15" across x values 8.8 to 29.59


so, you can see that distances 8.8 thru 23.19 have overlap for the first set of sprinklers and cumulatively these create total ground penetration of 0.38" across that distance... i need some graph to automatically see these overlapping areas and then add them accordingly. I need the total ground penetration depth contributed by all the sprinklers for each section along x. (my goal here is to have somewhat even ground soak across my entire area - which i will be able to see if I can somehow get this to work...)


I've been poking around with excel for the past few hours and nada. I don't even know if excel is capable of this or not... hopefully I explained it well enough.


any help would be greatly appreciated. and thank you in advance for even reading. If someone can give me a solution they will be my new personal hero :)
 
Niki


Welcome to the Chandoo.org Forums


I assume you want to do a section through the area and not a 3D of the system


Setup 1 series to calculate the water of soakage and a second series for the second sprinkler

Don't worry that there will be common X values in both sets of data


then add the series to a chart and display it as a Stacked area chart
 
Hi ,


See if the following worksheet helps.


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21112


Narayan
 
Thank you both for the incredibly timely responses! what a wonderful community you all have here.


And I must say Narayank, you sir are a freakin' genius. I used your format and applied it across all my data for the sprinklers (there were 60 data sets, so it was a chore) and it worked brilliantly!


Many many thanks to you, b/c I would have never figured this out on my own. I am so appreciative that you took the time out to set up that sample sheet for me. You are completely my new favorite person, hands down :) couldn't have asked for better help.
 
Another quick question that will have an easy answer...


if I'm using the solver tool in excel, how can I get generated values between 2 numbers?


for example: I've set excel to generate the flow rates at each nozzle (60 values) using the solver tool but I need the values that are generated to be between 0.026 and 7. other than setting 60 constraints, is there a more simple way?


I tried to use Data Validation and set the cell values between these ranges but that doesn't keep the solver tool in line... any quick suggestions before i add all the constrains manually myself?
 
Hi ,


Thanks for your appreciation.


Regarding your question , I am not sure I can understand it.


You wish to have only those solutions which lie between the two limits 0.026 and 0.7 ; are you saying that you wish to run the Solver for 60 different solutions , which means you have to specify the constraints 60 times ?


Specifying the two limits is just one constraint , isn't it ?


Narayan
 
sorry it wasn't clear.


I think I got it though. I just selected the whole column of data that was being generated and applied the constraint to that.

i.e. selecting B2:B62 >= 0.026 and then <= 7.


I did that before but it didn't seem as though it was working, but I think my solver settings were too restrictive. Went in the options and changed the precision option in solver to a bit higher value (allowing a bit more error) and the data seems to come out a bit better in meeting those 0.026 to 7 values I needed. I think solver just had a hard time meeting all my constraints when it had to come up with so many values. I am going to fine tune the values by hand so its acceptable for the values to be a bit erroneous, and the graph you helped me generate is an excellent tool for that.


Thank you again. If I do well on my project I owe a large portion on my grade to you! :)
 
You must log in or register to reply here.
Back
Top