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 »
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
- Click the table.
- Rest the pointer on any corner of the table until
appears, and then drag the table boundary until the table is the size that you want.
Change the row height in a table
- Rest the pointer on the row boundary that you want to move until
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
- Rest the pointer on the column boundary that you want to move until
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
Make multiple rows or columns the same size
- Select the columns or rows that you want to make the same size, and then click the Table Layout tab.
- Under Cells, click Distribute Rows or Distribute Columns.
–jeroen
Posted in Office, Office 2011 for Mac, Power Point, Power User, Word | Leave a Comment »
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 »
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.
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:
Creating graphs out of up and down time durations over time, aggregated by day.
Ideas for correlations that might matter:
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 durationA 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”:
Posted in Excel, Office, Power User | Leave a Comment »
Posted by jpluimers on 2019/09/30
Since I always forget this: [WayBack] Exceljet: Get day name from date
If you need to get the day name (i.e. Monday, Tuesday, etc.) from a date, there are several options depending on your needs.
Basically there are four ways go get the day of the week; the first three are readable, but when ordered, they are ordered alphabetically. The last one is numeric.
Combining the numeric with the text is easier in for instance Pivot Legend Fields (Series).
So here they go (based on the above link), assuming that B4 contains a timestamp:
=WEEKDAY(B4) gives you the numeric weekday (starting with Sunday=1 to Saturday=7) which allows sorting in a meaningful order=TEXT(B4,"dddd") gives you the full day name of B4 in your locale=TEXT(B4,"ddd") gives you the shortened day name of B4 in your locale=CHOOSE(WEEKDAY(B4),"Sun","Mon","Tue","Wed","Thu","Fri","Sat") gives you a name of your liking from a series of 7 textsNote that depending on your locale, these formulas might actually need a semicolon:
=WEEKDAY(B4)=TEXT(B4;"dddd")=TEXT(B4;"ddd")=CHOOSE(WEEKDAY(B4);"Sun";"Mon";"Tue";"Wed";"Thu";"Fri";"Sat")–jeroen
Posted in Excel, Office, Power User | Leave a Comment »
