dinsdag 29 oktober 2013

Excel: How to Dynamically Compare Different Ranges With Different Starting Point

Look at the X-axis of this graph:


In the example you can use scroll bars to manipulate how to compare the three series.

The data from A1:F31:

Apples Pears Banana's
A 2001 50
2002 100
2003 150
2004 200
2005 100
2006 300
2007 250
2008 400
2009 430
2010 250
P 2001 123
2002 100
2003 150
2004 543
2005 250
2006 300
2007 54
2008 251
2009 234
2010 98
B 2001 87
2002 100
2003 234
2004 543
2005 345
2006 456
2007 350
2008 400
2009 389
2010 123

I created 7 names:

apples =OFFSET(Blad1!$D$2;0;0;Blad1!$I$1;1)
bananas =OFFSET(Blad1!$F$2;Blad1!$H$1;0;Blad1!$I$1;1)
labels1 =Blad1!$C$2:$C$30
labels2 =OFFSET(Blad1!$B$2;Blad1!$G$1;0;30;1)
labels3 =OFFSET(Blad1!$A$2;Blad1!$H$1;0;30;1)
labelsall =Blad1!labels1 & CHAR(13)  &  Blad1!labels2 &CHAR(13) &  Blad1!labels3
pears =OFFSET(Blad1!$E$2;Blad1!$G$1;0;Blad1!$I$1;1)

The cells G1, H1 and I1 are the outcome of scroll bars.

The series in the chart are based on:

=Blad1!apples
=Blad1!bananas
=Blad1!pears

And the labels are based on:

=Blad1!labelsall

You can also download the file CompareYears.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

zaterdag 26 oktober 2013

Excel: Switching Between Graphs, Together or Apart?

Saw a graph like this on the internet. In it self, nothing special.


But in my opinion it would be more clear to group the subjects like this:


But both might be good alternatives. So why not give the viewer a choice. So I added a pick list with two choices: apart or together. So now you have both graphs in one.

Here are the data as I grouped them from A3:D33:

Town Haircut Half Gallon T-bone steak
Manhattan 22,21
Brooklyn
Honolulu 15,1
San Francisco 16,79
San Jose CA 14,13
Queens 17,79
Stamford CT 20,03
Washington 16,29
Orange County 15,57
Boston 15,29
Manhattan 2,34
Brooklyn 2,12
Honolulu 3,28
San Francisco 2,28
San Jose CA 2,05
Queens 2,58
Stamford CT 2,51
Washington 2,71
Orange County 2,24
Boston 2,72
Manhattan 15,52
Brooklyn 12,54
Honolulu 8,74
San Francisco 11,02
San Jose CA 10,89
Queens 11,48
Stamford CT 11,61
Washington 10,82
Orange County 9,58
Boston 11,16

From F3:I5 I typed:

B C
apart 0 0 30
together 10 20 10

Cell F1 refers to the list F4:F5.

From G1 til I1 I typed the formula's:

=VLOOKUP($F$1;$F$4:$I$5;2;FALSE)
=VLOOKUP($F$1;$F$4:$I$5;3;FALSE)
=VLOOKUP($F$1;$F$4:$I$5;4;FALSE)

There are three names. scope Blad1:

AA: =OFFSET(Blad1!$B$4;0;0;Blad1!$I$1;1)
BB: =OFFSET(Blad1!$C$4;Blad1!$G$1;0;Blad1!$I$1)
CC: =OFFSET(Blad1!$D$4;Blad1!$H$1;0;Blad1!$I$1)

I then created I bar chart of A3:D33. The series values I then changed to:

=Blad1!AA
=Blad1!BB
=Blad1!CC

And everything worked just fine.

You can also download the file graphaparttogether.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

vrijdag 25 oktober 2013

Crystal Reports: Match Stick Graph

Just a simple one:

A stacked bar chart based on the product table from the database NorthWind. I used a formula field Top to get the upper part of the column:

maximum({Products.UnitPrice})/5

The data:


The upper column I then changed into a Beveled Box:


For the lower column I switched the data labels on and chose Outside maximum.

The file you can download here: www.walmar.nl/zips/graphmatch.zip

You can also download the file through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

donderdag 24 oktober 2013

Crystal Reports: Gantt Chart With Different Colors Before and After a Given Date

In Crystal Reports it is very easy to create a Gantt Chart. You just need a table with a starting date and an ending data. And there you go. In the given example I used the data from the Orders table from the database Northwind. I just took the data from July 1996.

The regular chart looks like this:

I just chose Color by series to get the same color for all the bars.

Now I created a Gantt Chart with different colors before and after a given date (July 15 1996, in this case).

Here Orange stands for Done and Green for To Do.

How did I create this one?
  • First I created a parameter GivenDate with 15-7-1996 as Default value.
  • Next I created a Formula Field BeforeGivenDate:
if {Orders.OrderDate}<{?GivenDate} and {Orders.ShippedDate} < {?GivenDate} then
    {Orders.ShippedDate} - {Orders.OrderDate}
else
    (if {Orders.OrderDate}< {?GivenDate} and {Orders.ShippedDate}>= {?GivenDate} then
        {?GivenDate}- {Orders.OrderDate}
    else
        0)
  • And then a Formula Field AfterGivenDate:
if {Orders.OrderDate}>= {?GivenDate} and {Orders.ShippedDate}>= {?GivenDate} then
    {Orders.ShippedDate}-{Orders.OrderDate}
else
    (if {Orders.OrderDate}< {?GivenDate} and {Orders.ShippedDate} >= {?GivenDate} then 
        {Orders.ShippedDate}- {?GivenDate}
    else
        0)
  • Creating the chart I picked a regular Horizontal Stacked Bar Chart instead of a regular Gantt chart.
  • For the Data Axis I picked a minimum of 35245 and a maximum of 35305.
  • I switched off the legend.
  • The Data should be like this:
  • I just chose Color by series to get the same color for the same set.
  • For the bar OrderDate I chose as a color Transparant.
  • @BeforeGivenDate I colored orange and @AfterGivendate green.
  • The format of the horizontal Y-axis I changed like this, Category: Date and Type: 8-2-2002

The file I created you can download here: www.walmar.nl/zips/graphganttcolors.zip

You can also download the file through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

woensdag 23 oktober 2013

Excel: Why Not Split the Y-Axis Four Times Or More?

In this example I split the Y-axis into 5 parts. The main principles? I used a 2-D bar combined with data labels to create the Y-axis. I combined this 2-D bar chart in one graph with a stacked column chart. Integrated with this stacked column chart I used line charts to create the X-axis labels.


I already managed to split the Y-axis, but in this example we can do this unlimited times.

Here are the the basic data:

Amount 1 Amount 2 Amount 3 Amount 4 Amount 5
2001 220 200 180 189 258
2002 350 320 290 279 325
2003 250 290 300 312 267
2004 430 400 370 435 345
2005 350 369 167 234 189
2006 456 367 423 367 421

You can also download the file splityaxis4times.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Excel: Offset Function With Relative or Mixed Cell Referencing

Normally when you use the OFFSET function to create dynamic names, it will use absolute cell referencing. So, you will get examples like this:

=OFFSET(Sheet2!$A$1;0;0;COUNTA(Sheet2!$A:$A);1)

So, what if we used relative cell referencing instead? The result would depend on the cell our cursor is in when we create our formula and the cell our cursor is in when we are looking at the name again.
  • Fill the range A1:A4 with the figures 12 to 15.
  • Put your cursor in A1 again.
  • Create a name RANGE based on the formula with relative cell referencing:
=OFFSET(Sheet1!A1;0;0;COUNTA(Sheet1!A:A);1)
  • Now put your pointer in B15.
  • Look at the name again:

As you can see, we get a different formula now based on the position of our cursor. So for what purpose could we use relative cell referencing?

On a sheet with the name relativeoffset,I created a table in A1:J18 like this:

Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9
Salesman 1 12 29 46 63 16 17 18 19 23
Salesman 2 13 30 47 64 17 23 45 67
Salesman 3 14 31 48 65 18
Salesman 4 15 32 49 66 19 19 19 56
Salesman 5 16 33 50 67 20 20 34
Salesman 6 17 34 51 68 21 21 45 45
Salesman 7 18 35 52 69 22 22 56
Salesman 8 19 36 53 70
Salesman 9 20 37 54 71 23 23 23 23
Salesman 10 21 38 55 72 24 24
Salesman 11 22 39 56 73 25 25 25
Salesman 12 23 40 57 74 26 26 26 26
Salesman 13 24 41 58 75 27
Salesman 14 25 42 59 76 28 28 28 28
Salesman 15 26 43 60 77 29 29 29
Salesman 16 27 44 61 78 30 30 30 30
Salesman 17 28 45 62

