Doorgaan naar hoofdcontent


Posts uit oktober, 2013 weergeven

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:

ApplesPearsBanana'sA 200150200210020031502004200200510020063002007250200840020094302010250P 20011232002100200315020045432005250200630020075420082512009234201098B 200187200210020032342004543200534520064562007350200840020093892010123
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$30labels2=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!labels3pears=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:


And the labels are based on:


You can also download the file through this link:


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:
TownHaircutHalf GallonT-bone steakManhattan22,21BrooklynHonolulu15,1San Francisco16,79San Jose CA14,13Queens17,79Stamford CT20,03Washington16,29Orange County15,57Boston15,29Manhattan2,34Brooklyn2,12Honolulu3,28San Francisco2,28San Jose CA2,05Queens2,58Stamford CT2,51Washington2,71Orange County2,24Boston2,72Manhattan15,52Brooklyn12,54Honolulu8,74San Francisco11,02San Jose CA10,89Queens11,48Stamford CT11,61Washington10,82Orange County9,58Boston11,16
From F3:I5 I typed:
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) =VL…

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:


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:

You can also download the file through this link:

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 {…

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 1Amount 2Amount 3Amount 4Amount 5200122020018018925820023503202902793252003250290300312267200443040037043534520053503691672341892006456367423367421
You can also download the file through this link:

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:


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 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8Week 9Salesman 1122946631617181923Salesman 21330476417234567Salesman 31431486518Salesman 415324…

Excel: Match Stick Graph

Simple but nice, the match stick graph:

Here are the data from A1:B18:

You can also download the file through this link:

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:

TaskStart DateDuration in daysTask 13-6-20131Task 24-6-20131Task 34-6-20135Task 411-6-20131Task 511-6-201312Task 625-6-20133Task 728-6-20131Task 828-6-20134Task 94-7-20133Task 109-7-20131Task 1110-7-20133Task 1215-7-20133Task 1318-7-20131Task 1421-7-20135Task 1524-7-20137Task 1627-7-20134Task 1730-7-20133Task 182-8-20136Task 195-8-20138Task 208-8-20139Task 2111-8-201310Task 2214-8-201312Task 2317-8-201314Task 2420-8-201315Task 2523-8-201313Task 2626-8-20135
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.…

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 MatrixProductsMarket growthMarket SizeShareMarket ShareMarket Share Largest CompetitorRelative Market ShareProduct13% €  10.000.000 9%14%32%       0,44 Product25% €  20.000.000 19%22%13%       1,69 Product314% € 15.000.000 14%51%25%       2,04 Product417% €  40.000.000 37%12%26%       0,46 Product57% €  12.000.000 11%15%28%       0,54 Product64% €   10.000.000 9%24%34%       0,71
You can also download the file through this link:

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.

Sint Nicolaasga:
St. Jacobiparochie:

Excel: Invert If Negative With Corresponding Background

Just an idea.

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

C1:C10 contains a formula:


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 through this link:

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 ReportBuilder 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…

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:


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:

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 (

You can also download the file through this link:

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:

You can also download …

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:

You can also download the file through this lin…

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:

You can also download the file through this link:…

Outlook: 2010 tips en trucs

Outlook 2010 tips & trucs Je kunt het bestand outlook2010tips.pdf hier downloaden:

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      …

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:

RevenueCostsProfitCompany 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 PlusRevenue - Costs PlusRevenue - 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(D…