Power Query: Gt & Transform in Excel Following the January 2018 Update

I am hosting four videos here for my Excel blog. Please watch them if you are using Power Query aka Get & Transform in Office365 ... Excel 2016.

Four videos that discuss the four main changes to G&T that were just announced.

Your feedback will be highly valued and I look forward to sharing it.

Hop over to my Excel Blog to download the Excel file

Video 1 Bucket/Bin Ranges

Video 2 Positive/Negative Association

Video 3 Positive/Negative Association Explained in Full

Video 4 Creating Associated Lists

DW
31st January 2018

Nobel Again

You have seen my long post on this subject, now read the shorter version in the Todmorden News ... Tod News

DW
25th January 2018

You are only fooling yourself

The other day I was travelling and saw something that I found disheartening.

A very large man was travelling with someone else and they were sitting together and chatting. Then this morbidly obese man left his friend and came and sat next to me. He carried a bag with him that he opened and started to take food out of: greasy burgers and chips with lots of coleslaw. He ate this food as if he had hardly eaten for weeks. After he had gorged himself he closed his bag and clearly wanted to clean his hands before he returned to his friend. So he wandered to the toilet to get clean and then went back to sit next to his friend.

I think I felt sorry for this man: clearly he has an eating disorder of some sort that includes hiding his consumption from his friend. I hope he can find some peace that will allow him to stop this long, slow suicide that he's in the middle of.

DW
22 Jan 2018

Analysis of Nobel Prizes

The place of Todmorden in the Annals of the Nobel Prize! as at the end of December 2017

Introduction

My home town is Todmorden in West Yorkshire, England and throughout all of my childhood we were proud to say that Todmordian John Douglas Cockcroft had won the Nobel Prize for Physics in 1951: it was a joint award and he won it with Ernest Walton of Dungarvan, Ireland. We are told they were the first to have split the atom!

In 1973 another Todmordian, Geoffrey Wilkinson won the Chemistry prize along with Ernst Otto Fischer for their work on sandwich compounds: they were working independently of one another, it seems.

This put us in the stratosphere: which other town or city on the planet could boast TWO Nobel Prize Winners? Moreover, in spite of the 24 year age gap between them, Cockcroft and Wilkinson shared the same science teacher at Todmorden Grammar School.

This article sets out to answer a series of questions I have never seen answered before which includes, is Todmorden unique in respect of it Nobel Prize achievements? Is Todmorden at the top of any Nobel list? Has any other town or city produced more than two Nobel Prizes. Has any town of the size of Todmorden or less produced two, or more, Nobel Prize winners? … all low level stuff but I could not find anywhere THE source that would tell me everything I wanted to know.

Yes, the Official Web Site of the Nobel Prize, https://www.nobelprize.org, contains a massive amount of detail but it didn’t tell me, for example, if Todmorden is the smallest town to produce two Laureates and so on.

My File

I have produced an Excel file that contains all of the basic details of every Nobel Prize there has been: from 1901 to 2017. My list includes every Laureate, date of the award, dates of birth, dates of death, male or female and more.

Since I was trying to make Todmorden the centre of attention, I had said in a Todmorden based Facebook forum last week that I felt it is probably unique in producing two Laureates relative to its population size! I had long since given up the notion that Todmorden was top of the pile in all respects, of course; because we must expect the University cities of Oxford, Cambridge (UK and USA), Paris, Bonn … to outshine little old Todmorden!

By the way, Todmorden is not a University town so Cockcroft and Wilkinson belong to us by rght of birth: they are Tod Lads!

I went to various sources to find the populations of the cities for which there are two Nobel Laureates. I did not look for the populations of cities that had 1 or 3 or 4 or more Laureates since Todmorden is not competing with them by my reckoning!

You can download my file from the link at the end of this article (ths link will appear in the final version of this article) and for Excel warriors, you will find that I have used a wide range of techniques in there that includes various functions and formulas as well as Get & Transform/Power Query, including some programming in M.

How Many Prizes?

 Todmorden has claimed two prizes: how many other towns and cities have claimed the same or more or less? Examples














New York is top of the pile by a long way, London is third and as you can see, Todmorden is there with two Laureates. Overall, the number of cities by number of prizes is as follows:













Todmorden, then, is one of 44 cities around the world to have claimed two Nobel Prizes: one of just 91 cities to have claimed more than one Nobel Prize.

Ages of Laureates

John Cockcroft was 54 years old when he won his prize and Geoffrey Wilkinson was 52 years old. For a Physics Laureate, Cockcroft was almost two years younger than the average and for a Chemistry Laureate, Wilkinson was over six yearsyounger than the average.



By the way, the first Nobel Prizes were awarded in 1901, a relatively modern institution; but take a look at the dates of birth of the first Laureates, from 1901 and 1902:



The second ever Nobel Literature Laurate, Theodor Mommsen, was born on 30th November 1817: there was no other Laureate born before Theodor Mommsen!

By the same token, the first 20th century born Laureate did not appear until Frederic Joliot-Curie was awarded the Chemistry Prize in 1935. Frederic was born on 19th March 1900 and was part of the famous Curie family! For interest, here is the Curie family Nobel history:

 

To date, the youngest ever Laureate is Malala Yousafzai of Pakistan who was a mere 17 years old when she won the 2014 Peace Prize: Malala was born 180 years after Theodor Mommsen! Until then, the youngest ever Laureate had been the 25 year old Australian William Lawrence Bragg who won the Physics Prize in 1915. The oldest prize winner to date is Russian born Leonid Hurwicz, who was 90 years old when he was awarded the 2007 Economics Prize.

Sex of Prize Winners

Up until the end of 2017, women have won just 49 Nobel Prizes, men have scooped the other 847: almost 30 prizes have been awarded to Organisations, so they are gender neutral!

Marie Curie’s award in 1903 was the first by a woman and in the first 20 years in which the Prizes were awarded, only four went to women. In the 20 years to the end of 2017, women have been awarded 21 Prizes.

I will attempt no answer to suggest why more women have not earned Nobel Prizes and I have to say that over the last 20 or so years, women have been actively lobbying for more women Laureates. Since they clearly cannot create scientific achievement out of thin air, the majority of prizes for women have been the Peace and Literature Prizes with a further 12, Physiology or Medicine, Prizes having been awarded to women!



Please note, the relatively small number of Prizes for Economics reflects the fact that the first Economics Prizes were not awarded until 1969. Moreover, the title of this Prize is, in full: The Sveriges Riksbank Prize in Economic Sciences in Memory of Alfred Nobel.

Populations and the Final Answer

For me, this is the big question: person for person, is a Todmorden Nobel Prize worth more than anywhere else on the planet? In other words, is Todmorden the smallest town in the world to have been awarded two Nobel Prizes?

Please note, I have not gone back to the dates of the award of the Prizes to find the populations of the cities concerned: in some cases that would be impossible anyway. What I have done is to take the latest figure I can find on the internet as at 8th January 2018 for all of the cities of interest.

Well, a two Prize city has a mean population of over 960,000 and a median of almost 388,000 inhabitants. Yay! Tiny Todmorden has around 15,500 inhabitants. The largest two Prize city is Mexico City with 8.9 million people, Halifax, 12 miles from Todmorden also has two Prize winners and has a population of 88,000 or thereabouts.

So, the smallest two Prize city is … Rendcombe in Gloucestershire with a population of just 354 people. More than that, their two Prizes came from one man, Frederick Sanger who won the Chemistry Prize outright in 1958 and outright again in 1980. Hats off to Frederick!

Conclusions

There you are: good news, bad news! Todmorden is in the elite Nobel Prize World by having two Nobel Prize winners in its history. It is the smallest town to have two Prize winners. Alas, it is not the smallest village! Let’s give due credit to Frederick Sanger and Rendcombe (or Rendcomb) for not only winning two Prizes but winning them both outright: a very rare achievement.

Sanger helped to answer another of the questions that was raised on the Facebook page I mentioned earlier and that is: is Todmorden Grammar the only school to have produced two Nobel Prizes? Again, no! However, let’s find a winning angle for Todmorden: Sanger won two prizes but he is only one Laureate, albeit Laureate and bar! So, for having two Laureates from the same school, Tod is Tops!

E&OE

Finally, this article took me several days both to research and for me to carry out the analysis in my Excel file: I humbly apologise if there are any errors here and if you do find something amiss, please let me know and I will correct it! I have made very few changes to the basic data but I have had to make a very small number of minor tweaks to place names/locations to prevent misrepresentation.

Acknowledgements I am grateful to the Nobel Prize organisation for making their database freely available and for the various sites that provided me with population and other data: in that regard, mainly www.wikipedia.com

Duncan Williamson
9th January 2018

Excel file for download: the link will appear here in the final version of this article

Gardening

I have loved having a garden for as long as I can remember and I first owned my own garden in 1983. It was tiny and I went from complete tripe to overwhelmed by one kind of flower to mediocre. My garden in Malawi was fantastic: real credit to our garden boy and, of course, my estate management skills. Ahem! I am taking back control of my garden here, now. We inherited some trees and bushes and they are still here. I laid a lawn front and sides of the house and whilst they don't sell what I call grass here, it looks like a lawn and I mow it from time to time to keep it neat. I love kitchen gardens and have now taken control of that aspect too. Mrs W is prone to planting a hundred seeds of one plant that then grows and overwhelms us with a harvest we cannot appreciate: she gets things to grow, at least. I am taking a more measured approach now and have planted a lot fewer seeds and bulbs but will plant again in a few weeks' time to get a proper flow of plants going. Let's see how it all works out. I will report back from time to time. DW

Love Actually ... well, no, actually

I bought the DVD of Love Actually at least twice and I watch it just about every year. So, it's time to watch it again. Except, of course, Windows 10 in all its fantastic glory will not allow me to play it. Who knows what the problem is? Genuine DVD. Played several times already. Not scratched or damaged in any way. Very frustrating DW

Trip to Khao Kho

We live in a rice farming area which is, not surprisingly, very flat. No hills, hardly a slope in sight. So far a weekend away I insisted that we went somewhere hilly. Goodness, did we find some hills. And so VERY steep roads! We can to Khao Kho in central Thailand. It's a very nice area with lots of twisty roads and some scary hill climbs. My legs were rewarded with the resistance I was looking for and there are some interesting things to do here and interesting things to see. By the way, even on the top of a couple of hills, on sloping ground, we saw rice being grown! DW

The Pushchair NOT the Baby

Waiting to check in for my flight from Istanbul to Doha when a man carrying a very young baby walked in front of me on his way to another counter. A short conversation that I could not hear took place, after which the check in clerk laughed heartily as she said for all the world to hear, No, not to check in the baby, check in the baby stroller!! The man went past me again as he took his baby away. He came back within a few minutes minus baby, plus pushchair! Some entertainment at least.

Scooby Doo

It's hardly the most important thing I ever thought about but I could not abide that television programme, Scooby Doo. It really got on my nerves. In my inbox today I received my usual OED word of the day message to find the word Scooby ... here is the entry: scooby, n. [‘not to have a scooby: = not to have a clue at clue n. 2e.’] Pronunciation: Brit. /ˈskuːbi/, U.S. /ˈskubi/, Scottish /ˈskubɪ/ Forms: 19– scoobie, 19– scooby. Origin: Formed within English, by clipping or shortening. Etymon: proper name Scooby. Etymology:Short for Scooby Doo, the name of a cartoon dog which features in several U.S. television series and films (which typically include the name of the dog in the title), as rhyming slang for clue n. The fuller form scooby doo is also sometimes found. colloq. (chiefly Sc.). not to have a scooby: = not to have a clue at clue n. 2e. 1993 Herald (Glasgow) 14 May 16 Your lawyer telling youse that he husnae a scooby and youse can jist take a wee tirravie tae yersel. 1999 C. Dolan Ascension Day(2000) vi. 120 Mum, trying to be businesslike, quizzed Morag about blood counts and bone marrow suppression and other such matters about which of course she didn't have a scooby. 2006 Daily Record (Glasgow)(Nexis) 12 May 33 Isn't research meant to ask questions we haven't a scoobie about? May the saints preserve us!! DW

RIP Liam Coughlan

I saw a question on quora.com today that piqued my interest so I answered it: it concerned the British view of Irish people. Since I have worked and admired several Irish people over the years, I responded to say so. Following on from there I thought I would try to find the Irishmen I worked with by way of a google search and was saddened by what I found. I read a story online about my old Irish friend Liam Coughlan. We met and worked together in Yeravan then Tblisi and then as I was posted to Bishkek, Liam was posted to Tashkent: we both still worked for the same organisation in the same project but we were countries apart. We met briefly in Croatia when Liam invited me to run a one weekcourse for him there a while after we had finished our Central Asian gig. We lost touch after that! Liam was one of the smartest men I have ever known: highly qualified in his field; holding high level positions in a variety of organisations. Liam was generous, friendly, open, talkative, informative, supportive, helpful, down to earth, very well read on Ireland and Irish history and politics. Liam was also politically very astute. Liam also had a fantastic sense of humour and any time spent with him was bound to entail his endless blarney and endless anecdotes. He knew or had met countless people: honest johns and downright rogues and he had stories about them all. Liam was educated by the Christian Brothers in Ireland and what they did to him and to others is beyond the pale. Stories of brutality, sexual activity, paedophilia and possibly murder seemed to haunt every waking hour of anyone who was in the throes of anything to do with that outfit. I was transfixed by his stories but never doubted them. Strories in newspapers, books and online match what Liam told me. He regaled me with stories of when he stood for Parliament in Ireland and how he realised how stupid he might have been to try. He was certainly clever and honest enough but maybe a little idealistic. In any case, at or around that time he gave a lift on the back of his motorbike to Charles Haughey, now the late and unlamented Taoiseach whom Liam branded a cheat, liar, thieving arse. At the time I knew Liam I reviewed every book I read on my web site and he not only read my reviews but he commented on them: normally constructive and supportive … apart from the book I reviewed on Haughey. Let me confess that Liam lent me the book and I didn’t read all of it so my review was a little short of proper insight. I got an email from Liam setting me straight and I never admitted my shortcomings but I published Liam’s correction without hesitation! Of the two of us, I was the qualified teacher but Liam was by far the better educator. Because of his intelligence and diligence, he took subjects apart and rebuilt them. He had a learner’s insight and a teacher’s gift and his students were definitely the better for it. He had two women ACCA students in Tblisi and they both sailed through every exam because of Liam and they became qualified accountants in double quick time. If ever a beggar or a hawker came anywhere near Liam or the people he was with, he would be the first or the only one to buy what they were selling or to give them something to eat or just to hand over a few coins or notes. I have known no one else like that. In terms of the question on quora.com, Liam was very clear about that: he liked and respected British people and if anyone tried to say there was hatred between the two nations, he would easily strip out the rumour and gossip and explain who the haters were, where they were, what they wanted and how few of them there were! He could have been an ambassador for Ireland. The story I read about Liam online this morning said that he had died from natural causes in Austria, as testified by the Austrian police: aged 51. I knew his partner had had a baby shortly after we went our separate ways but it seems there was another one after that. The crux of the story I read today concerned the woman who passed herself off as Liam’s first wife who claimed no knowledge of the second wife. Let it be known, I knew about both women and I met the second one several times as she was in Tblisi and elsewhere with him. I never met the first wife but I heard a lot about her and their son. Liam never hid from his responsibilites and I imagine he was a fantastic father but he has died tragiccally very young and I wish his children well. One of the last times we met face to face was in Ireland. At the end of our Central Asian work I said I was looking for somewhere to go on holiday and he suggested Ireland so that’s where I went. From South Wales to Waterford by car ferry and then a week doing a grand tour of Ireland, ending up in Belfast. Liam met me in Waterford and we had a jar or two of Porter there. Grand craic was had during that week and Ireland is a place well worth visiting: Waterford to Cobh, Limerick, Kerry, Knock (Liam had a story about that, too!), Galway and across to Belfast. Excellent drivers in Ireland I have to say: very corteous. I noted the speed signs on the roads as I went from the ferry to the hotel where I met Liam and I asked him, are those signs in miles per hour or kilometers …he replied, you decide! Typical Irish, typical Liam. I am sorry I lost touch but these things happen and I am very sorry to hear that he has died: by the way, no surprise as he smoked like a chimney and eschewed just about every form of exercise known to man! I shoud say, I don’t know what killed him but he did smoke a lot and I know he was treated from time to time for possible skin cancer given the type of skin he had. Nevertheless, ave atque vale Liam. It really was a pleasure and a privilege to have known you. Duncan October 2017

Recipe time: veggie sandwich

Lightly fry the following: Medium sized onion, sliced Clove of garlic crushed and chopped Stick of celery chopped Button mushrooms sliced Fry the onions and garlic for a minute Add the celery for two minutes Add the mushrooms with some ground black pepper, a pinch of salt and 6 or 7 splashes of Worcestershire sauce and keep cooking for another two minutes Meanwhile Toast two slices of your favourite bread and after they have cooled for two minutes spread hummus on both sides of each slice Pile half of your onion mix onto the first slice of bread. Put the second slice I top of that. Now pile the rest of the onion mix on top of the second slice Serve with a salad of your choice if you wish but I couldn't wait! I make my own hummus but shop bought is normally just as good. DW 11th September 2017

American Food

I worked with Mrs W over the last few days to write an introductory piee on American Food: here it is! The USA is the richest country in the world and it has the biggest, most expensive and often the best of everything. There are about 300 million people living in the USA and apart from Native American Indans, they have come from all parts of the world. As these people arrived from Europe, Africa, Asia, South America and elsewhere, they brought their ideas, cultures and cuisines with them. We know about Little Italy and Chinatown in New York, there is the Latin Quarter in New Orleans. All over the country, the food comes from here, there and everywhere. • Jerky • Dutch Baby Pancake • Jersey Breakfast • Shrimp and Grits • Hoppel Poppel • Scrapple • Tex Mex And a lot more A lot of these imported dishes are good quality, nutritious dishes that everyone can eat but there is downside to food from the USA: fast food. Since the 1940s the USA has been the leader in creating food and restaurants that cater to speed and greed. People who either cannot cook or who cannot be bothered to cook. Food portions that can be carried away in buckets, not just on plates. Beefburgers made to be the size of a man’s head. Deep fried food. Food with lots of fat and sugar and carbohydrates. Burgers, fried chicken and pizzas. At least two generations of Americans have been brought up on fast food and the result is that more than half of all Americans are not just overweight, not just fat but they are obese … very fat. No surprise when a single meal at MacDonald’s can contain 1,600 calories and many grammes of fat as well. As countries develop and get richer, the more its people get fatter: it’s time to stop and think. We admire the development in the USA but we do not need its fast food and its obesity. DW 26th August 2017

Leek and Potato Soup

Last night I made the best leek and potato soup I have ever made and the secret is the potato! Serves Two Ingredients small onion, sliced one medium sized leek, washed and sliced small potato, washed and chopped ... peeling is optional, I did not peel it half a vegetable stock cube or add real stock or water ... about 15 fluid ounces/400 millilitres dessertspoon of vegetable oil Method Heat the oil in a pan ...medium heat ... and add the onions ... sweat them for a minute Add the leek ... sweat them for a minute Add the potato and continue to sweat all of the veg for another two minutes Add the stock cube and stir it in Now add the liquid/stock Bring to the boil Simmer until the potatoes are soft ... maximum 10 minutes should be enough At this point, take the soup off the heat, pour it into a blender and blend it thoroughly If it is too thick for you, simply add more water or stock It is ready now. Add any adornments you like such as a sprig of parsley or coriander leaves ... as you wish The potato secret? Don't add too much otherwise it dominates the taste DW

The Cost of Light!

Flick a switch and the light comes on. Have you ever thought about the cost of light throughout history? I found a table of the cost of lighting per million lumen hours in the UK in British Pounds for the period 1301 to 2006. In 1301 The estimated cost of lighting for one million lumen hours was £33,042.9 whereas in 2006, the cost was ƒ2.89 per one million lumen hours. Oddly, the cost in 1301 had rocketed to £40,820.58. Take a look for yourself at this page from DER SPIEGEL/Statista, where you can read: "One hour of light (referred to as the quantity of light shed by a 100 watt bulb in one hour) cost 3,200 times as much in 1800 in England as it does today, amounting to 130 Euros back then (or a little more than 150 dollars). In 1900, it still cost 4 euros (close to 5 dollars). In the year 2000, we arrived at a cost of 4 euro cents (5 US cents)." The following chart shows how much work we have to do to be able to switch the lights on: one second now, 400 hours in 1750 BC! You will find more statistics at Statista Fascinating stuff! DW

Air Malawi: welcome back

I last flew on an Air Malawi aeroplane in the middle of 1993 as I finished a five year posting to the University of Malawi in Blantyre. They had bought two new planes shortly before I left the country but I flew on neither of them. I flew on the BAC111 and the turbo prop plane that used to frighten me to death as it bounced around the skies! Today I will fly from Lusaka in Zambia to Dar es Salaam in Tanzania via Lilongwe in Malawi on Malawian Airlines. I am looking forward to seeing how Malawi Airlines has changed things in the last 23 years and I can see from the web that they fly some smart planes now. My plane today will be the Dash 8 Q400, for both legs of the journey ... Dash 8 Q400 And here, a Boeing 737-700: Boeing 737-700 I have seen a Boeing 747 in Air Malawi livery but I don't see that plane on their web site now where they say they have just the two planes you see above. See you there and muli bwanji in advance! DW

Welcome to this Blog: Excel with ExcelMaster

 

Visitors to this Blog come from all over the world and all are equally welcome. I abhor what has happened to George Floyd and I stand in solidarity behind the movement for greater peace and equality

27th October 2020: Logit regression is the order of the day today. If you are using 0 and 1 style dummy variables in a regression model then you need to read my page on this subject and then experiment with the four examples in my Excel file, also downloadable.

5th October 2020: As is often the way with this blog, there are no posts for a while and then three posts come along in three days. This time, I am sharing my PowerPoint Slide Deck that shows how to get Excel materials into a PowerPoint Presentation. There are probably a few more ways than you thought possible.

4th October 2020: I have significantly updated my page on Gantt Charts. This makes the page easier to read and it demonstrates the use of Microsoft 365 and not a much older version of Excel. There is an Excel file to download with the page, too.

3rd October 2020: Covid-19 has brought out the idea of exponential growth and yet the term is being abused. I have created a page called Exponential that illustrates some of the Covid-19 data and contrasted that with an Exponential modelling template. There is a video to go with this page and an Excel file. 

22nd August 2020: Dates in Excel can be a right royal PITA, can't they. Here is a sorry tale with a happy ending. Dates downloaded that were formatted either as General or Date ... both formats in the same column. Read here how to solve such a problem.

31st July 2020: Today I did something really interesting with Data Tables/Dynamic Array Functions. I was answering  question about compound interest and then decided to answer it by using a Data Table and then I used the new SEQUENCE() function, a dynamic Array Function, too. That gave me three different ways of answering the question. 

I tried an experiment and bulk copied some of my work from Quora ... bad idea as they all copied over as pages and had no title ... not helpful.

16th June 2020: There is no page on this blog for this, just follow this link for a fantastic Excel resource. There are links that are good for Excel and links that point out weaknesses and dangers of Excel. As far as I know, all links are free of charge and be ready, some of the links are dead now.

5th June 2020: as promised, I have uploaded some specimen materials to my course outline page for my online courses. See the link in the 30th May entry ... my slides and my working Excel file. More to follow soon.

30th May 2020: I have been busy over the past month running online classes for a client and they have gone very well. The course was an Introduction to Financial Modelling and was a comprehensive guide to many of the tools available in Excel as well as reviews of modelling best practice and many other insights along the way. Here is my course outline and invitation to attend my other offerings online. I will be sharing some of my course materials here over the next few week and that will help you to make your decision to attend or miss out!

27th April 2020: the RANDARRY() is a huge leap forward for random number generation in Excel. I have created an extensive review of how to use the function here.

26th April 2020: The GESTEP function ... you use it every day, right? Me neither. I had never heard of it until an hour ago, so I created an example to show what it is and how it can work. Go to my page here.

22nd March 2020: Along with many other Excel bloggers, I am spending a lot of time on making Excel the go to software to gather, store and communicate the covid-19 data. I have done something that no one else is doing, again. I am demonstrating how to turn text into tables, pivot tables and pivot charts. You will hardly believe it's possible but you can see what I did and then download my Excel file and do it yourself. This will take you time to learn and set up but it takes just a minute a day to update. Here is my page on Data From Text

17th March 2020: If you are Irish, have as happy a St Paddy's Day as you can! Otherwise, two pages on the Append function in Power Query. Brilliant pages for everyone with page two being relatively advanced.

16th March 2020: Three new pages for you: How MIRR really works, Z Charts and COVID-19 and Long mathematical equations in Excel

7th March: This is a treat for anyone who is using Power Query and needs some relatively advanced insights into using them to create Pivot Tables and Pivot Charts, when using Connection Only Queries. There is neither video nor Excel file but the page is so good and has such good graphics that the keen reader will not mind that they are not here! Go to this page ... Pivot Table for a Connection Only Query?

6th March: [Data.Format.Error]We couldn't convert to number ... If you ever receive this error message when creating a Query in Excel or Power BI, you might find there is not enough help on the internet to help you ... until now! Read this page for the solution.

5th March 2020: this is a moderately interesting problem you might come across. See the page entitled Percentages Disappeared from Power Query to see what can happen when Excel chooses the wrong Data Type for you. It also shows you how to correct that mistake. 

3rd March 2020: this is epic! Sorting a Pivot Table by TWO columns. It cannot be done ... until you find Helgi and www.stackoverflow.com and she helps you to achieve the impossible. Read on!

7th February 2020: Here is a fascinating article: fascinating for two reasons. Reason one is that I have used some of the latest new Excel functions, RANDARRAY() AND XLOOKUP(). Reason two is that I caught someone out in a porky pie by means of programming a spradsheet. Go and have a look at Infinite Monkey or Porky Pie. You will also learn about the infinite monkey theorem while you are there!

23rd January 2020: I have previously written three pages on Sparklines and here is a fourth: it's fascinating and so easy to do. However, if Sparklines are new to you, do note the links to the other three pages at the bottom of the page of this latest post. Have fun with Sparklines! Sparklines with the Camera Tool.

16th January 2020: I started this blog in 2011 and since then, this is where you all come from ... a map showing where my blog is viewed from and I have highlighted the top ten countries. Looks like I cover the world!

This is where YOU are!

15th January 2020: You know that I am a regular contributor to www.quora.com. A couple of weeks ago, I was invited to open and use what they call a Space: it's essentially a blog inside Quora. You will see that I put things there that could easily go here. So, do yourself a favour by following this link and signing up with me there as well as here ... meaning you will miss nothing!

5th January 2020: Happy new year to everyone, first of all! Here is something you might not have seen yet. If you take a look at the Review Ribbon in your version of Excel you might see something new: in the Proofing section on the left of the ribbon there is something called Workbook Statistics ... click on it and see what it tells you about the file you are currently in. Is it useful? Will you use it? Who will use it? Also today, I have added a new page, on the very important topic of Accessibility in Excel: that page is here

12th December 2019: A video ... a video and a file to download. How to use a checklist to solve the problem of dirty data. It's a smaller problem than I have dealt with before, so we did not need to use Power Query. Still, solving these problems is a skills that we all need. The file is here where the video and Excel file are waiting for you!

6th December 2019: Today's contribution to this blog is a page I have written on the XOR function. This is something of an odd function since I don't think I have seen it in action. Never mind that, it is not difficult to use and understand and there are clearly many uses for it. As a bonus, I have included the ISODD() and ISEVEN() functions in this article and, hot on the heels of yeseterday's post on IF, IFS and CHOOSE, this is a totally topical page! You can find the page here and there is an Excel file to download, too.

5th December 2019: If you have ever wondered about the use of Nested IF, IFS and CHOOSE, look no further because I just created a page for you. Two of the examples are easy and the third example is much more of a challenge. Go here to see the page https://excelmaster.co/nested-if-ifs-and-choose/

5th November 2019 For the benefit of all Brits ... have a happy and safe Bonfire Night. Secondly, look out for my NEW page, Quora Questions. Just scroll down from here and you will see the heading, just click it to open and read what I am doing. Simple concept with some very useful ideas.

26th September 2019: I have created this page to show how I went from an image on a web page, via OCR software, to a relatively sophiticated Excel file creation that uses Data Types ... Stocks, Excel Tables, Pivot Tables, Conditional Formatting and more.

13th September 2019: a fantastic page on corporate bonds and their analysis. How to program a table using dates, the IF(), AND(), MONTH(), YEAR() functions and arithmetical calculations. If you find difficulty using dates, consider this page.

11th September 2019: a friendly introduction to the setting up and use of a Training/Testing Model in Excel. It is an introduction but it is a good one. The page does not, however, carry out any detailed analysis of the validity of the model you will read about. The data are real and you can replicate what I have done very easily. The page is here.

22nd August 2019: As you all know, this site is free of charge and always will be. Every now and again, I announce something like the publication of one of my books. Sometimes my books are not free of charge because they are professionally published. Whilst that is true of my latest book, it IS free of charge, at least for a while. The title of the book is EXCEL POWER QUERY, AN INTRODUCTION: BUSINESS INTELLIGENCE PART I.Click on the title to go to the site where you can download it free of charge.

1st August 2019: I have already created two pages (Flash Fill, Flash Fill and Quick Analysis) on Flash Fill, both for Excel and for Power Query and here is another one. In this case, I am introducing even more ideas on how Flash Fill (FF) can be used very effectively. Firstly, by using FF to extract, combine, extract and combine; and secondly, I watched a video of a highly respectable Excel practitioner who tried to sell us the idea that there are times when FF is just not good enough and neither is Power Query: in that case, he said, use a formula ... he demonstrated a relatively complex formula. The formula worked but I am showing here that that presenter is wrong and that FF will do exactly what we want without programming anything.

14th July 2019: A different approach but the same quality. I have just posted my latest two videos to YouTube. Video 1 is about Flash Fill and Quick Analysis in Excel. Video 2 is about Flash Fill in Power Query and Quick Analysis in Excel. The aim is to illustrate how it is possible, even with dirty data, to create a basic dashboard within 10 minutes! Video 1 ... Video 2

19th June 2019: BOOKS ... Since I was last here, I have completed writing two books on Excel and I will announce publication details here when I have them. The first book is the fourth in my Excel Solutions for Accountants series and the second book is the first in the series on Power Query, Power Pivot, Power BI. These books are the only things I talk about here that are not free of charge.

CASES ... in addition to books, I create case studies and for courses I am running at the moment, I am sharing four such cases with you, in a PDF file. There are two cases on financial analysis, one case on budgeting for buying and running a private jet and a case on exchange rates. Look at the page to see the outline of each case and what you need to know about Excel to work through them.

5th May 2019: If you are serious about learning VLOOKUP in the Power Query and Power Pivot environments, look no further. I have created two videos, one of PQ and one for PP to help you. Just go to this page and take a look at my detailed explanations.

20th March 2019: One file, two videos. This page outlines what I did after downloading some World Health Organisation data on DTP3 vaccinations (Diphtheria, Tetanus and Pertussis), covering the period 1980 to 2017. From a health point of view, what you see here is fantastic. From an Excel point of view, what you see is intermediate level but there are enough features illustrated to make it well worth your while to take a look at it! The page is here

6th March 2019: This page ends with an Excel file but it is really a discussion of how I created some Power BI files that I then used to create the Excel files by copying M Language code from one to another. What I describe here is magical and I have used the departures and arrivals information for both of Bangkok's two international airports as the basis of my work. The page you want is here and there are two bare bones Excel files to download ... Word Press will not allow me to share Power BI files, I am sorry to say.

15th February 2019: Let's watch a video! I have created a video showing you how to use Data Types Stocks to pull in share prices, Beta values, market capitalisations of potentially thousands of different companies from around the world. There is a video to watch and a spreadsheet to download. The page is here: Data Types ... Stocks

7th February 2019: I know you have read my pages on Sparklines and therefore you know how clever they can be and how you can format the cells they are in, you can add text/formulas to the cell they are in. Well, now, you are about to learn to use Conditional Formatting Icons in them. As far as I know, you can find this technique nowhere else on the web! Here is the link to the page Sparklines with ICONS

9th January 2019: This is a fabulous page ... not because I am so brilliant but because I have just learned how to combine array constants with a VLOOKUP function. It has been around for years but it just filtered down to me. You can thank me later! Here is the page you need: https://excelmaster.co/vlookup-with-array-constants/

8th January 2019: Happy New Year Everyone. Secondly, the purpose of this update is to share with you my solution to creating a histogram for any one of àbout 90 possible line items in a ratio analysis worksheet. The data relate to Netflix and comprise a ten year view of the ratios that come from the morningstar.com web site. The file also includes my Positive Skewness Test that I have recently started to program and use. There is a file to download too and if you find it useful, please tell me how you are using it. the file is here: https://excelmaster.co/one-histogram-90-choices/

If you'd like my help and guidance on something, just ask and I'll do what I can.

Duncan Williamson

Introduction for Absolute Beginners: Introduction to Excel for Absolute Beginners
Excel Files for Practice:
The basics of spreadsheeting 1
The basics of spreadsheeting 2
The basics of spreadsheeting 3
The basics of spreadsheeting 4

Tether, End of

A month or so ago I started feeling grotty and very quickly realised I probably had a fever. I saw a doctor immediately and he treated me.

The next two days were as expected but day three, when I normally expect to start feeling better, was the worst. Lethargy, fever, generally grotty. The following days were better.

However,  things have not really improved and yesterday I lost some hearing in my left ear. Today I got some more medicines since the ear infection follows on from my bronchitis and sore throat.

It really has been a bad month and a bad start to the year.

DW

Michaela Finnegan

This is the tale of Michaela Finnegan who grew whiskers on her chin ne gan. Who would ever have thought that I would be writing yet another doggy obituary? After all, Stewart died after a car accident and all other dogs are either dead or just too lazy to get into any bother ... that's you Pongo and Slutty! Well, I was wrong. Here is a photo of the tiny Thai Ridgeback that Siri found for us as a replacement for Pongo: Pongo the lonely. So lonely she sent him back to be bone idle with Slutty. After a couple of weeks I noticed that the Ridgeback has some rather nitty whiskers on its chin so I started singing ... There was an old man Called Michael Finnegan He grew whiskers On his chin-ne-gan The wind came up and blew them in again Poor old Michael Finnegan, Begin Again He was meant to be an outside dog but she tried her hardest to get into the house at every possible opportunity. I stopped it. It made no difference,i t still tried. I was determined. It got in from time to time as if I weren't there! Then we all left home for a while: I came away to work and Siri took a trip to Bangkok to meet me as I was passing through. Finnegan went to stay for a while at the Lazy P idle dogs home. Things didn't work out: after a few days they found Finnegan with blood coming out of its ears and generally being in a bad way. They think one of the neighbouring dogs snapped at Finnegan and cracked its skull: chances are, that mutt was stealing Slutty and Pongo's food and Finnegan decided to join in. SNAP! Slutty and Pongo are both cowards and stealing their food is a breeze. Finnegan was far too young to sense any danger from anything so that was the end of that. Three months old maybe and now in a downward spiral that would leave her dead fairly quickly. I had plans for that dog to be an outdoor and family dog. Abi played with Finnegan and Finnegan teased Abi by biting her feet with its needle like teeth. We played together, I threw it out of the house. We fed it. It slept outside. the poor thing never even had the chance to wear a tee shirt or scarf in the middle of a Thai Winter. Sorry you had to go Finnegan but thanks for the entertainment while you were here! DW

Low Fares? Define Low

I don't like AA ... that's an airline! They try to convince us that everyone can fly now because they are so cheap.

Well, because their competitors do not fly to KL on Sunday from Bangkok I had to choose them. Kerching! Low fare? Not at all! I could actually pay less with the other airlines AND have a meal AND not scratch around worrying about the weight of my luggage AND choose any seat that's available: from previous experience,  they will give me a window seat, the worst possible option for me.

Then, due to misinformation from another carrier I found I had to change my flying time. SORRY,  it's less than 48 hours to take off so you can't change that flight. So, horror of horrors, I had to buy a new ticket. Low fare? Free meal? Guaranteed baggage allowance? No, no, no. I consider this second ticket outrageously expensive and feel sad that I have had to break my vow of refusing to fly with this airline. Until recently, I had not flown with them for 2 or 3 years. I hope this is my last trip with them for a very long time.

Appalling Behaviour on a Newspaper Forum

I have discussed many topics on newspaper forums over the years: The Independent had one then closed it; I subscribed to The Times for a few years and that was a good experience; now I am with The Guardian, new starter. I have paid membership dues to the Guardian because I feel I should but they say many people use their services but don’t pay. No judgement there, that’s how the Guardian manages things. I engaged in my first full blown discussion on the Guardian site last week when I told the assembled horde that Al Gore’s Inconvenient Truth visuals and other parts of his message were wrong or inappropriate. I said that and I believe that: my argument is that his graphs were far too nice to warrant critical review and many of his other ramblings have been challenged: I said that knowing it to be true: youtube contains many videos in which Gore is caught out. Immediately, I was attacked rather forcefully and I have to say that I did call Gore a liar. I defended myself and as the debate wore on, I was called all sorts of names and accused of all sorts of calumny. Then someone said I was a climate change denier. They made that up because I didn’t even infer that let alone say it. Well, that was it: John Samuel claimed to be a weather expert of some kind and he really stuck to that line and in spite of proving no such words came out, he called me a liar many times. Good for him. I even came here to this blog and reminded myself of what I had said about Gore when I had seen his film. I told Samuel that I had blogged about Gore in 2007 and again in 2010 … three references altogether. Since he was being so unfriendy, I suggested he search for my blog to prove for himself the truth of my assertions. He couldn’t find it! Smart as he claimed to be, in spite of clues as to a particular phrase to put into google, it was beyond him. He found an Excel blog and pretended that was the blog I meant. I said no, so he called me a liar again. I then mentioned having read and reviewed State of Fear by Michael Crichton which I said contained many interesting ideas. Samuel replied to tell me how bad the book was and that no one liked or respected it. Climate change denier that I am. You’re getting the picture now: John Samuel who does not know the difference between you’re and your, tells the world that they should not read a book because he does not like it. The reality is that Crichton might have got some of the science wrong but he questioned things and I think that was a very healthy thing to do. As Samuel tried only assassinate my character, others joined in and of course, several of them pointed out their master’s degrees, how they have been peer reviewers for academic journals and what do I know? All the while, these people have assumed that Samuel has identified the truth: he said the same thing so often that the others believed he must be telling the truth. I have seen this before and it is impossible to challenge because everything is subject to gainsay. Samuel was obviously frustrated that I wouldn’t just give him the link to my blog posts so I was branded a liar again but I felt he’s so smart he can ruddy well find it. Moreover, he would clearly have merely said something innappropriate about what I had said so I let it ride. I am sharing this post with him now and even he will then find that I really did make those posts about Gore all of those years ago and that I am not a liar! As that discussion wound down Samuel revealed another two traits: he labelled many people as liars if he didn’t like what they said; and he was obsessed with having the last word. I played the last word game and he played along exactly true to type. If John Samuel is really a scientist I just wonder if his work is ever peer reviewed as he cannot take criticism. He must be very difficult to work with when someone like me turns up and refuses to bow down to him. A very immature bully in my opinion. I decided to write this post since it is a controlled way of sifting out that man’s own lies and exaggerations and at the same time sharing with others just how he behaves in debate. Samuel and others are free to comment here and I will post everything that's not just outright offensive! I made mistakes in my discussions and admitted them: I am not too proud to do that but I will not allow some tiny minded little man to bully me or anyone else into kow towing to him. DW

Put the Bloody Phone Down or it Will Kill you

On our way to replace the driving licence I lost and as we were crossing the road a woman on a motorbike came round the corner and was driving right at me and Abi. The stupid woman was reading something on her phone as she rounded the corner and had not seen us.

I was ready to fend her off but she looked up, saw us and avoided us. I said to her, Watch where you're going and put your phone down!

How stupid and how dangerous that woman is.

Multi worksheet scenario model

Tuesday 25th October

This is on its way ... as promised ... I will upload it later today or early tomorrow.

Duncan Williamson

The Charge of the Light Brigade

As I was travelling across country by bus today I thought about Alfred Lord Tennyson's poem The Charge of the Light Brigade. I went and found it, read it and then created a voice file of a professional actor reading it out loud. The professional actor is me! Unfortunately I can't upload the voice file here ... I will find a way. The Charge of the Light Brigade Half a league half a league, Half a league onward, All in the valley of Death Rode the six hundred: 'Forward, the Light Brigade! Charge for the guns' he said: Into the valley of Death Rode the six hundred. 'Forward, the Light Brigade!' Was there a man dismay'd ? Not tho' the soldier knew Some one had blunder'd: Theirs not to make reply, Theirs not to reason why, Theirs but to do & die, Into the valley of Death Rode the six hundred. Cannon to right of them, Cannon to left of them, Cannon in front of them Volley'd and thunder'd; Storm'd at with shot and shell, Boldly they rode and well, Into the jaws of Death, Into the mouth of Hell Rode the six hundred. Flash'd all their sabres bare, Flash'd as they turn'd in air Sabring the gunners there, Charging an army while All the world wonder'd: Plunged in the battery-smoke Right thro' the line they broke; Cossack and Russian Reel'd from the sabre-stroke, Shatter'd and sunder'd. Then they rode back, but not Not the six hundred. Cannon to right of them, Cannon to left of them, Cannon behind them Volley'd and thunder'd; Storm'd at with shot and shell, While horse and hero fell, They that had fought so well Came thro' the jaws of Death, Back from the mouth of Hell, All that was left of them, Left of six hundred. When can their glory fade? O the wild charge they made! All the world wonder'd. Honour the charge they made! Honour the Light Brigade, Noble six hundred

Can I automate the functions found in the 'analysis ToolPak' in Microsoft Excel?

Someone asked this question in Quora and here is my answer which I think many of you will find useful:

If you use Excel on a Mac the chances are that you are not running Excel 2016 for the Mac and that your Mac does not have the ToolPak at all … I know, older versions have it and I know you can get alternatives!

In that case, I often demonstrate to Mac users how to create and automate the functions in the ToolPak: correlation matrix, regression analysis, moving averages, descriptive statistics … the others as well!

Descriptive statistics, for example, could be, for data in column A:

=AVERAGE(A:A)

=STDEV(A:A)

=KURT(A:A) …

=SKEW(A:A)

and so on.

Other answers have mentioned statistics software packages and that’s fine except they might not be free! Yes, if you are a student, your college or university is likely to have statistics software free for you to use.

How about R and R Studio, however? Open source, free, with massive amounts of support? Of course, it takes time to learn R but here is the code for some descriptive statistics using the psych package in R:

describe(order_sales_profit$Sales)

That’s it! This is what I get from my current data set, sales values: not exactly the same as the ToolPak but my point is, it is very easy to replicate. Look at the screenshot of the output from R.



By the way, as a novice or beginner level user of Excel, there is a lot to learn from manually automating what’s in the ToolPak. Moreover, if you take my next learning point, use this opportunity to set up templates for you to analyse your data sets: that means, you automate the ToolPak elements once and that is it!

Finally, many elements of the ToolPak return non volatile results which means that if you change your data, you have to run the ToolPak again. If you automate it yourself, the formulas you create will all be volatile: change the data, change the answers!

Duncan Williamson

I like such photographs

I hope you can see this photo.

DW

Top Tips: rules you really should follow

19th August 2016

I have just completed another very successful Financial Modelling course and as you know, at the end of such courses, I come here and offer something new: a new topic, a new file or some advice. In this case, it is advice: things that you really need to think about when you create and work on any Excel file.

• Tab/Sheet Names


• Links


• Dead Cells 125,433 rows created but only 831 needed/active ... files that balloon to many Mb for no real reason

