woensdag 17 september 2014

Excel: Pivot Tables Versus Functions Like SUMIFS, AVERAGEIFS And COUNTIFS; Ad Hoc Versus Standard

Thee Excel pivot table may be the best tool in the market for creating ad hoc report. We can very rapidly compare data from various angles.

As soon as we are dealing with standard reports, reports with a longer time span and standardized results, I personally think the pivot table is less suitable as a reporting instrument.

In that case I prefer Excel functions like:

  • SUMIFS
  • AVERAGESIFS
  • COUNTIFS


To build reports like this is definitely more complex and time consuming. In return you get longer lasting reports.

In the following example I created three examples to compare SUMIFSAVERAGEIFS and COUNTIFS to the similar pivot tables.


The examples are based on a dataset. Examples and dataset you can download. In the dowload it is easy to see how I used the different functions.

Even in these relatively simple examples you can see an important diffenence. If you select a specific country the left examples still show all rows and columns. Some rows and columns disappear from the pivot table.

You can download the file ExcelPivotTableDifferent.xlsx via:

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

vrijdag 12 september 2014

Excel: draaitabellen versus functies als SOMMEN.ALS, GEMIDDELDEN.ALS en AANTALLEN.ALS; ad hoc versus standaard

De Excel draaitabel is misschien wel de beste tool in de markt voor ad hoc rapporten. We kunnen heel snel gegevens langs allerlei invalshoeken met elkaar vergelijken.

Zodra het evenwel gaat om standaard rapporten, rapporten die langer meemoeten en de uitkomsten gestandaardiseerd aan moeten leveren, vind ik de draaitabel minder geschikt.

Dan geef ik de voorkeur aan Excel functies als:

  • SOMMEN.ALS
  • GEMIDDELDEN.ALS
  • AANTALLEN.ALS

De opbouw van dergelijke rapporten is beslist ingewikkelder en vergt dus meer tijd. Tegenover die tijdsinvestering staat een langere levensduur.

In het onderstaande voorbeeld heb ik met drie voorbeelden de SOMMEN.ALS, GEMIDDELDEN.ALS en AANTALLEN.ALS uitgezet tegen de overeenkomstige draaitabellen.



De voorbeelden zijn gebaseerd op een dataset. Voorbeelden en dataset zijn te downloaden. In de download is goed te zien hoe ik de verschillende functies gebruikt heb.

Zelfs in deze betrekkelijk eenvoudige voorbeelden is al een belangrijk verschil te zien. Bij een selectie op land blijven de linker voorbeelden alle rijen en kolommen tonen. Uit de draaitabellen verdwijnen de rijen en kolommen die geen gegevens opleveren.

Het bestand ExcelDraaitabelanders.xlsx is te downloaden via:

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

Verdere tips: je kunt het bestand exceltips.pdf (laatst gewijzigd (12-9-2014) hier downloaden:

dinsdag 9 september 2014

Excel: nice example of the formula EOMONTH

Got a question about calculating an average for monthly data. Here is an example of the data starting in cell A1:

date dieselprice avgpermonth
1-1-2007 0,985 0,96355
2-1-2007 0,985 0,96355
3-1-2007 0,984 0,96355
4-1-2007 0,984 0,96355
5-1-2007 0,978 0,96355
6-1-2007 0,964 0,96355
7-1-2007 0,964 0,96355
8-1-2007 0,964 0,96355
9-1-2007 0,964 0,96355
10-1-2007 0,964 0,96355
11-1-2007 0,964 0,96355
12-1-2007 0,958 0,96355
13-1-2007 0,958 0,96355
14-1-2007 0,958 0,96355
15-1-2007 0,958 0,96355
16-1-2007 0,954 0,96355
17-1-2007 0,954 0,96355
18-1-2007 0,984 0,96355
19-1-2007 0,948 0,96355
20-1-2007 0,948 0,96355
21-1-2007 0,948 0,96355
22-1-2007 0,948 0,96355
23-1-2007 0,948 0,96355
24-1-2007 0,958 0,96355
25-1-2007 0,964 0,96355
26-1-2007 0,964 0,96355
27-1-2007 0,964 0,96355
28-1-2007 0,964 0,96355
29-1-2007 0,964 0,96355
30-1-2007 0,964 0,96355
31-1-2007 0,964 0,96355
1-2-2007 0,964 0,98239
2-2-2007 0,964 0,98239
3-2-2007 0,978 0,98239
4-2-2007 0,978 0,98239
5-2-2007 0,978 0,98239
6-2-2007 0,976 0,98239
7-2-2007 0,992 0,98239
8-2-2007 0,992 0,98239
9-2-2007 0,982 0,98239
10-2-2007 0,982 0,98239
11-2-2007 0,982 0,98239
12-2-2007 0,982 0,98239
13-2-2007 0,993 0,98239
14-2-2007 0,983 0,98239
15-2-2007 0,983 0,98239
16-2-2007 0,983 0,98239
17-2-2007 0,973 0,98239
18-2-2007 0,973 0,98239
19-2-2007 0,973 0,98239
20-2-2007 0,974 0,98239
21-2-2007 0,974 0,98239
22-2-2007 0,974 0,98239
23-2-2007 0,984 0,98239
24-2-2007 0,994 0,98239
25-2-2007 0,994 0,98239
26-2-2007 0,994 0,98239
27-2-2007 1,004 0,98239
28-2-2007 1,004 0,98239

Of course, I could have used a formula like 

=AVERAGE($C$2:$C$32)

and change that every time it is needed. In stead I wanted to use a formula which calculates the average for each month automatically, depending on the row it is in.

I ended up with this one, from cell C2 down:

=AVERAGE(INDIRECT(ADDRESS(ROW()-DAY(A2)+1;2)&":"&ADDRESS(ROW()+DAY(EOMONTH(A2;0))-DAY(A2);2)))

This formula works fine for me. Just wondering wether it can be done more easy.

On a second worksheet I created:

end of month avg per   month
0 31-1-2007 0,964
1 28-2-2007 0,982
2 31-3-2007 1,000
3 30-4-2007 1,024
4 31-5-2007 1,026
5 30-6-2007 1,049
6 31-7-2007 1,058
7 31-8-2007 1,046
8 30-9-2007 1,077
9 31-10-2007 1,091
10 30-11-2007 1,180
11 31-12-2007 1,144
In cell B2 I put the formula:

=EOMONTH(prices!$A$2;A2)

and copied it downwards.

In cell C2 I put the formula:

=VLOOKUP(B2;prices!A:C;3;FALSE)

in order to  look up the right average on the first sheet.

Thanks to +Brian Canes and +Shane Devenshire we can even replace the formula in C2:

=AVERAGEIFS(prices!B$2:B$3660;prices!A$2:A$3660;">"&EOMONTH(B2;-1);prices!A$2:A$3660;"<="&EOMONTH(B2;0))

If we use the last formula we don't even need the third helper column on the first sheet anymore.

You can download the file ExcelExampleEOmonth.xlsx through

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

Friesland: Van Groot Wygara te Terzool naar het wapen op de kerkzak van Dronrijp

Ik liep al eerder tegen het wapen van de familie Wygara aan. Het stond op de met zilver beslagen steel van een kerkzak van Dronrijp.


De tekst onderaan luidt: M V W 1695

Na enig zoeken bleek het om het wapen van de familie Wygara te gaan. De initialen hebben betrekking op Magdalena van Wygara. Ze was in 1693 te Dronrijp getrouwd met Harmen Rudolph van Rusier. Op 14 november 1705 overleed ze te Leeuwarden blijkens een aantekening in de Dronryper Cronyck van schoolmeester H. Roucama:

s avonds is na een langduirige podrageuse ziekte van eenige jaren
gesturven tot Leeuwarden mijn (schoolmeester Roucama) buirvrouw vrouw Magdalena
van Wijgara, huisvrouw van de heer majoor Harman Rudolf van Rusiers

Haar ouders waren mij aanvankelijk onduidelijk. Op internet vond ik een melding dat ze een dochter zou zijn van Herman Feickes van Wygara. Dat blijkt evenwel niet te kloppen. Op 26 juli 1655 laat kapitein Dirk van Wygara haar dopen te Harich.

Dan kunnen we het spoor terug volgen. Dirk trouwt in 1663 met Ymck (Emilia) van Glins. Hij is afkomstig uit Minnertsga. Hij zal rond 1638 geboren zijn. Op de klok van Minnertsga uit 1648 vinden we de naam van zijn zeer waarschijnlijke vader: Wopke van Wygara.

Zijn zuster Geertruida ligt begraven in de kerk van Wyckel. We hebben een tekening van Martin. We herkennen rechts hetzelfde wapen:


Het wapen moet Martin ergens anders vandaan hebben gehaald. Op het origineel is het wapen niet meer heel goed zichtbaar:



Van de ouders van Dirk en Geertruid, Otto van Wygara en Magadalena van Tjessinga is er een familiebijbel met familiewapen bekend. We vinden een afdruk in De zilveren eeuw Fries pronkzilver in de zeventiende eeuw:


Het zelfde Wygara wapen.

Otto's tante Bauck ligt begraven in de Martinikerk van Franeker:


Het wapen is weggekapt maar met enige moeite is de ster nog wel te herkennen:


De grootouders van Otto, Wopke en Jouwer Dircks Fogelsangh liggen in de kerk van Terzool begraven. Ook van deze zerk is het wapen uitgekapt.

Met Terzool zijn we terug bij de basis. In Terzool stond ooit Groot Wygara. Via http://www.stinseninfriesland.nl/GrootWigaraTerzool.htm vinden we een uitgebreide beschrijving van de state en zijn bewoners. In 1483 wordt Wopcke Wygara als eerste van de familie genoemd.

Op basis van diverse bronnen waaronder mijn database met inscripties heb ik het volgende schema op kunnen stellen: