Visualizing date ranges

[sdspieg]

Does anybody have any idea whether I would be able, with Excel, to transform a table containing information like this into a graph like the one underneath? Thanks!

#	GP?	Name	Years	#GPs	Sev.	Country	Int.
1	•	War of the League of Venice	1495-1497	3	8	FSA	119
2		Polish-Turkish War	1497-1498	1	3	T	45
3		Venetian-Turkish War	1499-1503	1	4	T	60
4		First Milanese War	1499-1500	1	2	F	29
5	•	Neapolitan War	1501-1504	2	18	PS	269
6		War of the Cambrian League	1508-1509	3	10	FSA	145
7	•	War of the Holy League	1511-1514	4	18	PESA	261
8	•	Austro-Turkish War	1512-1519	2	24	AT	343
9		Scottish War	1513-1515	1	4	E	57
10	•	Second Milanese War	1515-1515	3	3	FSA	43
11	•	First War of Charles V	1521-1526	3	30	FE H	420
12	•	Ottoman War	1521-1531	2	68	TH	958
13		Scottish War	1522-1523	1	3	E	41
14	•	Second War of Charles V	1526-1529	3	18	FE H	249
15	•	Ottoman War	1532-1535	2	28	TH	384
16		Scottish War	1532-1534	1	4	E	55
17	•	Third War of Charles V	1536-1538	2	32	F H	438
18	•	Ottoman War	1537-1547	2	97	TH	1329
19		Scottish War	1542-1550	1	13	E	176
20	•	Fourth War of Charles V	1542-1544	2	47	F H	629
21	•	Siege of Boulogne	1544-1546	2	8	FE	107
22	•	Arundel's Rebellion	1549-1550	2	6	FE	79
23		Ottoman War	1551-1556	2	44	TH	578
24	•	Fifth War of Charles V	1552-1556	2	51	F H	668

<colgroup><col span="2"><col><col span="5"></colgroup><tbody>
</tbody>

 

[thisoldman]

Yes, something similar can be plotted in Excel.

The original chart plots annual casualties on a logarithmic scale with a broken vertical axis, the little jog between 0 and 1,000 indicates the break. We weren't given the casualties but we have the "Severity", which is primarily based on the casualties, so those values were plotted. I avoided the broken axis, something Excel isn't good at showing, and I did not use a logarithmic scale. The log scale was used in the original because the 20th Century war casualties would reduce the earlier values to insignificance.

The original used one-half the casualty value for the first and last years of each war. Rather than complicate this example, I did not opt for this refinement. You may travel the path of nested IFs, but I avoid it when I can.

Reducing the data to only what was needed, I came up with these columns, extending from A1 to E25:

*	A	B	C	D	E
1	#	Years	Sev.	first_year	last_year
2	1	1495-1497	8	1495	1497
3	2	1497-1498	3	1497	1498
4	3	1499-1503	4	1499	1503

<tbody>
</tbody>

Formulas:
Cell D2 and down: =VALUE(LEFT(B2,4))
Cell E2 and down: =VALUE(RIGHT(B2, 4))
Defined names, used later:
severity: = C2 and down
first_year = D2 and down
last_year = E2 and down

Then I constructed a table to summarize the data. Here is a fragment:

*	G	H	I	J
1	*	1	2	3
2	1495	8	0	0
3	1496	8	0	0
4	1497	8	3	0

<tbody>
</tbody>

Formulas:

Cell H2:
    =IF(AND(war_year >= INDEX(first_year, MATCH(H$1, $A$2:$A$25, 0)),
    war_year <= INDEX(last_year, MATCH(H$1, $A$2:$A$25, 0))),
    INDEX(severity, MATCH(H$1, $A$2:$A$25, 0)), 0)

Copied through the table

Defined name:
war_year = G2 and down

I summed each row in the second table to get the data to plot. Again I'm showing only a fragment:

*	AG	AH
1	*	Total Severity
2	1495	8
3	1496	8
4	1497	11
5	1498	3

<tbody>
</tbody>

Formulas:
Cell AH2 and down: =SUM(H2:AE2)

And here's the link to the scatter and bar charts that resulted:

 

[sdspieg]

Impressive. Thanks much!