Bar chart Battlefield Chart

[dariushou]

Hi All, i'm sorry to bother you, but i just can't figure this out. I figured such a chart would be somewhat easy, but as usual i think i am wrong...so here it goes:

I’m trying to create a bar chart kind of like a battlefield or chess set. Envision this:

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>Each troop/chess piece can move only horizontal and has a range. For example, Team A moves from left to right and Team B moves from Right to left (like a chess match or a battlefield)

TEAM A:

In cells A2:A4 is the name of each troop in Team A
In Cells B2:B4 is the amount of spaces each Team A troop can move that round

In Cells C2:C4 is the range of each Team A troop (range is range of attack..for example a archer can say move 2 spaces, but then shoot 5 additional spaces—range is the 5 additional spaces)

<o></o>
TEAM B:

In cells A5:A7 is the name of each troop in Team B
In Cells B5:B7 is the amount of spaces each Team B troop can move that round
In Cells C5:C7 is the range of each Team B troop.

<o></o>I would like the chart to have the troop name on the two Y-axis’ with the x-axis going from 0-5200. Team A starts at point zero and Team B starts at point 5200. They move towards each other like a battler or chess match. So the troops listed on each Y-Axis converge on each other—a bar chart where the bars move towards each other. Eventually I would like other stats in the graph, but I think I may be able to figure that out later. Right now I cannot figure out how to get them to converge on each other or even get the two y-axis setup.

Fyi—I would like them to face each other so that the warriors of Team A line up with the warriors of Team B. The movement of the two will never overlap, but their ranges could. I would like the overlap of their ranges colored a different color, but that is only after I figure out how to get the above setup right. EACH TROOP WOULD HAVE TWO BARS (ONE FOR MOVEMENT AND ONE FOR RANGE)—SORRY FOR THE CAPS, BUT I DIDN’T POINT THAT OUT EARLIER.

<o></o>
Please let me know if I haven’t explained myself and I’ll try to make it clearer…I’ve attached some data so you can see what I’m talking about.

<o></o>
Thank you very much
Darius


Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Troop</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Movement</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Range</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Warriors</TD><TD style="TEXT-ALIGN: right">50</TD><TD style="TEXT-ALIGN: right">25</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Archers</TD><TD style="TEXT-ALIGN: right">85</TD><TD style="TEXT-ALIGN: right">50</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Cavarly</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">35</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Warriors</TD><TD style="TEXT-ALIGN: right">5150</TD><TD style="TEXT-ALIGN: right">5125</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Archers</TD><TD style="TEXT-ALIGN: right">5115</TD><TD style="TEXT-ALIGN: right">5065</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Cavarly</TD><TD style="TEXT-ALIGN: right">5100</TD><TD style="TEXT-ALIGN: right">5065</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[energman58]

Sorry can you explain the bit about movement and range do you want the thing to show where he can go and if he got there his range of action in the same bar? Like maybe a dark colour for his possible range of movement and a light one for his range of attack?

Would the idea be to have say warriors edging towards each other until their range of attack overlap?

What happens when this occurs - do you want to show this separately?

Warriors face only warriors, archers only archers etc. - right?

 

[dariushou]

right. so take warriors for example. say warriors move 50 yards and have a range of 100

for warriors i would have two bars for offense and two for defense (1 of them being for movment and the 2nd for range)

and yes warriors only face warriors and archers only archers.

the movement would never overlap. warriors and warriors for example would only come face to face...they wouldn't run past each other.

range however can over lap...i would like overlap of range to be a different color. for example...say the offense range of warriors is blue and red for defense. maybe the overlap would be purple.

hope this helps.

 

[energman58]

Sorry - I am being thick

Lets just look at one team of Warriors

At the start they start at position 0

They could move to position 50 and from that point could strike another 25

What does that look like?

Next time step they are at 50 and could move to 100 and from that point could strike another 25

What does that look like?

 

[energman58]

Here is how you do it:

First you need to construct for each type of player ranges which are in columns:

Blank, Move 1, Range 1, Move 2, Range 2

Also give them a "title range" to the left of the data

