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


Area chart with levels - Excel-Croissant

posted Feb 1, 2015, 11:32 AM by Gábor Madács   [ updated Feb 1, 2015, 11:53 AM by Krisztina Szabó ]


Freddy: [...] Oh, I uh, you know I don't mean to embarrass you, but I'm rather a brilliant surgeon. Perhaps I can help you with that hump.
Igor: What hump?


by The FrankensTeam

The inspiration for this post came from Visualizing MBTA data project by Mike Barry and Brian Card where we found a great interactive chart under chapter How People and Trains Affect Each Other.
Here is a static screenshot about the chart:

http://mbtaviz.github.io/#interaction


Quote from the original study: "The gray bands show the total number of entries into the all stations per minute over time for each day of the week."

Each row represents one day, where the darker grey means more people. This graphic could be a good choice to visualize small and large data together, especially if you need to compare more data series.

To replicate the chart in Excel is very easy. The chart type is area chart with more series - as many as the number of grey belts you want. You simply need to shift the original data by a chosen number called limit (according to the number of grey belts). For example imagine that your original data is scaled from 0 to 2000. Draw the original chart (the lightest-grey) and choose 500 as limit - so you will have 4 belts. Cut the chart to 4 pieces: below 500, from 500 to 1000, from 1000 to 1500 and above 1500. Now put the 4 pieces on top of each other.
This is what you see in the orange frame below:



In Excel you can realize it by shifting the original data down by 0, 500, 1000, 1500 and put the 4 series to the chart. Simply create a data table and minus these numbers from the original data. Create the area chart with 4 series and set the axis limit accordingly: minimum 0 maximum 500.
Note: This way the slope of the area-parts will be correct. If we changed the data above 500 to 500 and below 0 to 0, the slope would not be correct because the lines would start from incorrect point on the horizontal axis.

We created an example using traffic data about the famous Hungarian “Line 1” which is the second oldest underground railway in the world (opened in 1896) and listed as UNESCO World Heritage.

Data (col. C) and chart series (E-H) of Station 8 (Bajza utca - Hősök tere):


and chart of Station 8:
(enlargement of the above orange frame)


As you can see, this kind of chart clearly displays the details for the whole range of values. The color emphasized 'wrapping' allows the easy visual identification of the higher value sections.

The final chart showing all the stations together looks like this:


We simply copied the charts to one sheet, and put on oversized chart with gridlines, legend and horizontal scale to the background.
You can download our file.

Now you are wondering why Croissant is in the title? Gábor named the chart because its layers are similar to this bakery product (in Hungarian we name it “kifli”)

Kris published a tutorial in Hungarian too.
You may also be interested in:

Leave a comment




100 - 101 - 102 …

posted Jan 19, 2015, 3:21 AM by Krisztina Szabó   [ updated Jan 19, 2015, 4:12 AM ]


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.

by The FrankensTeam

We decided to publish a short post when we receive the 100th comment in our Gallery Visitor’s Book. Actually  when I found time to write, we already have 102 comments. :-)

Freddy: Reputation. Reputation.

Looking back to the history of the Gallery, we had 40k visitors - well, we are not Louvre, but we are happy with the results. All the comments - and the lot others we received in e-mail - made our days and proved that what we do is interesting, useful and thought-provoking for others.

Unfortunately in the past months we did not have time to work on new charts and new posts…


Inga: Dr. Fronkonsteen, are you all right?

Yes, we are all right, and we still have some ideas. So let’s hope soon we will return to the Excel-life, because we have some great projects in the “Under construction” folder. :-)

At the end of this short note, let me quote from a nice mail - about our team:

“And I say to myself, <<Of course they are.  They are from Budapest & Milan.  How could they do any less?>>"

Villager: He's a Frankenstein, and they're all the same. It's in their blood, they can't help it. All those scientists, they're all alike. [...]

Leave a comment




How to copy charts based on named formulas to other workbook?

posted Sep 23, 2014, 9:17 AM by Krisztina Szabó   [ updated Sep 24, 2014, 3:57 AM ]

Freddy: Mr. Hilltop, will you hop up on your feet and stand beside this table. Nice hopping. Mr. Hilltop [...]

by The FrankensTeam

As an answer to a frequently asked question, we would like to share a tutorial about a simple method to copy a chart based on named formulas to your own file and use it according to your needs.
Our charts in E90E50charts gallery are usually built on named formulas. This way we do not use sheet cells to store the calculated background data, so the sheet is clear: the reader only needs to change the base data and labels in the downloadable example file and the chart is automatically updated. Unfortunately the example file is not suitable for those who would like to use the chart in an existing workbook. In this case the simple copy and paste the chart method does not work since the named formulas will not be created in the other file. Because of the complexity manual creation and re-linking the names is usually irksome and time consuming.

So how should you copy the chart? The trick is to copy the whole sheet and replace the link under the Data menu. Here is how to do it step-by-step using the matrix bubble chart as example.


1. Open the downloaded example file.

In the Name Manager (Ctrl+F3) you will see there are some named formulas of which rng is the dynamic named range of the data table.


2. Open your file where you would like to use the chart.

In this tutorial the file we use is My_workbook.xlsx. (It is important you have to use an existing file, alternatively you have to save the file before going to step 4.)

3. Move the sheet with chart to your workbook.

In the example file right-click on the sheet tab and choose Move or Copy



In To book select your workbook and check the Create a copy box.


Now the sheet is in your file, and the named formulas are created accordingly - you can check it in the Name Manager.

The problem is that the chart series are linked to the original file, as you can see in the chart series formula:



4. Change the data connection link source to your file

Under Data menu / Connections group / Edit Links click on Change Source and choose your own file from the Change Source dialogue box. (in the example it is My_workbook.xlsx)




You will see as if nothing happened on the Edit links pane, but now the chart series references are correct, showing the name of your file:



After you Save, Close and re-open your file, the link to the original file will disappear from the Edit links pane too.
Now you can use the chart in your workbook without any problem, and you can change the reference of the data table.

Some examples where the trick could be useful:



Leave a comment




Convert numbers to words with Excel formula - language collection

posted Aug 18, 2014, 8:43 AM by roberto mensa   [ updated Aug 27, 2014, 4:15 PM by Krisztina Szabó ]

INT. - AMERICAN TRAIN
Wife: Harry, he was at it again.
Harry: So what do you want me to do about it?
Wife: Everyday.
Harry: Let him, let him.
Conductor: New York next, everbody out for New York.

INT. - EUROPEAN TRAIN

Wife: Hans, er dachte schon wieder.
Hans: So?
Wife: Aber jaden tag.
Hans: Lass ihm, lass ihm.
Conductor: Transylvania nachste. Jeder austeigen fur Transylvania.


by The FrankensTeam

From time to time the question of converting number to words raises on Excel help forums. If you google for the topic, you can easily find VBA solutions, but the problem could also be solved using Excel’s built-in formulas.
Years ago in the Excel Hero LinkedIn group a challenge was opened by Daniel Ferry to create a formula for the English language conversion. The discussion and the “formula development process” was indeed interesting, and with the help of lots of great minds and strict eyes, we managed to create a relatively short formula.
Recently, for another forum request, Igo-r put together the Italian language formula (plus UDF solution too), and just for curiosity, IngaKris created it for Hungarian language.

Do you have formulas for other languages? French? German? Please slip your formula through slot in door, so we can publish it here!
If not - please take a look at our files! Maybe you will be inspired to develop formulas for your language!

Here are the files for download - please note there are some defined names (2 or 3) in the files. If you would like to use the formulas in your own workbook, please do not forget to copy the names!

Italian
English
Hungarian   (Hungarian language post on IngaKris's blog.)
Russian   - we received the russian versions from Ivan Bondarenko. He created 3 version of the formulas: for masculine gender, for feminine gender and for currency. Thank you, Ivan!

Ps: In Italian, for reverse conversion (undici --> 11) there is a VBA solution using regular expressions created by Igo-r too.

You may also be interested in:
Month and day names in all languages with Excel formula

Leave a comment



Trendline formatting bug in Excel 2013 and the secret of The FrankensTeam

posted Jul 15, 2014, 3:15 PM by Gábor Madács   [ updated Jul 16, 2014, 6:23 AM by Krisztina Szabó ]

Freddy: So then, what we're aiming for is a being approximately seven feet in height, with all features aither congenitally or artificially proportionate in size.
Igor: Something like this.
Freddy: Hello. You've caught something there. Crude yes, primitive yes, perhaps even grotesque. Yet something tells me that this might be our man.

by The FrankensTeam


There are two reasons to write this post. First of all, to share a good news we are really proud of: our dashboard won the Chandoo data visualization contest! We would like to say thank you to all those who voted - either to our or to the other 11 brilliant works! Take some time to analyze them - there is a lot to learn from the masterpieces.

Second…

The secret - Teamwork!

The beginning of the story was that IngaKris had seen a post somewhere suggesting to create a bubble+line chart combo starting from a normal line chart and adjusting the marker size manually. Because this way the result is totally static, she started to think if it was possible in a dynamic way. Bubbles could be done dynamically only on a bubble chart, so this must be the base chart. But bubble chart could not be combined with any other chart types, so we could only use the elements of the bubble chart itself. Scrolling over the “Add chart element” option, the trendline seemed to be suitable.
Unfortunately - by definition -  the trendline will never connect the data points… unless you have only two data points. So IngaKris added as many 2-point series as the number of values to the chart and added moving average trendline to all the 2-point series. Set the color of the trendlines to the same, format the line according to your needs. The result is more dynamic as the originally suggested one, although it needs lot of manual work, especially if you have several data points.
But it is something, so she sent a picture about it to Igo-r, as a proof that it is possible, asking how it was done.
And then - as usual :) - Igo-r created a way much more clever and easier solution, which could be used with dynamic named ranges too. That’s what you can read here.
IngaKris was a bit sad because she stopped thinking after finding the half-dynamic solution, but… that’s how we work: if one of us discovers something, or finds something new or (seemingly) impossible, he or she will immediately share it with the other. This excited and challenging environment and the team-effort turns a good idea to be something better, to be the best.
But this is not the end of the trendline story.
When IngaKris re-opened her first file with several same-colored trend-lines, she realized that the color formatting was somehow lost...

Trendline formatting bug in Excel 2013

To reproduce the problem, let's see step by step what you should do:
  1. Create a chart with two data series, using the default colors. Can be any chart type on which you can use trendline.
  2. Add trendline (any type) to both of the data series.
  3. Change the color of the second trendline to the same as the trendline of the first series.
You have to see something like:


4) Save the file, close and re-open in Excel 2013. You will see the color changed:


As we see, one can not set the color of the second trendline to be the default color of the first series. The strangest thing came up when we opened the same file in Excel 2010 (hungarian version) - the trendlines has the color as it was set!


Find more FrankensTeam works in our gallery with >150 Excel charts.

Leave a comment




Combine Bubble and xy scatter line chart

posted Jul 8, 2014, 8:39 AM by Krisztina Szabó   [ updated Aug 26, 2014, 2:35 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:
In the next post we shared the story of this discovery and a bug we discovered about trendlines. Please read it!


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 Jul 16, 2014, 6:21 AM ]




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:


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


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.

Take a look at our gallery with >150 Excel charts.

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



1-10 of 112

Comments