Chart help: Stacked Column with multiple groups

[switzd0d]

I have ZERO experience with charts. For work the other day it took me an hour just to make a simple chart with two values. But I am wanting to learn and even though this is not work related, I believe a project I am working on soon could benefit from this technique being learned.


Can someone explain to me how to get my chart to do what is in this image (I drew the bars in to show you what I am going for). Do not worry about the chart that is below the large one that says "Syracuse, Averni" etc. I am pretty sure that cannot be done in excel unless it is a new line for each faction.

The chart is for a computer game called Rome 2 Total War. If you have never played it before, think of it like RISK or a empire game where you try to conquer the world with armies you raise. I want the chart to show the amount of casualties sustained by my faction (Rome) and all other factions COMBINED for every year in the game. This data comes from the "Battle Synops" sheet in which I hard code (fill out) those values from the notes I take while playing the game.

What I need help with is how I would need to get this information to go into the large chart like my example photo (The RED Bar is my factions deaths that year "Rome"). I'd rather learn this instead of someone simply doing it because I plan to make another chart depicting casualties by location and theater of war (not shown), however, if you would rather just alter the excel file and do it yourself, please provide any extra information on how you did it if there are places you put information in aside from what I would see by selecting "Select Data" on the chart. Rome 2 Campaign Logger.xlsx

How I want the Chart to look like

rome_excel_example.jpg

How the data appears when added

data_example.jpg

[teylyn]

Hello,

Exact steps will differ depending on your Excel version, so please provide that and ask when something is not clear.

Starting with the sheet "Deadliest Year Formulas". You need to create a table with the data arranged in a specific way. Here are the steps.

- insert a row above the data. Enter these labels into cells A1 to F1: Year, Rome, Averni, Delmatae, Liguria, Syracuse
- in cell B2 (that should be the cell below the label "Rome") enter this formula and copy down

=SUMIF('Battle Synops'!F:F,'Deadliest Year Formulas'!A2,'Battle Synops'!G:G)

This gives you the total losses for Rome for each year.

- In cell C2, below the label "Averni", put this formula. Note that the placement of the $ signs is important!

=SUMIFS('Battle Synops'!$H:$H,'Battle Synops'!$A:$A,'Deadliest Year Formulas'!C$1,'Battle Synops'!$F:$F,'Deadliest Year Formulas'!$A2)

- Copy the formula across to F2 and then copy down. Now you can see the total number of enemy casualties summed up by army and year. This is the source data for the first chart.

- select the data from cell A1 to F45, then click Insert > Chart > Column chart > Stacked column

Rome will be the lowest column in the stack. Select a column and open the Format dialog. Reduce the Gap Width, so the columns are closer together.

For the totals, create a simple table like this:

Row\Col	I	J
1		Total casualties
2	Rome	12908
3	Enemies	57070

The formula for Rome is

=SUM(B:B)

The formula for Enemies is

=SUM(C:F)

Select that data and insert a column chart.

I have attached a file where I performed the steps described above. Let me know how you get on.

cheers, teylyn

[teylyn]

The horizontal chart is a bit more involved. If you are just starting to work with Excel charts, you may have many questions about the following steps. Again, exact "click here" instructions will vary with your Excel version, so please pipe up if something is not clear.

The horizontal bars can be created with a -- yes -- horizontal bar chart. You need to prepare the data in a separate table. On the War Phases sheet, I have created this table:

Row\Col	A	B	C
9		assume that 270 BC = 0
10		gap before war	duration in years
11	Syracuse	6	2
12	Liguria	7	6
13	Delmentae	8	3
14	Averni	8	9

To plot the start and duration of each conflict, we first need a number for the gap from the start of horizontal axis to the beginning of the conflict, and then a number for the duration of the conflict. In this scenario, I have arbitrarily chosen 270 BC as the starting point of the chart, so I can create vertical lines to indicate every 5th year on the time line.

Enter the number for the gap and the number for the duration of the conflict in the table. If you want to play this to the max, you can use formulas, but to keep things simple, let's stick to manually entered numbers for now. So, with 270 BC as the base line, a conflict that starts in 264 BC needs a gap of 6 years before the bar appears in the chart. The conflict lasts for 2 years.

- Select the data from A10 to C14 and insert a stacked horizontal bar chart.
- Format the horizontal axis to have a minimum of 0 and a maximum of 40 (think of these as years)
- set major units to 5 (years), minor units to 1 (year)
- format the the axis to have no line, no labels and no tick marks.
- turn on major and minor gridlines
- select the legend and delete it
- select the gap series and format it to have no fill and no line
- format the vertical axis and set it to plot "Categories in reverse order". Now Syracuse is on top.
- set the axis labels to "none"
- select the visible data series and add data labels. Format them to show the Category Name and set the position to Inside Base
- reduce the gap width
- select each individual bar and change its color as desired
- move the chart to the same sheet as the other charts and drag it below the casualties chart
- re-size the chart width to match the casualties chart
- re-size the plot area width so that the major grid lines match up with the casualties chart. To select the plot area, click between two vertical lines where there is not horizontal bar. Then drag the sizing handles to position the plot area. The first vertical line is 270 BC and the last major grid line is 230 BC. Align accordingly.

Refer to the attached file for reference.

Let me know how you get on.

cheers, teylyn

[switzd0d]

Hi teylyn,

I hope you saw I gave a +Rep the other day. Sorry for not responding earlier. Your directions helped me a lot and I was able to add on more charts and graphs showing the progress of my campaign in game and it looks great.

I am hung up on one last part though. It is more a formula question, but decided to post here instead because you are familiar with this thread and I am sure since you are very good with graphs that you have come across this multiple times.


I will provide the excel sheet in a few hours (it is too large to use as an attachment so when I get home I'll upload it and link it). But what I am running into problems with is instances duplicates are used with the RANK chart to make "Top 10" and "Top 5" graphs/charts. As you can see in this image I have been busy making a dashboard with the information I learned from your directions. In the image, take a look at the values in

- "TOP 5 LONGEST WARS" Chart - It shows Syracuse twice. However, I only had one war so far with them. It should list a different faction in which I had a 2 year war with.

- "TOP 5 LARGEST BATTLES" Chart - It shoes #NA for one of the years the battle was fought. The year was 242 BC. I am guessing this is another RANK formula issue because the Battle of Lemonum was also in 242 BC

- I believe it will only be these two charts that I would need to correct the RANK command with. The other charts will more than likely never have a repeating value. 1 in a 10000 chance.



I learned that to change RANK from causing repeats you use the countif function in conjunction. But the problem is in all of the examples I found, they did not cover multiple fields in which the data is received. All they where doing was ranking data from one column. But in these charts, they take data from 2 columns instead of just 1.



I know there is not much you can do until I send the spreadsheet and point you to where the formula is and where the data is coming from. Just giving you a heads up and also to get a reply on this thread ASAP because I did not want you thinking you helped me out for nothing. I went from chart newbie to .... well a lot better!!!! Even translated what I learned to add it to a a lot of spreadsheets I use for work so now I finally have better visual data.



finished_dashboard.jpg