The Wiert Corner – irregular stream of stuff

Sometimes in a table, you want to have a key column where one of the rows sorts after Z (for instance having a total value further on).

The A-Z sort order sorts all non-letter ASCII characters in front of A-Z and a-z because [WayBack] Excel sorting is not in ASCII order – Microsoft Community, see ASCII Sort.xlsm – Microsoft Excel Online.

Using =NA() (which displays as  #N/A  ) is too visually intrusive (but works, see: [WayBack] Forcing an item to sort last in Excel [Archive] – Actuarial Outpost)

Luckily, putting in an Arabic character like 'ٴ  works. You can even put it in front of normaal ASCII characters like in 'ٴ ----- which then displays it at the right (since Arabic is Right-to-Left) -----ٴ .

The character is high Hamza – Wikipedia; [WayBack] Unicode Character ‘ARABIC LETTER HIGH HAMZA’ (U+0674)

via:

• [WayBack] sorting – Simple to enter Unicode character that would sort after Z in most cases? – Stack Overflow
• [WayBack] Windows Sort files by name. What character will come after Z in the list?

–jeroen

Since I don’t do Excel visualisations often enough, I always forget the details on Pivot Charts, some links and tips below.

TL;DR

You can’t have enough axes

The tips below assume you can create a pivot table from an existing table (that already can contain formulas), then show you:

• additional formulas in your original table can make life easier
• formulas for pivot tables themselves (named “Calculated Fields”)

Problem at hand

Creating graphs out of up and down time durations over time, aggregated by day.

Ideas for correlations that might matter:

1. linear over time during a few weeks
2. by week and by day of week

Incoming data:

• end-timestamp
• state (down or up)
• duration of that state

Calculations

First of all, I needed “day of month”, “day of week”, and “week number” so I could group by those. Based on Readable weekdays in Excel, you get formulas like these:

• =DAY(B4)
• =WEEKDAY(B4) and =TEXT(B4;"dddd")
• =WEEKNUM(B4)

Then I needed to split the duration of the state in distinct up/down durations. So I made a few formulas:

• =("Up", A4) to have a boolean for up/down
• =("Up", A4) to have a boolean for up/down
• =IF(D4;C4;0)to split the up duration from the state duration
• =IF(NOT(D4);C4;0)to split the down duration from the state duration

A pivot table could aggregate total up and down durations, but I wanted a measure of up ratio, so I needed a formula inside the pivot table itself.

Following the steps at [WayBack] Calculate values in a PivotTable Use different ways to calculate values in calculated fields in a PivotTable report in Excel 2010, I got to this one:

This aggregates nicely: drag it to the aggregates column, then change the aggregation to “Average”:

Putting it in a 3D Pivot Chart

Read the rest of this entry »