As you can see, not every row is completely filled in. Now I created three names:

With the cursor in B2:

BLOCK =OFFSET(relativeoffset!$B2;0;0;1;COUNTA(relativeoffset!$B2:B2))

SALESMAN =OFFSET(relativeoffset!$A1;0;0;1;1)

WEEK =OFFSET(relativeoffset!$B$1;0;0;1;COUNTA(relativeoffset!$B2:B2))

As you can see, I actually used mixed cell referencing.

Now I created a graph with just one series:

Series name: =relativeoffset!Salesman
Series value: =relativeoffset!Block
Axis label names: =relativeoffset!Week

To get the graph really going (the formula needs recalculating every time I move the cursor) I had to add a bit of VBA to the worksheet relativeoffset:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Calculate
End Sub

And then the content of the graph moves along with your cursor:


In poistion F10 I get Salesman 9 until Week 5 and so on.

You can also download the file graphexperiment.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

maandag 21 oktober 2013

Excel: Match Stick Graph

Simple but nice, the match stick graph:


Here are the data from A1:B18:

Year Apples
2000 20
2001 71
2002 91
2003 71
2004 71
2005 20
2006 4
2007 40
2008 10
2009 16
2010 47
2011 77
2012 12
2013 68
2014 76
2015 62
2016 7

You can also download the file lucifergrafiek.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

zondag 20 oktober 2013

Excel: Gantt Chart With Different Colors Before and After a Given Date

On the internet you can find many examples of Excel Gantt charts, either created as a graph or using conditional formatting and how to build them.

My example has some additions to the graph type of the Gantt chart:
  • A date line (the black vertical line).
  • Different colors for the task or part of a task before and after a specific date (in this case July 31).


Here are the data, from A1:C27:

Task Start Date Duration in days
Task 1 3-6-2013 1
Task 2 4-6-2013 1
Task 3 4-6-2013 5
Task 4 11-6-2013 1
Task 5 11-6-2013 12
Task 6 25-6-2013 3
Task 7 28-6-2013 1
Task 8 28-6-2013 4
Task 9 4-7-2013 3
Task 10 9-7-2013 1
Task 11 10-7-2013 3
Task 12 15-7-2013 3
Task 13 18-7-2013 1
Task 14 21-7-2013 5
Task 15 24-7-2013 7
Task 16 27-7-2013 4
Task 17 30-7-2013 3
Task 18 2-8-2013 6
Task 19 5-8-2013 8
Task 20 8-8-2013 9
Task 21 11-8-2013 10
Task 22 14-8-2013 12
Task 23 17-8-2013 14
Task 24 20-8-2013 15
Task 25 23-8-2013 13
Task 26 26-8-2013 5

The date is in cell I1: 31-7-2013.

And there are a lot of formula's in D1:H27. I hid the columns D:H.

You can also download the file ganttchart.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

zaterdag 19 oktober 2013

Excel: Boston Consulting Group Matrix

Evidently, this is not my idea. But I made some adjustments to it and that is what I want to present here.


The data A1:G8:

Boston Consulting Group Matrix
Products Market growth Market Size Share Market Share Market Share Largest Competitor Relative Market Share
Product1 3%  €  10.000.000 9% 14% 32%        0,44
Product2 5%  €  20.000.000 19% 22% 13%        1,69
Product3 14%  € 15.000.000 14% 51% 25%        2,04
Product4 17%  €  40.000.000 37% 12% 26%        0,46
Product5 7%  €  12.000.000 11% 15% 28%        0,54
Product6 4%  €   10.000.000 9% 24% 34%        0,71

You can also download the file bcgm.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

vrijdag 18 oktober 2013

Friesland: verslagen Grêfskriftekommisje Fryske Akademy

In de loop van de afgelopen jaren zijn verschillende kerken in Friesland gerestaureerd. De Grêfskriftekommisje van de Fryske Akademy heeft in zulke gevallen een nauwkeurige inventarisatie gemaakt van de bekende en onbekende grafschriften.

Veel van deze verslagen, wisselend in het Fries en Nederlands, zijn nooit gepubliceerd. Ik wil ze nu via deze Blog beschikbaar stellen in de vorm van PDF's.