Ever seen a tab name like this: FBU or OPT? I bet you have: short and sweet and probably mnemonic so easy to read and remember. How about L_P_Obasange_receivables_dont_forget_to PRINT_it_out? You think I am joking? I am serious! Just imagine you are working on your file with the large tab name and you want to link to a cell on that tab from another one: this is what will appear in your formula, by way of an example ... =IFERROR(AND(A15=45,D26="Jack",L_P_Obasange_receivables_dont_forget_to PRINT_it_out!BA154 ...

I am sure you see the point now. Keep tab names short and simple! More than that, if you do feel the need to use tabs to give instructions, colour code them to pass such messages: there are many colours to choose from so do that. Have a table of contents too. Give everyone a chance for a simple life!

Links

If you share a file with someone, make sure any links in your file are either live or delete them. If you receive a file with links that you cannot use or update, you know how frustrating it is. Think of the user before you send linked files.

Dead Cells

It is the easiest thing in the world to create a worksheet and as you work and improve what you are doing, to delete cells and ranges. We all do that. We create new ranges too, don't we! Check your work now and again though and if these happen, take a break and check your file:

• it takes 30 seconds 45 seconds or even longer for the file to open


• what seems like a small file in terms of content and complexity has ballooned in size to 20 or 30 or more Mb


• saving the file takes an age too

If these things happen, go to a worksheet and press Ctrl+End and see where that takes you. You work only in the range A1: CD831 but Ctrl+End has taken you to CG125433 ... what? How did that happen?

Even if there are not as many as an additional 1.8 million cells but just 500,000, look in those cells for formulas  that are trying to find something from somewhere that is not there ... in some of these extra cells for example. Delete all of these extra cells. I did that this week: an extra 1.8 million cells in TWO separate worksheets complete with formulas. File size down from 28 Mb to 0.8 Mb, opening time just seconds, recalculation time hardy noticeable.

They were just some of things to report on from this week. Otherwise, this group of delegates really enjoyed the work and their end of course presentations were interesting and showed that significant learning had taken place!

Duncan Williamson

 

This Week's Delegates

Here they are, the chosen few from my course in Ghana this week.

Good delegates, successful course: financial modelling.

DO

Accra Ghana Spark Page

Just take a look at this very simple Spark Page I have just created.

<a class="asp-embed-link" href="https://spark.adobe.com/page/rdgNxpzgPBQ5R/"><img src="https://spark.adobe.com/page/rdgNxpzgPBQ5R/embed.jpg?buster=0" alt="Accra Ghana" style="width:100%" border="0" />Duncan's Accra Spark Page</a>

I hope you like it.

DW

Boeing 787 ... 3 out of 10

I don't normally talk about aeroplanes and flying but sometimes ... You have probably heard of the Boeing 787 Dreamliner and how marvellous it is. I have just flown on it for the umpteenth time and again I come away wracked! The seats are bone hard and the economy cabin is cramped: they cram us in. For anyone in the first row in any cabin in economy, they have put the TV remote at hip level within the seat AND it's non removable. That means you need to be a contortionist to use it and if you are even slightly on the large side you will not be able to see it let alone use it. I firmly believe that this aeroplane was designed and built by people who never fly in it or never fly economy in it. In my opinion, it's an insult although I know the airlines love it because it runs cheaply compared to other planes. Well, here you are Boeing: 3 out of 10. DW

In the Current Climate

As I was about to get to the passport control desk at DXB, a man in the queue behind me pointed out to one of the staff that there was an unattended bag in the middle of the floor. There was!

The staff member suggested that someone had probably left the bag there as he went round and round the queue system to save having to carry it.

The man responded with. I realise that but no one should leave their bag like that in the current climate.

The man was absolutely right but I loved his use of the phrase, in the current climate!

DW

Fungus is here

These just grew in the garden!

DW

Fascinating Story

Read this story if you can. Very interesting in every way!

The Iraqi who saved Norway from oil - http://www.ft.com/cms/s/0/99680a04-92a0-11de-b63b-00144feabdc0.html

Champion Burnley FC

Well done Burnley FC,  promoted to the Premier League for next season. A 23 game unbeaten game run in too.

Let's hear it for the team and for the manager Sean Dyche.

Of course, Burnley has never been a fashionable team so the response of the media to their achievement has been grossly understated.

How to Erect a Pillar Perfectly

They have erected the support pillars for our new venture and they are perfectly vertical.  To get them vertical they used two pieces of string, a plumb line and two bits of wood to adjust them if necessary.

Fantastic skills.

DO

The Start of a new Project

Here is the ceremony to celebrate the start of a new building and a new venture.  The ceremony went well and I hope the project does too.

DW

And She's Off!

Daughter Abi will be 10 months old tomorrow and already she is standing for a short time without holding on. She's also taking steps or trying to.

Good progress

DW

Business Intelligence

Are you interested in using BI? Do you already use it?

Did you know that BI is free to create?

I will be adding some BI resources here this week ... but there are already some here! Take a look at Power Query and Power Pivot for a start.

Back soon and if there's something you want me to write about, let me know.

Duncan Williamson

SUMDIVIDE ... sort of

A delegate asked me today if there was such a function as SUMDIVIDE. There isn't of course, but I found a way to simulate it.

What would SUMDIVIDE do? SUMDIVIDE would have array 1 divided by array 2 and the results then added together: in the same way that SUMPRODUCT multiplies and then adds.

This is how it works: imagine A1:A5 contains array 1 and B1:B5 contains array 2 then the SUMPRODUCT function to divide them will be =SUMPRODUCT(A1:A5,1/B1:B5). Simple, eh? Who'd have thought it would be so simple.

Duncan Williamson

When the TV Doesn't Work

I flew from Bangkok to Dubai last night and even though I have lost my gold status with them, I chose to fly with Emirates. As long as I can I suppose I would always choose to fly with Emirates. I can't use their lounges across the world now and I don't get welcomed onto the plane with a special greeting any more. When something goes wrong on an Emirates flight, I take it personally: I always have and I don't know why. With some service providers, if something goes wrong, I think how typical it is or that I am glad I don't use THEM all of the time. Yesterday, I settled into my seat, said hello to the couple next to me as their 13 month old daughter made herself known to everyone! I started to see what ICE had to offer. I made a few mental notes of what I might watch. Then it turned from ICE to ICE Lite ... the service you get on ICE on a rickety old crate flying to somewhere not so glamorous. Half a dozen films, almost no music, four or five TV programmes and virtually no radio programmes. That is a far cry from the thousands of normal choices. Instead of the ICE home screen I had ICE Lite with Alvin and the Chipmunks as my home screen. I looked around and it seemed to be just my TV that had gone wrong. I asked one of the cabin crew to help me to sort it out. She seemed to understand the problem, took a note of my seat number and went away. The TV stayed with Alvin. I had some things to watch on my iPad so I watched them. Then I saw another cabin crew member and asked him for his help: 2.5 hours to go to Dubai. He looked at the screen and made a call for them to do a full reset of my screen. 10 minutes, he said, is what it would take. I waited 10 minutes as I was wondering if I had time to watch my film of choice. 10 minutes came and went and the screen went black as the handset stayed ICE Lite. I decided to do some work, reading my papers for the coming week then I watched something on my phone this time and with 90 minutes to go I saw on the handset that ICE had come back. I thought at least I could listen to some of my favourite UK Number One Hits. So I opened the screen and started browsing. I found the first of the songs I wanted. Click! Waiting ... waiting ... waiting ... I chose a different song. Click! Waiting ... waiting ... waiting. I left that idea and tried to start a film and as I found the new James Bond, Spectre, Alvin and the Chipmunks came back with their ICE Lite. As far as I could tell this was only my problem. That's why I take these things personally: only me me me! So, a relatively long flight without the possibility of entertainment of any kind is a very old concept isn't it? On other airlines I have been given a DVD player to offset the loss of the TV. In this case, one cabin crew member did something and the other one MIGHT not have: what neither of them did was follow up on my problem. They both left me and didn't return. So, I felt let down by my number one choice of airline. DW

Are you Following me?

Time is just flying by and although I've got so many things to share time is against me.

In addition to this blog you might want to follow me on LinkedIn too ... I don't accept everyone there but if you tell me you subscribe to this blog I guarantee acceptance.

I am working in Dubai this week: presenting a three day course on Financial Modelling and Business Intelligence. Then a two day course on Budgeting and Cost Control.

With my courses you get a tool box of functions and techniques. You also get one to one time with me as I solve your problems ... financial modelling etc! You take away all of my notes and PowerPoint slides as well as all of my fully worked Excel files.

I use real world data in my models and demonstrations. You get full explanations from me. I am also honest: if you ask me something I will give you the right answer, even if it means I have to do further research or ask a friend!

Find out where I am working and what I am doing and who knows, you might even join me one day. Invite me to speak where you are and maybe we can get together that way.

Duncan Williamson

Let me try 23 Mbps!

See my previous story about my wifi connection.

We found out by accident that the router in the sales office of our new wifi provider pumps out 20+ Mbps speeds. I thought, I'd like to see what that's like.

I set my phone to download a 330 Mb video and stood outside the wifi provider's showroom. Nothing. It didn't start!! I waited a while then checked the speed ... very slow at about 1 Mbps.  I thought: ah! It hasn't connected properly yet. I went away for a while and then returned. The same.

Anyway, having checked download speeds again I concluded that the office had not turned on their cable router! So I was using their ordinary system from outside this building.

End of my experiment!

DW

Monopoly Broken

I have been suffering for 18 months or so from a monopolist wifi supplier. Variable connections. Breaks in connections for as long as two weeks. 18 breaks in service in a January alone. February started with the first 6 days dead.

I searched in vain for an alternative all of this time and became most frustrated and angry when I realised that these people were managing my business and private life. I need a decent connection for work and for my relaxation, entertainment and so on.

A friend told us about a router they had bought in Switzerland ... good solution but very expensive to buy. It sowed a seed, though.

Then a chance conversation sent us to a local provider of service who offered 4G pocket routers.  Really? We looked at their brochure and asked questions. While 4G doesn't reach to our village, it will soon.

I thought about it for a while: it would cost us the same as our current provider per month and the pocket router came free!

I said, I want to take the risk: there's a chance that this will be better ... we did it. We came away with the router and some hope.

We got it home and it works. At times we can get speeds of 5 to 6 Mbps. As importantly, after 5 days, service has been unbroken and because it is a pocket router, we can take it with us wherever we go.

I stopped paying for the old service immediately. They are no more.

Since I have been sending the old providers an sms whenever there has been a significant break of service, I said: they will realise something is different when they realise I have stopped texting them!! After all, we didn't tell them we were leaving since they never told us that their service had stopped. I felt no loyalty to them.

After 5 days they asked how things were going and we told them we no longer needed them. They said oh!

Incidentally I used to send tweets to the head office of our old provider: they never replied.

Another case of monopoly madness.

Reward or Retard?

I wrote a long and solidly constructed piece for this post then I lost it, in the spirit of this weekend.

Bottom line: I did a job that should have brought me £0.50 per unit. I have been paid £0.04 per unit. It's a matter of control over channels in the same way that I have no control over the awful broadband connection I suffer from here.

So, shit happens as they say :)

DW

Flat Fish or Flat Cat

The cat you see is scraping some fish off the road for breakfast.

The cat got a whole fish from the rubbish bin and took it there to eat: middle of the road in a car park.

A car came along and as the cat was reluctant to move, the driver very carefully inched the car forward. He didn't see the cat moving out of the way so he reversed the car until he saw the cat again.  The cat had moved out of the way but now it came back.

The driver then drove to the right then the left as he tried to ensure he didn't kill or injure the cat. He was successful: the cat survived!

What the driver did do was to drive over the fish that the cat left on the road. Hence flat fish but not flat cat!

DW

Flat Bread

For some reason I decided I wanted to make some flat bread.  Today I did it!

I didn't have yoghurt so I used milk instead. Otherwise, so simple and they turned out pretty well. I turned one of them into a pizza!!

Look:

Cramp, wifi and a water pump

I was woken this morning by cramps in my left calf. Horrific. Extremely painful. It wouldn't stop. Eventually I was able to move to get out of bed but I cannot remember such a bad attack.

Our wifi is currently broken ... for the EIGHTH time this month. Not bad since it's only 13th January.

Our water pump stopped working this morning too: ants had been crawling through it and shorted it out. It's ok now thankfully.

DW

The Books Are Available Now

My latest two books are now available for sale: Finance for the Non Financial Manager Buy Here Finance for the Non Financial Manager Exercise Book With Solutions Buy Here These two books are crammed with information, techniques, exercises, Excel help and guidance and having read them, you will have learned a huge amount and you will be able to do a huge amount. DW

Biff!

Daughter Abi is 7 months old now and can crawl and stand using furniture. Hooray!

As everyone was getting ready this morning I checked on Abi in the living room: she was standing,  holding on to the settee. She was alone and I thought, she'll be fine. And she was.

After a few minutes I went back in the room to see she had crawled quite a way across the room.

I left her and she started calling so I shouted back for her to follow me. She did. As I waited, I saw her appear in the doorway ... then suddenly ... boof!  Her hand stuck to the floor and as she motioned forward that caused her head to go down instead of along.

She got a temporary fat lip and dented pride.

Otherwise,  all is well!!!

DW

My Kitchen Rules Australia 2014

I would never have heard about this programme if I hadn't taken out a trial for a film/TV etc streaming web site. However, I have heard about it now and I watched about a third of the episodes of the 2014 series and enjoyed them. It's a typical cookery programme in which people talk really big about their cooking skills and then when it comes down to it, they often serve rubbish. I don't say that in a superior way because I do the same at home and have never put myself in the position that these men and women have. Good luck to them that they tried. I liked most of the contestants most of the time: the stars were the Greek twins I think although they didn't win. The couple I came to despise did make it to the final but the good news is they didn't win: Chloe and Kelly, two awful, vindictive, spiteful women who talked big about everything but mostly themselves. They rubbished everybody else and everything else. How they got to the final is a mystery although I have to say in the final they did alright. What really got to me was Kelly's massively artificial, forced smile. In the end, every time she was on the screen I covered her face, I just couldn't bear to see it any more. Well done to Bree May and Jessica Liebich who did win it and they won it in style: with an excellent menu, excellent coordination and excellent technical skills. Good to see. I liked this programme but it is such a big commitment that I am not sure I would watch so many episodes again. I understand they are producing MKR in the UK now or soon and I am sure they will find some of the worst people on the planet to compete in it. Just like they did with Storage Hunters (the UK equivalent of the US programme of a similar name). The people they found for that were squirmingly and stereotypically naff beyond all telling. You can now see what kind of things are on offer on expat TV, can't you. Stuff I would never normally watch in a million years. I have now found much better fare to settle down to, however! DW

New Book: Finance for the Non Financial Manager

I have submitted my manuscript to my publisher now for my latest book: Finance for the Non Financial Manager. This will be book available online at www.bookboon.com and I will post the direct URL once I know what it is: shouldn't be too long. The business model for www.bookboon.com is that some books are free of charge and others have to be paid for: I have no idea which is which as I type this but it will be obvious once it's uploaded. In any case, the books are not expensive. There are special offers too which means that sometimes a premu, paid for book, is suddenly free of charge for a while ... worth watching out for. Remember, my 3 volume set Excel solutions for Accountants has already been on sale at www.bookboon.com since the end of June 2015 and not only is it selling well but the reviews are fantastic! My page on ww.bookboon.com is here: Duncan Williamson Author Happy reading: there are many exercises and Excel files in the package too ... it really is good value! DW

Happy New Year

I know the weather has been bad in lots of places but the Christmas holidays have been and gone now for most people. So as you settle back in at work or college or even at home, here's wishing you a happy new year 2016.

DW

Weather Forecast? Far From it

Before Christmas I called my old friend Malcolm in Halifax for a chat and we talked about this and that,  as usual.

Since he has no real idea of where I am living and what it's like, he asks me questions about all sorts of things, including the weather.

I told him it was winter now, the dry season: it probably won't rain again until April next year, possibly, I said. Oh! He said.

Of course, within two hours of ending the call, we had at least an hour of solid rain here!!!

DW

New Video! -

Turkmenistan

I have arrived in Ashkhabad,  Turkmenistan and first impressions are favourable.

A lot of white marble buildings: new and modern looking. Good roads with lots of nice looking cars.

My trip from the airport to the hotel showed that most cars are Toyota,  BMW, Lexus with a smattering of Lady and Niva.

It's already dark so I will see the lie of the land in more detail in the morning.

DW

New Video! -

three dice