 | Jeroen Wiert Pluimer… on Does Odido (the old T-Mobile N… |
 | Lars Fosdal on Security alarm provider Woonve… |
 | Thomas Mueller on Question got closed in May 202… |
 | Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
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/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 »
Posted by jpluimers on 2019/08/16
Since I always get confused by the differences in Excel versions (not just between Mac OS X and Windows):
In Excel for Mac, you can adjust where automatic page breaks occur, add your own page breaks manually, and remove manual page breaks.
Source: [WayBack] Insert, move, or delete page breaks in a sheet.
–jeroen
Posted in Excel, Office, Office 2011 for Mac, Power User | Leave a Comment »
Posted by jpluimers on 2019/06/28
I hardly print with Excel, so I always forget these two:
–jeroen
Posted in Excel, Office, Office 2011 for Mac, Office 2013, Power User | Leave a Comment »
Posted by jpluimers on 2019/06/10
Via [WayBack] ms office – Keyboard shortcut to select all text in a cell in Excel – Ask Different a few keyboard tips.
Lets start with the shortest one:
Command–Z or Control–Z to undo the changeThis probably is unintended, but works great: all text is now selected, so you can copy/cut with Command-C/Command–X.
Now the “official” way:
Control–U or F2 to edit the cell (the cursor is now at the end)Shift–Alt–Home or Shift–Control–Home (to select all text)Home can also be Fn–Left.Other selections you can make while the cell is in edit mode:
Shift–Alt–End or Shift–Control–End (to select to the end of the cell)End can also be Fn–Right.Shift–Alt–Right or Shift–Control–Right (to select one word to the right)Shift–Alt–Left or Shift–Control–Left (to select one word to the left)Shift–Alt–Down or Shift–Control–Down (to select to the same position on the line below)Shift–Alt–Up or Shift–Control–Up (to select to the same position on the line up)Keyboard symbols (more at [WayBack] Command, Option, & Shift Symbols in Unicode):
⇧ – Shift^ – Control⌥ – Alt which is the same as Option⌘ – CommandFn – Function–jeroen
Posted in Apple, Excel, Mac, Mac OS X / OS X / MacOS, MacBook, MacBook Retina, MacBook-Air, MacBook-Pro, MacMini, macOS 10.12 Sierra, Office, Office 2011 for Mac, Power User | Leave a Comment »
Posted by jpluimers on 2019/06/03
What key combination do I have to press to create a line break in a cell in Excel for Mac 2011? The Windows combination of Alt+Enter does not work on the Mac.
Source: [WayBack] keyboard – How to add a line break in a cell in Excel for Mac – Ask Different
The answer depends on the Excel for Mac OS X version you are using.
Excel 2015 is simple (thanks esham): use Option+Enter.
In older Excel <= 2011 (thanks nwinkler), use Command+Option+Enter or Control+Option+Enter.
Some users report the also need the Fn key in addition to the above modifiers.
–jeroen
PS: Later I found out that [WayBack] Beckism.com: Use a linebreak in Excel on Mac also shows the Excel <= 2011 solution Control+Option+Return (note that Return is the same key as Enter).
Posted in Apple, Excel, Mac, Mac OS X / OS X / MacOS, MacBook, MacBook Retina, MacBook-Air, MacBook-Pro, macOS 10.12 Sierra, Office, Office 2011 for Mac, Power User | Leave a Comment »
Posted by jpluimers on 2018/08/10
Because I tend to forget how to use this:
Excel VLOOKUP tutorial with an example spreadsheet & video. The article shows how to lookup the values from one column to use in another worksheet column.
Source: [Archive.is] VLOOKUP Example Spreadsheet & Tutorial | Productivity Portfolio
–jeroen
Posted in Excel, Office, Power User | Leave a Comment »
Posted by jpluimers on 2017/01/09
To match dd/mm/yyyy and mm/dd/yyyy column values in columns C (Date1) and N (Date2), I used these formulas in row 2:
Meaning: Column: Formula: Explanation: Date1Text I =TEXT(C2,”dd/mm/yyyy”) Interpret date text of the original text no matter the Excel settings Date1Value J =DATE(RIGHT(I2,4),MID(I2,4,2),LEFT(I2,2)) Encode the date text to an actual date: dd#mm#yyyy format where # is any separator DatesEqual K =J2=L2 Are the encoded dates equal? Date2Value L =DATE(RIGHT(M2,4),LEFT(M2,2),MID(M2,4,2)) Encode the date text to an actual date: mm#dd#yyyy format where # is any separator Date2Text M =TEXT(N2,”mm/dd/yyyy”) Interpret date text of the original text no matter the Excel settings
Learn how to convert text values into dates in Excel. This lesson covers a range of different scenarios to help you. The comments have even more examples.
Source: Convert a text value into a date in Excel. Learn Microsoft Excel | Five Minute Lessons
Posted in Excel, Office, Office 2007, Office 2010, Office 2013, Power User | 1 Comment »
The Wiert Corner - irregular stream of stuff 