I Wonder Why!

We have just spent two hours in the Platinum shopping centre in Bangkok and in one part of the mall there is a clothes shop called Galashiels. I happen to know that Galashiels is a small town on the borders of England and Scotland and if only my Thai were much better I would have asked how they ended up with a shop with that name. Fascinating! For references, here is a link to that Scottish town, population just over 12,000: http://en.wikipedia.org/wiki/Galashiels Please note, this is not criticism of anyone or anything, just wondering aloud. DW

Moresby, Mali and Melbourne

I am in transit in Dubai and I have just been chatting to a Kiwi who works in a gold mine in Mali. I said, there’s a bit of strife over there at the moment isn’t there? In the North, yes, but not where he and his 149 expat colleagues are helping to mine gold and silver.

He started his expat life in Papua New Guinea but was baled out by the New Guinean army when the locals cut up rough over a pay claim and drove machinery and vehicles over cliff edges … you can imagine.

He is on his way to Melbourne now, a fourteen hour flight. His route is as follows:

• Mali to Paris
• Paris to Dubai
• Dubai to Melbourne

Two and a half days to get home. He has just left to find a bed for the night as he has a 9 hour wait for his next flight!

Good luck as we shook hands in farewell!

DW

Password Possibly Poleaxing

This has happened before but I can't remember when.

For some reason many of the services I use online are refusing to recognise my passwords. I then go through the process of resetting the dratted things but I cannot guarantee that I won't have to reset it again very soon afterwards.

Frustrating waste of time.

DW

Pivot Table COUNT Default Problem CURED!

I had a delegate this week whose Pivot Tables would take any of his numerical data and by default count them rather than sum them. When he asked me why that happened I went through the normal explanation of data v numbers ... which is true. However, in his case there seemed to be no reason behind this phenomenon: his data was clean as he was using my files and no one else had the same problem. He was using a good quality company laptop too.

We both checked on the internet last night and gained nothing from the many discussions on this problem: most of which related to data v numbers I have to say.

This morning I suggested that we look at his Windows Regional etc settings. His default language etc was US English and that seemed to be in order; but I said, let's try this, change it to UK English and see what happens.

Ta daa! For some reason, this change did the trick and now this delegate's Pivot Tables sum his data rather than counting it.

Another success story fromExcelMaster!

Duncan Williamson

Weak in Riyadh

The title's a pun!

Just finished my working week in Riyadh and will be travelling back home in about four hours from now.

DW

Reverse Pivot Magic

UPDATE: 13th September 2019 I can imagine some of my visitors will still find this page of use if they are using a very old version of Excel. However, if you are using Excel 2016 or 2013 or even 2010, you should be able to use a much newer and more powerful technique for unpivoting data. Of course, I already have a page for that and here it is: Power Query Unpivoting

UPDATE: 16th January 2014: I have been using this technique since I last reported and I have found some limitations AND solutions to this reverse pivot technique. I will prepare a page and video to demonstrate what I have found.

2nd December 2013

Watch the video and then imagine that you have downloaded a five year set of financial statements for a company. Nothing wrong with the data or the database they came from but imagine you want to set up a pivot table from them. Probably not possible unless you are lucky enough to have a data provider who has designed their output with this in mind.

Let's cut a long story short: open this file amazon_case_reverse_pivot and try to apply the reverse pivot technique in my video to the data you will see there. Good luck and it will be worth any effort you put into it! PLEASE READ THE NOTES in this work book re copyright ...

I didn't invent this technique but I learned it a couple of weeks ago and used it in earnest just now ... watch the video!

I had a table that had months across the top, years down the left hand side and data in between. What I wanted was the data in a list in three columns:

year month value

transposing, rotating, copying and pasting can give you what you want but it will take a long time. My original table covered all twelve months and five years ...

Reversing a Pivot table is what it's called and this is how to do it:

1. Press Alt + D then P to call up the pivot table wizard and select Create a "Multiple Consolidation Ranges PivotTable."


2. Select "I will create my own page fields".


3. Select your data range and choose 0 page fields


4. When you see your pivot table, double click on the intersection of Row Grand and Column Grand, in the bottom right hand corner of your pivot table


5. You will be presented with your new table in the form of a list ... that is, from pivot table style to list style ... it's magic!

The video [wpvideo tGsBeRej]

Duncan Williamson

AGGREGATE Function Templates

I know I learned this function a while ago but then I forgot about it. The aggregate function was newly created for Excel 2010 and it provides us with some interesting functionality: overcoming shortfalls in the performance of such functions as AVERAGE, MIN, MAX, LARGE, SMALL and a few more ... 19 of them altogether. The trouble is, good as it is, it is not that simple to apply. What I have done then is  to create templates that will help you to use the aggregate function in one or both of the formats in which it might be used.

From the Excel Help File:

Returns an aggregate in a list or database. The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.

Syntax

Reference form

AGGREGATE(function_num, options, ref1, [ref2], …)

Array form

AGGREGATE(function_num, options, array, [k])

The AGGREGATE function syntax has the following arguments (argument:

Function Number

Required. A number from 1 to 19 that specifies which function to use

Options

Required. A numerical value that determines which values to ignore in the evaluation range for the function

Ref 1

Required. The first numeric argument for functions that take multiple numeric arguments for which you want the aggregate value

Ref 2

Optional. Numeric arguments 2 to 253 for which you want the aggregate value

For functions that take an array, ref1 is an array, an array formula, or a reference to a range of cells for which you want the aggregate value. Ref2 is a second argument that is required for certain functions. The following functions require a ref2 argument:

• LARGE(array,k)


• SMALL(array,k)


• PERCENTILE.INC(array,k)


• QUARTILE.INC(array,quart)


• PERCENTILE.EXC(array,k)


• QUARTILE.EXC(array,quart)

The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value. But, hiding a row in vertical range does affect the aggregate.

My templates will tell you if you are trying to use the wrong format of aggregate and has then been set up to ensure you don't make any other mistakes when using it. This includes providing combobox guidance for choosing which functions and options you need to use.

The Excel file is available here: aggregate_function ... just click!

All feedback is warmly received as are suggestions for improvement.

Duncan Williamson

Wildcards are Great ... Until THIS Happens!

Wildcards

As I am working away this week, I can be found looking at, opening up, working on a spreadsheet or two. Last night I came across yet another excuse to build an Excel data set. I found the TopTrack 250 and just had to have it: all seven years' worth of day for the top 250 companies according to the Top Track definition. Look here for the 2013 data set.

So I duly copied and pasted the data for 2013 into a spreadsheet and edited it. Having seen that I liked what I had done I then copied over the other six years' worth of data from Top Track. Because the data copied over in a very standard and predictable way, I was able to select all six sheets of the additional data and work on them all concurrently: moving data from one row to another, setting up columns in a better way, lining up the data to make it consistent with what I had already done with the 2013 set. Easy and not too time consuming.

But now the wildcards. On the web site that the data came from they had symbols such as asterisks that pointed to a note that said ... average, estimate ... or something like that. I needed to get rid of them all! As it was late and I wasn't looking carefully, I carried out an edit and replace of * and having pressed Replace All, found that ALL of my data disappeared ... it was doing what a wildcard must do ... in this case, any character was being replaced by nothing! A very hasty Ctrl+Z put everything back to the way it was but I still had to get rid of these symbols.

What to do? Delete every symbol manually? No, there were too many of them. Given that the symbol was in the same place in the cell it was in, at the beginning, I ended up with a formula solution that I entered in a new blank column that I would eventually use to replace the current data:

=IF(LEFT(D6,1)="*",RIGHT(D6,LEN(D6)-1),D6)

Why D6? That's where the first of my data points is in each of the tables I set up. I then copied the formula across to the next cell on the right and then down to the bottom of the table ... it worked a treat.

I remembered that I should have added that there were other symbols in the data in addition to the * and I amended my formula to cope with the ‡ symbol:

=IF(OR(LEFT(D6,1)="*",LEFT(D6,1)="‡"),RIGHT(D6,LEN(D6)-1),D6)

Now I had to copy and paste my edited values as text rather than leaving them in formula format, otherwise I have to leave the original data in the file AND have the edited version. So I just copied and pasted the edited values over the original data and my data were now clean and tidy ... but read on!

Numbers as Text

One problem remained: having replaced these symbols, the result was numbers as text rather than number as number. In this case I did this:

• enter 1 in any available cell


• copy that cell


• select the entire range of numbers ... all of them so you miss nothing and don't waste your time selecting just the affected ones if your data set is large like mine


• paste special, multiply


• ta daa! your numbers as text have now become numbers as numbers


• Delete that cell with 1 in it

There you are wildcards and paste special to set up a nice, clean table of data copied from the web.

Duncan Williamson