Then you develop formulas such that Blank is the full field of play minus the move and range for the team strating from the right (I have assumed for these purposes that the move increments by 50 or whatever each turn so the two teams approach each other - if not you will need two extra "padding" ranges for "where I am now") the other two are self explanatory. Move and range are for team 1 and 2 respectively and should be blank in the row for the other team (so range 2 and Move 2 will be blank in the team 1 row and vice versa. Blank should be zero for the team that starts from the left of the range.

Then you construct two bar charts one for team 1 and one for team 2 using teh same settings - I woudl use teh defaults for everything.

Delete the entries in the title range on the worksheet once you have this right

You may need to fiddle the plot order to get the blocks appearing in the right order (right click on the chart and select the data - this will give you the ability to promote or demote blocks in the plot order until it is right) - so if team 1 starts on teh left we have the order Move 1 Range 1 Blank range 2 Move 2

Delete the legend on both graphs and set the horizontal scale to fixed 0 to 1000 or whatever the field of play is you are using

Select the "blank" block and format it to "no fill" Select all the other block areas in both graphs and set the transparency (under format) to about 50% you can also set the colours at this point so that the same sort of troops have either the same or contrasting colours depending on how you want it to appear.

Select the plot area of one of the graphs (doesnt matter which one but it will be the one that appears on top) to no fill and do the same for the background to the graph - you should be able to see the gridlines of teh worksheet underneath this graph now

Finally drag the transparent graph so it sits exactly on the top of the other graph (so there is only one set of grid lines visible) and the job is done!

If you want to manipulate the graphs teh easiest way seems to be to grab the top graph and move it slightly away from the underneath graph and then select both - then right click and select group - group. Then select them again and right click and select group-ungroup this time, move the two graphs over each other again and select group-regroup this should allow you to manipulate the graph as you like (stretch it, move it about etc.) as if it was one graph although it is a bit hit and miss on my version.

You will need to add legends and so forth using text boxes

HTH - overlaying the graphs is the only way I could think of making it work but by making the columns semi transparent you get the overlaps illustrated nicely.

 

[dariushou]

not sure if that will work. the team b would need to move from right to left. plus i'm going to have stats at the bottom of the graph so i think i will need to have just one graph instead of overlaying a transparent one on top of the other graph.

There has to be a way to do this on one graph. It's beyond my means, but it would seem this has been done....think of a chess set...graphing the moves (that is if they only moved in horizonal lines). Each chess piece approaches each other. Forget about the range concept that I was talking about for a second and just think about movement. Left to right and Right to left. How would you graph that?

Thanks Again,
Darius

 

[dariushou]

Anyone have any ideas on how to do this? I've been trying for almost a week and just pulled my last hair out trying.

 

[energman58]

Team B DOES move from R to L if you implement my solution. If you only want one dimension illustrated then it is a lot simpler - you can do a simple stacked bar with 3 values - Team A, the gap and Team B where gap=full range minus team a's distance minus team b's distance.

So if the field is 100 and the two teams have each moved 30 then gap is 40 and format the gap range so it is invisible. A will then appear to move from the left and B from the right side of the field. The problem is getting the two teams opposite each other - with these bar charts you cannot use a second axis

 

[dariushou]

Let's use one troop say archers and range. range can over lap.

Say both Team's archers have a range of 80 (on a scale to 100).

Team A would have range from 0 to 80
Team B would have range from 100 to 20

The y-axis on the left would be for Team A
The y-axis on the right would be for Team B

The text "Archers" would be on each axis directly across from the other.

Because they overlap, i would need the bar of one (lighter color) to be thicker than the bar of the other. So i would have the bar for the Archers for Team A be yellow and for Team B Archers the bar would be black. That way i could easily see the overlap of both (in the above example i said both would have a range of 80 but in reality Team A archers may only have a range of 50 and Team B would have say 70 so you can't just have the overlap a different color as you would need to distingush the over from each team...the thicker bar makes this possible).

Also, there are 100 rounds, so that's 100 graphs. If at all possible i would like to accomplish this with one chart per round instead of two. crossing my fingers that this is possible.

I should also note that each round is cumulative so the bar would not break from it's own axis (meaning if the archers can move 50 per round then the first round would show a bar going from 0-50 and then in round two it would show 0-100).

Does this make sense? Is this possible?

 

[dariushou]

I'm still trying to get his in one chart. Anyone know how? This has me stumped and it's killing me. Can't stop thinking abou it