Arum: www.walmar.nl/zips/arum.pdf
Cornwerd: www.walmar.nl/zips/cornwerd.pdf
Drachten: www.walmar.nl/zips/drachten.pdf
Goutum: www.walmar.nl/zips/goutum.pdf
Haskerhorne: www.walmar.nl/zips/haskerhorne.pdf
Heerenveen: www.walmar.nl/zips/heerenveen.pdf
Leeuwarden: www.walmar.nl/zips/leeuwarden.pdf
Oosterlittens: www.walmar.nl/zips/oosterlittens.pdf
Sint Nicolaasga: www.walmar.nl/zips/stnicolaasga.pdf
St. Jacobiparochie: www.walmar.nl/zips/stjacobiparochie.pdf
Stavoren: www.walmar.nl/zips/stavoren.pdf
Winsum: www.walmar.nl/zips/winsum.pdf


woensdag 16 oktober 2013

Excel: Invert If Negative With Corresponding Background

Just an idea.


A simple idea. Here are the data from A1:C10:

0-10% 125 500
10-20% -24 -500
20-30% 245 500
30-40% 432 500
40-50% -123 -500
50-60% 235 500
60-70% 53 500
70-80% 67 500
80-90% -87 -500
90-100% 98 500

C1:C10 contains a formula:

=IF(B1>0;500;-500)

  • All three series are in the column graph.
  • Series 3 is connected to the secondary axis.
  • Series 3 Overlapped 100% and No Gap 0% and Transparancy 50%.
  • Both Y-axis between -500 and 500.
  • Both series: Invert if negative.
  • Right Y-axis removed.
  • Data labels for the second series switched on.
You can also download the file excelbackground.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Crystal Reports vs. SS RS / Report Builder: Repeating Column Headers on Each Page

Crystal Reports
In Crystal Reports, this is very simple. When you put a number of fields in the detail section of your reports, it will automatically put the field names in the page header. The page header will be repeated on every page and the problem is solved.

In case of a group, you can simply check the option Repeat Group Header On Each Page:


Report Builder / Reporting Services
In Report Builder / Reporting Services it is all a bit complicated. In a table, for example, you do not have column groups, so Report Builder does not recognize which textboxes are the column headers and checking Repeat header columns on each page doesn't work.


A known bug for which we need a work around. Instead, we need to open Advanced Mode in the Groupings pane:


We'll get:

  • Clicking on a Static group highlights the corresponding textbox in the tablix.
  • For the column headers that you want to repeat, select the Static group that highlights the leftmost column header.

This is generally the first Static group listed. 
  • In the Properties window, set the RepeatOnNewPage property to True.
  • Make sure that the KeepWithGroup property is set to After

The KeepWithGroup property specifies which group to which the static member needs to stick. If set to After then the static member sticks with the group after, or below, it acting as a group header. If set to Before, then the static member sticks with the group before, or above it, acting as a group footer. If set to None, Report Builder decides where to put the static member.

Now when you view the report, the column headers repeat on each page of the tablix.

Crystal Reports: graph based on just month names and no years

I based my example on the database Northwind using the tables Orders and Order_details.

I created a formula field Revenue:

{Order_Details.Quantity}*{Order_Details.UnitPrice}

and another one Monthname:

monthname(month({Orders.OrderDate}), true)

Based on those two formula fields I created the graph which then looks like this:
The problem is, it is then sorted on mont name in an alphabetical order (the month names are in Dutch by the way). The way I found to solve this problem is this.

  • Go tot the Chart Expert:


  • Click Monthname.
  • Click Order.
  • Choose In specified order.
  • Now, fill in all the names of the months like this.
  • Then click Others.
  • Click Discard all others.
  • Click OK twice.
Your graph will then end up like this, with the month names in the right order:








maandag 14 oktober 2013

Excel: Alternative to Broken Axis on Column Graph

My idea for an alternative to the broken axis on a column graph looks like this:


This image of a broken axis graph I found on Andy Pope's website (http://www.andypope.info/charts/brokencolumn.htm):


You can also download the file restrictmaximum.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

zondag 13 oktober 2013

Crystal Reports: Graph with Different Colors Below and Above Norm

A graph in Crystal Reports with different colors when the revenue values are above the norm or when they are below the norm.


The graph is based on the tables Orders and Order_Details from the database Northwind.

  • Formula Field Revenue:
{Order_Details.Quantity}*{Order_Details.UnitPrice}
  • Group on Orders.OrderDate per Month.
  • Summary on Formula Field Revenue for the created group.
  • Formula Field RevenueAbove:

if Sum ({@revenue}, {Orders.OrderDate}, "monthly")> {?prmNorm} then
    Sum ({@revenue}, {Orders.OrderDate}, "monthly")
else
    0
  • Formula Field RevenueBelow:
if Sum ({@revenue}, {Orders.OrderDate}, "monthly")<= {?prmNorm} then
    Sum ({@revenue}, {Orders.OrderDate}, "monthly")
else
    0
  • Parameter Norm, type Numeric.
  • Formula Field Norm:
{?prmNorm}
  • Created a Line Chart with the data:

  • Changed the lines for revenueabove and revenuebelow into Risers.
  • Picked nice colors.
You can dowload the file here: www.walmar.nl/zips/graphbelowabove.zip

You can also download the file through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

zaterdag 12 oktober 2013

Crystal Reports: Pareto Analysis

Based on the tables Categories, Order_Details, Orders and Products from the Northwind database I created this example of a Pareto Analysis, the best I could get so far:


  • To get the percentage I first created a Running Total {#PercentageCum}on the field Order_Details.ProductID. 
  • Then I created a Formula Field Percentage:
{#PercentageCum}/Count ({Order_Details.ProductID})*100

  • This field I used in the report.
  • The initial column graph is based on change of Categories.CategorieName.
  • Values:
Count of Order_Details.ProductId
#PercentageCum
  • I linked the second series to the second Y-axis.
  • For this series I chose line graph instead of column and gave it the color red.
  • I turned the secondary Y-axis off (you can not use the field Percentage in the graph; so you can not show the real percentage on the Y-axis: Crystal Reports doesn't allow charts to use formulas that refer to running totals).
You can download the file here: www.walmar.nl/zips/GraphPareto.zip

You can also download the file through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Crystal Reports: Graph with Different Colors for Positive and Negative Values

How hard is it to create special graphs in Crystal Reports. In my opinion a tool like Crystal Reports lacks the flexibility of Excel, but this does not mean one can not create nice graphs.

In this case I created a graph with different colors for positive and negative values. Crystal Reports version 2011 does not do this automatically. You need to help it.

I started with creating a straightforward vertical bar graph.

and changed it in to this:

Apart from some minor changes like removing the legend and adding data labels, I only had to do one major change: adding conditions to the Color Highlight tab.

In the Chart Expert I picked the tab Color Highlight:


I created two conditions for values below and above the zero line, as you can see.

In this example I experimented with the Access Northwind database. You can download the file here:

www.walmar.nl/zips/StackedBarAboveBelow.zip

You can also download the file through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

vrijdag 11 oktober 2013

Outlook: 2010 tips en trucs

Outlook 2010 tips & trucs

Je kunt het bestand outlook2010tips.pdf hier downloaden:


https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

 I N H O U D S O P G A V E

1             Algemeen         
1.1          Werkbalk snelle toegang            
1.2          Snelle stappen
2             E-mail  
2.1          Regels 
2.2          CC mail onderscheiden
2.3          Verwijderde items automatisch wissen              
2.4          Een pauze voor het verzenden               
2.5          Afzender toevoegen aan Contactpersonen      
2.6          E-mailadressen verslepen         
2.7          Verzonden berichten terughalen           
3             Vergaderverzoeken     
3.1          Automatisch accepteren            
4             Reminder Afwezigheidsassistent / Automatische antwoorden               
5             Handtekening maken  
6             Postbus toevoegen      
7             PST bestanden
7.1          Inleiding             
7.2          Bestaande archiefbestanden koppelen              
7.3          Nieuw archiefbestand aanmaken        


Excel: Display Profit Plus or Minus Per Company

How do you display for each company which one had a total positive profit (revenue minus costs) and which one a negative. My idea:



Here are the data from A1:D11:

Revenue Costs Profit
Company 1  €      500  € 400  €  100
Company 2  €   1.000  € 760  €  240
Company 3  €      890  € 900  €   -10
Company 4  €      640  € 700  €   -60
Company 5  €      570  € 600  €   -30
Company 6  €      875  € 800  €    75
Company 7  €      690  € 600  €    90
Company 8  €      500  € 600  € -100
Company 9  €   1.050  € 700  €  350
Company 10  €      900  € 700  €  200

From F1:H11 I created formulas:

Revenue - Costs Plus Revenue - Costs Plus Revenue - Costs Minus
=IF(D2:D11>0;B2:B11;NA()) =IF(D2:D11<=0;B2:B11;NA()) =IF(D2:D11<=0;C2:C11*-1;NA())
=IF(D3:D12>0;B3:B12;NA()) =IF(D3:D12<=0;B3:B12;NA()) =IF(D3:D12<=0;C3:C12*-1;NA())
=IF(D4:D13>0;B4:B13;NA()) =IF(D4:D13<=0;B4:B13;NA()) =IF(D4:D13<=0;C4:C13*-1;NA())
=IF(D5:D14>0;B5:B14;NA()) =IF(D5:D14<=0;B5:B14;NA()) =IF(D5:D14<=0;C5:C14*-1;NA())
=IF(D6:D15>0;B6:B15;NA()) =IF(D6:D15<=0;B6:B15;NA()) =IF(D6:D15<=0;C6:C15*-1;NA())
=IF(D7:D16>0;B7:B16;NA()) =IF(D7:D16<=0;B7:B16;NA()) =IF(D7:D16<=0;C7:C16*-1;NA())
=IF(D8:D17>0;B8:B17;NA()) =IF(D8:D17<=0;B8:B17;NA()) =IF(D8:D17<=0;C8:C17*-1;NA())
=IF(D9:D18>0;B9:B18;NA()) =IF(D9:D18<=0;B9:B18;NA()) =IF(D9:D18<=0;C9:C18*-1;NA())
=IF(D10:D19>0;B10:B19;NA()) =IF(D10:D19<=0;B10:B19;NA()) =IF(D10:D19<=0;C10:C19*-1;NA())
=IF(D11:D20>0;B11:B20;NA()) =IF(D11:D20<=0;B11:B20;NA()) =IF(D11:D20<=0;C11:C20*-1;NA())

and from J1:K11:

Revenue - Costs Minus Profit Plus Profit Minus
=IF(D2:D11>0;C2:C11*-1;NA()) =IF(D2:D11>0;D2:D11;0) =IF(D2:D11<=0;D2:D11;0)
=IF(D3:D12>0;C3:C12*-1;NA()) =IF(D3:D12>0;D3:D12;0) =IF(D3:D12<=0;D3:D12;0)
=IF(D4:D13>0;C4:C13*-1;NA()) =IF(D4:D13>0;D4:D13;0) =IF(D4:D13<=0;D4:D13;0)
=IF(D5:D14>0;C5:C14*-1;NA()) =IF(D5:D14>0;D5:D14;0) =IF(D5:D14<=0;D5:D14;0)
=IF(D6:D15>0;C6:C15*-1;NA()) =IF(D6:D15>0;D6:D15;0) =IF(D6:D15<=0;D6:D15;0)
=IF(D7:D16>0;C7:C16*-1;NA()) =IF(D7:D16>0;D7:D16;0) =IF(D7:D16<=0;D7:D16;0)
=IF(D8:D17>0;C8:C17*-1;NA()) =IF(D8:D17>0;D8:D17;0) =IF(D8:D17<=0;D8:D17;0)
=IF(D9:D18>0;C9:C18*-1;NA()) =IF(D9:D18>0;D9:D18;0) =IF(D9:D18<=0;D9:D18;0)
=IF(D10:D19>0;C10:C19*-1;NA()) =IF(D10:D19>0;D10:D19;0) =IF(D10:D19<=0;D10:D19;0)
=IF(D11:D20>0;C11:C20*-1;NA()) =IF(D11:D20>0;D11:D20;0) =IF(D11:D20<=0;D11:D20;0)

When you want, you can replace all the formulas by names.
  • Then I created a stacked bar graph based on F1:I11.
  • Changed the colors
  • Chose categories in reverse order.
  • Y-axis labels to Low.
  • Maximum for X-axis to 1200 and minimum to 1000.
  • Number format of the X-axis to 
#.##0;#.##0
  • Then added J1:K11.
  • Added both to the secondary axis.
  • Again maximum for X-axis to 1200 and minimum to 1000.
  • Added the secondary Y-axis.
  • Again categories in reverse order.
  • Deleted the secondary X-axis
  • Deleted the secondary Y-axis.
  • Added data labels.
  • Formatted them with:
_ € * #.##0_ ;_ € * #.##0_ ;_ € * "-"??_ ;_ @_ 
  • Added the graph title using the formula:
=Blad1!$A$37
  • In A37 the formula:
=Blad1!$B$1 & " Minus " & Blad1!$C$1 &": " &Blad1!$D$1

You can also download the file revenueplusminus.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing