E90E50fx... only formulas

Welcome to the E90E50fx site!
This blog-site is a collection of articles about Excel. We share here our ideas, discoveries and some teaching materials about Excel related topics. The posts here are focused on formula-based solution. You can find lot of charts built with formulas.
If you are interested in other Excel realated topics, you can check out the E90E50 site or take a look at our E90E50charts collection.
Please respect the work of the authors. All of the publications are our own work. Feel free to use it, share it - but please always mention the source.

Have fun,
The FrankensTeam

Combine Bubble and xy scatter line chart

posted Jul 8, 2014, 8:39 AM by Krisztina Szabó   [ updated Jul 8, 2014, 8:47 AM ]


Freddy: Behind the bookcase. Hand me that robe, would you dear? You were right. It's coming from behind this wall. Where is it, where is it?
Inga: What?
Freddy: There's always a device. If I could just spot the triggering mechanism. Hello. It seems louder over here. Hand me that candle, will you? Put the candle back. Alright, I think i have it figured out now.


by The FrankensTeam

Have you ever wanted to connect the bubbles of a bubble chart with lines?
For example to create kind of a timeline: how the position of a product changes month by month in a Growth/Profit matrix.
You would like to see something like this:


Bubbles are based on this sort of data table:


Well, it’s a fact that bubble chart can not be combined with any other charts, so the usual “change series chart type” way will not work.

Our out-of-the-box idea is to use trendline.
We started to think about moving average trendline, because this is very simple to calculate based on the Period - as you can read on MS office site:
“For example, if Period is set to 2, the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, etc.”
The idea is to create a dummy data series which determines a trendline which equals to the connecting line of the original data points. Only need to reverse the calculation logic of 2 period moving average: now the first data and the moving average (=the original data) is given, and need to calculate the second data.
In other words, you will need a help column, where the average of the first two data is equal to the January Proft (-2%), the average of the second and third data is equal to the February Profit (5%), etc.
The formula behind this dummy series column now goes without saying. Obviously we need one data point more than the original series. This additional data point could be a fixed value or you could use the first data of your original table.

Let’s see step by step.

1. Build up the help columns.
The very-first data (yellow cells) starts with a fixed 0.
  • Growth-help: from the second data is equal to the original growth data (this is the x values of the series)
  • Profit-help: from the second data has a simple formula: the original Profit multiplied by 2, minus the data above. So for example the dummy 8% for March is the average of the original March and February data: (11%+5%)/2 = 8%
  • Size-help contains fixed 0 values.


2. Add a dummy series to your bubble chart using data from the help column:
(right click on the chart, Select data / Add)


3. Add a trendline to the dummy series:
  • in Excel 2010: Select the dummy series under Chart tools / Layout, and add a moving average under Trendline
  • in Excel 2013: Select the dummy series under Chart tools / Format, and add a Trendline with moving average under Chart tools / Design / Add chart element / Trendline.

4. Select the trendline and format it according to your needs.

+1. Dynamic model
It is easy to change the model to update automatically when you add new data below the data table. You only need to copy down the dummy series formulas to more rows, add names and use the names in the chart series instead of fix sheet ranges.
In our file you will find the below names:

x =booble_linea!$C$4:INDEX(booble_linea!$C$4:$C$102,COUNTA(booble_linea!$C$4:$C$102))
y =OFFSET(x,,1)
size =OFFSET(x,,1)
x_dummy =OFFSET(booble_linea!$G$3,,,ROWS(x)+1)
y_dummy =OFFSET(x_dummy,,1)
size_dummy =OFFSET(x_dummy,,2)


You can download our example file for better understanding:

Please share your thoughts with us!

Leave a comment




Vintage charts in Excel: arrows from one point

posted Jun 27, 2014, 5:55 AM by Krisztina Szabó   [ updated Jun 29, 2014, 12:23 PM ]




Fraü Blucher
: And this is your room. It was your grandfather Victor's room.


by The FrankensTeam

In the last post we shared a vintage chart reproduction found in a great, old book about charts. Today's vintage chart has a totally different story. The below picture is from a (Hungarian) wall calendar illustrated with photos of legendary actors and actresses.
The chart is a stage prop... well, it is not suitable for practical use... it's for theater use only!


Play: State Department Store by Tibor Barabás and Béla Gádor. 1952
Characters: Tivadar Bilicsi (Uncle Bezzeg), Kálmán Latabár (Dániel) and Kamill Feleki (Glauziusz)

Here is the stage prop enlarged:


The title says "Table of competition". The segments in the circle represents store departments, the lines are the performance of those departments in the socialist competition. The best department ("Férfi ruha" = Male clothing) is marked with roman number I.

The Excel reproduction looks like:


It was a funny exercise to create this chart though as you see, it is definitely not for practice, but some tricks could be handy in other projects:
  • The chart type is a combination of xy scatter, pie and doughnut.
  • We used a pie chart filled with a black circle drawing to shape the circle border.
  • The text labels were added using a doughnut chart.
  • We rotated the labels using our Rotate chart label tool.
  • The marker on the best department is a roman number I - it was added as manually rotated picture.
Download the file so you can see the pictures we used and the data behind the xy scatter lines.

NOTE: In Excel 2013 it is possible to change the size of the data lables individually, so the final result with the text labels looks much more better comparing to 2010 version.

Leave a comment




Vintage charts in Excel: bar chart with highlight

posted Jun 25, 2014, 8:27 AM by Krisztina Szabó   [ updated Jun 28, 2014, 2:10 AM ]


Medical Student: But aren't you the grandson of the famous Dr. Victor Frankenstein who went into graveyards, dug up freshly buried corpses, and transformed dead components into...

by The FrankensTeam

It is always exciting to see vintage charts and infographics - how many good chart and formatting ideas were discovered before the Excel-era. Interesting challenge to re-create these charts with our modern techniques - so with Excel.

http://archive.org/stream/graphicpresentat00brinrich#page/n0/mode/1up

In Graphic presentation by Willard C. Brinton from 1939 we found a bunch of great chart examples.
This one is on page 152:

http://archive.org/stream/graphicpresentat00brinrich#page/152/mode/1up

The book tells:
<<The chart represents occupational distribution in 1930 of 134 MIT graduates of the classes of 1917 1929 inclusive. The emphasis of the area of "Major Executive" tends to make the comparison a vertical one resulting in area comparison.>>

Here is the Excel-reproduction:



How we did it?
The chart type is stacked bar chart, the gap width was set to 30%. We added Series Lines to the chart - these thin lines to connect the data series. (Chart tools / Design / Add Chart Element / Lines)
The widest data series (Major Executive, in column F in the below data table) was formatted individually using pattern fill. As you can see on the original picture, this series is highlighted with stronger series lines.
Unfortunately it is not possible to set the format of these lines individually, so we used a small trick: two more columns were added to the data table: before and after the highlighted series you can see 0,2 values (column E and G). This very thin series is formatted with solid black fill.


To supress the small (below 2) values in data labels, we use custom number format with code: [>2]0,0;""

Download the Excel file.

You can find another interesting vintage chart reproduction here.
Or take a look at our gallery with >150 Excel charts.

Leave a comment



Chandoo Excel Dashboards challenge

posted Jun 12, 2014, 9:14 AM by Krisztina Szabó   [ updated Jun 12, 2014, 9:58 AM ]

Inga: Oh, Doctor. I'm sorry
Freddy: No, no. Be of good cheer. If science teaches us anything, it teaches us to accept our failures as well as our successes, with quiet dignity and grace. Son of a bitch bastard, I'll get you for this. What did you do to me? What did you do to me?


by The FrankensTeam

We participate on a great challenge opened by Chandoo: visualize US State to State migration trends. Now all the 49 entries are visible here. Honestly, it took a bit much time to publish the entries, we were not able to wait and few weeks ago we shared a little trick we used in our work: use slicers "without" pivot table.
But now we are happy to see all the great works and ideas together - thanks Chandoo for sharing it, and good luck for all the participants!

You can download our file from Chandoo's site or from this link too.

Here is a screenshot about our work with Leaving view, for California as selected state:


Please tell us if you like it!

Leave a comment






Generate QR code with google chart API using UDF in Excel

posted Jun 4, 2014, 6:21 AM by roberto mensa   [ updated Jun 4, 2014, 8:23 AM ]

It - could - work!
We would like to share a tricky UDF to generate QR codes on your worksheet.
The technique is simple: you can create a new QR picture or update an existing one by using URL_qrCode_SERIES() formula. The picture itself is generated by Google chart API, so your system needs to have internet connection.
The picture will be updated when the formula is calculated, so when you change the value in the cell with link to be encoded (A9 on the below picture) you will immediately see the picture.


Download the file.

Mandatory arguments:
  • Name of the picture is mandatory, if you set a picture name which already exists on the sheet, it will be updated. (Deleted and re-added.) If you choose a new name, the picture will be positioned next to the caller cell.
  • QR values is also mandatory, usually it is an URL address you want to encode to the QR picture.
Optional arguments:
  • Size of the picture (only one measure, because the shape will be rectangle).
  • DisplayText is the return value of the formula. It will be visible in the cell you write the UFD (caller cell).
  • Updateable argument can be set to False if the picture should not be updated when the referred cell values change. It could be useful if you use the tool offline (without internet connection).


Technology:
The base concept of this UDF technique is the fact that UDFs could modify, delete and add shapes to worksheet.
We use Google chart API to generate the QR code itself. Our UDF composes the URL for the API, and use this URL as the first argument of the Shapes.AddPicture method.

You can use our example file, or you can copy the code to your own files.

Option Explicit
'other technical specifications about google chart API:
'https://developers.google.com/chart/infographics/docs/qr_codes

Function URL_QRCode_SERIES( _
    ByVal PictureName As String, _
    ByVal QR_Value As String, _
    Optional ByVal PictureSize As Long = 150, _
    Optional ByVal DisplayText As String = "", _
    Optional ByVal Updateable As Boolean = True) As Variant

Dim oPic As Shape, oRng As Excel.Range
Dim vLeft As Variant, vTop As Variant
Dim sURL As String

Const sRootURL As String = "https://chart.googleapis.com/chart?"
Const sSizeParameter As String = "chs="
Const sTypeChart As String = "cht=qr"
Const sDataParameter As String = "chl="
Const sJoinCHR As String = "&"

If Updateable = False Then
    URL_QRCode_SERIES = "outdated"
    Exit Function
End If

Set oRng = Application.Caller.Offset(, 1)
On Error Resume Next
Set oPic = oRng.Parent.Shapes(PictureName)
If Err Then
    Err.Clear
    vLeft = oRng.Left + 4
    vTop = oRng.Top
Else
    vLeft = oPic.Left
    vTop = oPic.Top
    PictureSize = Int(oPic.Width)
    oPic.Delete
End If
On Error GoTo 0

If Len(QR_Value) = 0 Then
    URL_QRCode_SERIES = CVErr(xlErrValue)
    Exit Function
End If

sURL = sRootURL & _
       sSizeParameter & PictureSize & "x" & PictureSize & sJoinCHR & _
       sTypeChart & sJoinCHR & _
       sDataParameter & UTF8_URL_Encode(VBA.Replace(QR_Value, " ", "+"))

Set oPic = oRng.Parent.Shapes.AddPicture(sURL, True, True, vLeft, vTop, PictureSize, PictureSize)
oPic.Name = PictureName
URL_QRCode_SERIES = DisplayText
End Function


Function UTF8_URL_Encode(ByVal sStr As String)   
    'http://www.nonhostile.com/howto-convert-byte-array-utf8-string-vb6.asp
    Dim i As Long

    Dim a As Long
    Dim res As String
    Dim code As String
   
    res = ""
    For i = 1 To Len(sStr)
        a = AscW(Mid(sStr, i, 1))
        If a < 128 Then
            code = Mid(sStr, i, 1)
        ElseIf ((a > 127) And (a < 2048)) Then
            code = URLEncodeByte(((a \ 64) Or 192))
            code = code & URLEncodeByte(((a And 63) Or 128))
        Else
            code = URLEncodeByte(((a \ 144) Or 234))
            code = code & URLEncodeByte((((a \ 64) And 63) Or 128))
            code = code & URLEncodeByte(((a And 63) Or 128))
        End If
        res = res & code
    Next i
    UTF8_URL_Encode = res
End Function


Private Function URLEncodeByte(val As Integer) As String
    Dim res As String
    res = "%" & Right("0" & Hex(val), 2)
    URLEncodeByte = res
End Function


Read more:

You may also be interested:


Leave a comment



Broken line panel charts aka. bifocal charts in Excel

posted May 23, 2014, 7:57 AM by Krisztina Szabó   [ updated May 23, 2014, 8:18 AM ]


Harold: A visitor is all I ask for. A temporary companion just to help me pass a few short hours in my lonely life.


by The FrankensTeam

How to show small and large number on one chart? It is a common data visualization challenge.
We started to think about line charts with “double focus”; showing both the small and large numbers - similarly as bifocal glasses do.


We created two versions of the charts using the same logic: the bottom part represents small values on the primary axis (on the left-hand side) while the large values are on the secondary axis (on the right-hand side). We do not use broken y axis, both of the axes are scaled from 0, so the charts could be understood as if two charts (one for small numbers, another for large numbers) on top of each other.
Both the two versions are suitable for line or xy scatter charts.

Version 1 - as line chart


On a line chart we use one data series where we down-scaled the large numbers to draw the blue data line, it is plotted on the primary axis.
The visual trick to “break the line” is an additional series forming a horizontal line on the chart. We set it to be transparent, so it will partially cover the data series line. Then we add one more series set to dashed line - this will help to separate the small and large value parts.
The two axes are the key elements of the chart. You will have to determine the scaling for both of the axes regarding the fact that the major unit of the axes’ must be parallel. The upper limit of the small values (100) will be in-line with the 0 value of the secondary axis. (On the secondary axis we do not display the values below 100.)
You have to take a look at the appropriate scale to read the value, or you can add labels to present the real value instead of the series value.

Label trick

Data is represented by one single line plotted on the primary axis. The trick is to use a special column where you convert the values to text string - this will serve as label. You have to set the labels as category label of the primary horizontal axis.

You can download our example file for better understanding.

Version 2 - as xy scatter

This version is a simple solution in case you do not have very big difference between the small and large data. We added one more series with both the small and large numbers in order to make a full picture of the data too. This will be the “upper series” plotted on the secondary axis.
Both of the series use the same range: the original data values. The bottom series is on the primary axis, and it will go out of the plot area. To fade the unnecessary part away, we use gradient color. Also gradient color is used on the upper series to highlight the large values.
On the picture you can see the explanations.


Axis labels

We used an additional data series to create correct labels (date) on the horizontal axis. Error bars (with yellow on the picture) were added to help visually connect data and axis.

If you are interested, you can download our file to learn the tricks.

Leave your comment




Witch of Agnesi curve in Excel

posted May 16, 2014, 7:29 AM by roberto mensa   [ updated May 16, 2014, 7:35 AM ]

Freddy: What knockers
Inga: Oh, thank you, Doctor


by The FrankensTeam

Today (16 May) we celebrate Maria Gaetana Agnesi a great Italian mathematician and philosopher - she was born almost 300 years ago.
In mathematics Witch of Agnesi is a curve earlier studied and constructed by Pierre de Fermat and Guido Grandi. Agnesi in her famous summation treatise Instituzioni analitiche ad uso della gioventù italiana discussed the curve, and, after some language mixing and mistranslation, the curve has become known as the "Witch of Agnesi".

You can see on the google-doodle from google.it how the curve is constructed:


And, certainly, here is the model of the curve in Excel:

https://sites.google.com/site/e90e50fx/home/_draft_post/Versiera.png

Download the Excel file to play with it.

You may also like:

Leave your comment




Spotlights chart in Excel

posted May 15, 2014, 8:50 AM by Krisztina Szabó   [ updated May 15, 2014, 3:02 PM ]

Freddy: Aren't there any lights in this place?
Igor: Two nasty looking switches over here, but I'm not going to be the first.

by The FrankensTeam


IngaKris found a really eye-catching infographic by another Hungarian Krisztina: Krisztina Szűcs, about film production profitability, and she immediately wanted to do it in Excel.
http://szucskrisztina.hu/images/holly.png
Those who followed our latest works know that we built up lots of charts by drawing lines on Excel's xy scatter chart - using this technique, the "spotlight" chart could easily be built up.
Here is the final result with some Mel Brooks film (including Young Frankenstein of course!):



So no, it seems to be, but it's not an area chart!
On the left scale you can see the score of the film (Rotten Tomatoes). The light effect from this "projector" shapes the profit on the right side: the lower point is the budget, the upper is the gross income.
Each of the triangles formed by 50 lines going across the plot area. The base of technique is explained under an earlier post about slopegraphs. All the formula-work is done within named formulas.
On the chart we use separated data series for each of the films, because the transparency-cummulation effect does not work if the crossing lines belong to the same series. Another advantage is the possibility of using different color for the series, and also, the formulas are easier to understand.

Scaling
The tricky thing is in the scaling: on the left side of the chart you can see the Rotten tomatoes score of the film: the value is between 0 and 100. On the right scale two values belong to each film: the bottom value is the budget, the top value is the worldwide gross income. The difference is profit and it appears as the side of the triangle... or the light-spot on the screen.
Score and profit is measured on different scale, so to draw the chart we will need to re-scale the scores according to the profit - that's the reason why we put the scale max values on the sheet and use these (as named cells) in the formulas. The axes of the chart must have these values as maximum.


Named formulas
The named formulas behind the chart are simple. The number of lines used to draw one triangle is set as a constant:
lines = 50
Also we created names score_max and profit_max for the max-value cells.

A range of 50+1 rows and 2 columns...
lines_range = OFFSET(Film!$A$1,,,lines+1,2)
... will help us to build up an array for the x-coordinate values. It must look like:
1; 2
2; 1
1; 2
2; 1
etc. for the back and forth lines between the left and right side of the chart. (more about it here)
The named formula to create this two dimension array is:
x_0 = =IF(MOD(ROW(lines_range),2),{1\2},{2\1})
This named formula could be used for all of the series x values.

Now, for the 1 values (left side) we have to assign the re-scaled score value of the film. For 2 (right side) we have to assign the budget and profit interval, split to 50 steps.
The y value formula of the first film looks like:
y_1 = IF( x_0=1, Film!$C$6/score_max*profit_max, Film!$D$6+(ROW(lines_range)-1)*(Film!$E$6-Film!$D$6)/lines )


Label trick
We added one series for the white dots (movie projectors) plotted on secondary axis. We use the original score values and scaled the axis accordingly. The trick is that we use the label-text column as x values of this series. Excel will interpret the text as value, starting from 1, so the dots will not appear in correct position. The solution will be to set the scale maximum of the secondary x axis to a very large number.
And the labels could be added using the x values of the series!
You can find a detailed, step-by-step description of this label trick in our post about slopegraphs.


If you need more series form more films
If you would like to add a new series (new film) to the chart, you will have to build a name for the y values. The easiest way is to copy the formula of an existing y name, and change the cell references. Than you can add the new series using x_0 and the new y name. For the labels, simply expand the label and the score value ranges.

You can download our chart to learn it.


You may also like and learn:
Stacked column flow chart with Excel
Edward Tufte's Slopegraphs in Excel
How to use rectangular range as chart data

Leave a comment





Stacked column flow chart with Excel

posted May 12, 2014, 8:39 AM by roberto mensa   [ updated May 22, 2014, 6:21 AM ]

Freddy: This is the moment. Well, dear, are you ready?
Inga: Yes, Doctor.
Freddy: Elevate me.
Inga: Now, right here?
Freddy: Yes, yes, raise the platform.
Inga: Oh, the platform, oh that, yeah, yes.


by The FrankensTeam


I had seen a very interesting chart in an Italian daily paper. It shows the distribution of income in Italy in different years. The data is % so basically each year forms a 100% stacked column with small gaps between the parts. But this chart is more than a simple stacked column chart!
First of all, all the columns are sorted, then the parts are connected, forming a stripe-flow. These stripe-flows make clearly visible how the rank position changes year by year. It's similar to a composition of stacked bar and line chart.

I immediately wanted to create the Excel version of it!


You can see in 3 year the "platform is raised", so the stacked columns start from different point. In the original article there was no explanation about the purpose of this "platform raise", so we think this was added just for aestethic reasons. Anyways, we incorporated it into our version:



We downloaded the usage of the top 7 mobile operating systems from 2010 to 2014 to create a chart with more data series.
Here is the "platform raise" version:



...and the 0-baseline version:



In our file the data table could be found on Data sheet, below the table you can set the "platform raise" numbers so, the chart will be updated accordingly:

Source of the data: http://gs.statcounter.com/


How does it work?

The chart is built up to be able to work dynamically with a 8x8 data table, so if you add new years, those will immediately appear on the chart.

You can change the order of the colored stripes by changing the order of the series in the Select Data Source dialogue box. The topmost series in the dialogue box will be in the background, so all the other lines are above it.



The formula technique behind the chart is similar to what we developed for Cosmographs: draw lines on an xy scatter chart. Each colored stripe comes from one data series and built from 100 back and forth lines. All the necessary calculations are on Support sheet: there you can find a huge table full with numbers starting from cell B17. This table contains the data for all the lines for all series. Using OFFSET function in named formula we select which 100 rows build a particular series.
On the chart you can see additional series to draw the white lines which separate the years, and also 3 more series for the category, year and data labels.

This amazing sorted stripe-flow technique is a very powerful tool to visualize elections: It makes easy to follow how the % and the position of each party changes by time. We are looking forward to see the result of the European elections ... maybe someone can use this chart to present the proportions of the main forces?

You can download our Excel file to learn this new chart type.

UPDATES

Version 02

This version is not limited to percentages with max 100% in total. You can use any values in the data table.
You can download our Excel file



You may also be interested:
Life Expectancy by Nathan Yau's chart with Excel
Edward Tufte's Slopegraphs in Excel

Cosmograph in Excel - World migration with bilateral flow chart
Talent traffic chart with chord diagram in Excel


Leave a comment



Use slicers to create interactive, dynamic Excel charts and dashboards

posted May 8, 2014, 1:31 AM by Krisztina Szabó   [ updated May 8, 2014, 6:13 AM by roberto mensa ]

Freddy: Well, we'll see. Good night.
Fraü Blucher: Would the doctor care for a brandy before retiring?
Freddy: No, thank you.
Fraü Blucher: Some warm milk, perhaps?
Freddy: No, thank you very much. No thanks.
Fraü Blucher: Ovaltine?
Freddy: Nothing, thank you. I'm a little tired.
Fraü Blucher: Then I will say good night.
Freddy: Good night.
Fraü Blucher: Good night, darling. Good night, Herr Doktor.
Freddy: Good night, Fraü Blucher.

by The FrankensTeam

We would like to share an easy and simple method how you can improve user interaction - for example in case of complex, interactive charts or dashboards - with the help of slicers! Slicers were introduced by Microsoft in Excel 2010 to make easier and more visible filtering data in pivot tables and pivot charts - but now we will show you a slightly different usage...

The method is similar to the way of working of Excel’s built-in List box form control, but slicers look way much more better! The user will see all the possible choice values on the slicer and can click on one. Then we can use the chosen value for whatever we want. The trick is that we use the pivot table behind the slicer only for capturing the chosen value.

Let’s see step by step:

1) Create a Pivot table using your original database or alternatively you can set up a separated list as base of the pivot table.
2) Add the field you would like to use on the slicer to the row labels of the Pivot table.
3) Insert slicer and link it to the field.
4) Use the pivot table row label record cell as if it was the linked “output” cell of the slicer. You can refer this cell in the formulas feeding your chart.
5) You can move the slicer to other worksheet, also you can size it to be in-line with a data table on your sheet.



Using this way, slicers could be great (and no-VBA) alternative to active cell based or mouse-rollover based dynamic solutions, and also can replace validation lists or form controls. Using the pivot table cell as "output cell" makes easy to apply the technique in existing models.
You can build dashboards or other complex charts, and also you can use the trick with chart types which are not allowed to be used as Pivot Chart (xy scatter, bubble and stock charts).

Here is an example (detail) of a recent dashboard-work created by The FrankensTeam:



Or another example of our Chord diagram - you can also see and compare the two other versions of this chart: months ago we created it with mouse rollover and validation (no VBA) too. We have changed it to work with slicers - it took only a minute. If you are interested, you can read the original post.



Leave a comment




1-10 of 107

Comments

Basic bibliography by r

Basic bibliography by Kris

A-Z