The Wiert Corner – irregular stream of stuff

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

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

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

    Join 1,854 other subscribers

Archive for the ‘Office’ Category

« Previous Entries
Next Entries »

Display the relationships between formulas and cells – Excel

Posted by jpluimers on 2020/03/18

The “Formulas” tab has to buttons that help to Display the relationships between formulas and cells – Excel [WayBack]:

  • Precedent cells —   cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, then cell B5 is a precedent to cell D10.
  • Dependent cells —    these cells contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.

To assist you in checking your formulas, you can use the Trace Precedents and Trace Dependentscommands to graphically display and trace the relationships between these cells and formulas with tracer arrows, as shown in this figure.

Worksheet with tracer arrows

Related:

–jeroen

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

worksheet function – How to add or subtract to, or increment, column letters in Excel? – Super User

Posted by jpluimers on 2020/03/13

[WayBack] worksheet function – How to add or subtract to, or increment, column letters in Excel? – Super User:

Here’s the best I’ve found so far: =SUBSTITUTE(ADDRESS(1,( COLUMN() + 1 ),4),1,"")The part in the middle marked in bold is the only part that changes. In this example, it’s taking the current column and adding 1, so returning B if it’s in column A and AA if it’s in column Z.

It is related to the question and answer [WayBack] Formula to return just the Column Letter in excel – Super User:

FYI on your original formula you don’t actually need to call the CELL formula to get row and column you can use:

=ADDRESS(ROW(),COLUMN())

Then as an extension of that you can use MID & SEARCH to find the $ and trim down the output so you are just left with the letter:

=MID(ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()))+1,SEARCH("$",ADDRESS(ROW(),COLUMN()),SEARCH("$",ADDRESS(ROW(),COLUMN()))+1)-2)

edit You can even simplify this further:

=MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("$",ADDRESS(ROW(),COLUMN()),2)-2)

And it is part of a much more elaborate answer

Read the rest of this entry »

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

Error “30029-1001” – Google Search; when installing Office 365 language packs

Posted by jpluimers on 2020/01/31

On my list of things to figure out: Error “30029-1001” – Google Search.

This happens when installing language packs for Office 365.

Related:

–jeroen

Posted in Office, Power User | Leave a Comment »

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.is] Return 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 »

Download Earlier Versions of Office: 2010/2013

Posted by jpluimers on 2019/11/18

Not sure how long these will stay valid, but apparently my nl_office_professional_plus_2010_with_sp1_x86_x64_dvd_731121.iso was damaged (I forgot to check the hash) but I could download a fresh one from [WayBack] Download Earlier Versions of Office.

If you cannot find it by product key (I also tried it with a Visio one: worked fine too), then you can use [WayBack] Office 2010 direct link, X16-32250.exe, X16-32213.exe which are archived here:

–jeroen

via: [WayBack] Lost Office 2010 or 2013 CD/DVD? Legally Download Office From Microsoft

Posted in Office, Office 2010, Power User | 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.is] How 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 »

Resize a table in Word or PowerPoint for Mac – Office Support

Posted by jpluimers on 2019/11/08

Why does it take forever for Microsoft Office suite programs to function the same across their various components and over operating systems. It has been like 2 decades and still sizing columns/rows in tables is a nightmare.

[WayBack] Resize a table in Word or PowerPoint for Mac – Office Support.

In my case PowerPoint: it is so different from Excel, and even different from Word, that it gives me headaches.

This is what PowerPoint 2011 could do; more recent versions are only marginally better:

PowerPoint

You can resize a whole table to improve readability or to improve the visual effect of your document. You can also resize one or more rows, columns, or individual cells in a table.

Do any of the following:

Resize a table

  1. Click the table.
  2. Rest the pointer on any corner of the table until Table Resize Cursor appears, and then drag the table boundary until the table is the size that you want.

Change the row height in a table

  1. Rest the pointer on the row boundary that you want to move until Horizontal split arrow appears, and then drag the boundary until the row is the height that you want.

    If you have text in a table cell, the row must be the same height or taller than the text.

Change the column width in a table

  1. Rest the pointer on the column boundary that you want to move until Vertical split arrow appears, and then drag the boundary until the column is as wide as you want.

    If you have text in a table cell, the column must be as wide as or wider than the text.

Change the row or column to fit the text

  • Rest the pointer on the column boundary until Vertical split arrow appears or the row boundary until Horizontal split arrow appears, and then double-click it.

Make multiple rows or columns the same size

  1. Select the columns or rows that you want to make the same size, and then click the Table Layout tab.
  2. Under Cells, click Distribute Rows or Distribute Columns.

    Tables Layout tab, Cells group

–jeroen

Posted in Office, Office 2011 for Mac, Power Point, Power User, Word | 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 [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 »

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

« Previous Entries
Next Entries »