The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,815 other followers

Archive for the ‘Excel’ Category

Removing “Workbook Contains Macros” Prompt – Free Excel\VBA Help Forum

Posted by jpluimers on 2019/11/22

This indeed was an Excel 2011 for Mac thing.

Even without macros or VBA modules, Excel 2011 for Mac shows this dialog when opening a .xls file.

The solution was simple: save as .xlsx.

–jeroen

via [WayBack] Removing “Workbook Contains Macros” Prompt – Free Excel\VBA Help Forum

Posted in Excel, Office, Office 2011 for Mac, Power User | Leave a Comment »

Return empty cell from formula in Excel – Stack Overflow

Posted by jpluimers on 2019/11/19

I never thought you could do it, but you can: [Archive.isReturn empty cell from formula in Excel – Stack Overflow.

You have to crate:

  • a VBA function
  • a reference to a range that evaluates the function so it returns blank
  • a cell with a function that blanks the cell using the range

Convoluted, but clever!

–jeroen

 

Posted in Development, Excel, Office, Office VBA, Power User, Software Development | Leave a Comment »

How to Extract a Number or Text from Excel with this Function

Posted by jpluimers on 2019/11/11

Lots of fuzz, but the formula towards the end worked:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Source: [Archive.isHow to Extract a Number or Text from Excel with this Function

More of those at [WayBack] Extract Only Numbers From Text String

–jeroen

Posted in Excel, Office, Office 2011 for Mac, Power User | Leave a Comment »

Excel on Mac OS X / macOS: character that sorts after Z

Posted by jpluimers on 2019/10/04

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:

–jeroen

Posted in Excel, Office, Power User | Leave a Comment »

Excel Pivot notes: Table, Pivot Formula, 2D, 3D charts and secondary axes.

Posted by jpluimers on 2019/10/04

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 [WayBackCalculate 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 »

Posted in Excel, Office, Power User | Leave a Comment »

 
%d bloggers like this: