Easy, but I always forget:
= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440
–jeroen
via datetime – Round time to nearest 15min interval in Excel – Stack Overflow.
Archive for the ‘Excel’ Category
datetime – Round time to nearest 15min interval in Excel – Stack Overflow
Posted by jpluimers on 2016/10/14
Posted in Excel, Office, Power User | Leave a Comment »
How to group by date in excel? – Super User
Posted by jpluimers on 2016/10/10
Sometimes you don’t recognize how easy it is until you see it:
Add a column that is of type date with value =FLOOR(A2,1) .Then Insert -> Pivot Table. Then drag the date column on the right into the Rows box, and also drag it into values box. Done.
Thanks David d C e Freitas!
via How to group by date in excel? – Super User.
Share this:
Posted in Excel, Office, Power User | Leave a Comment »
How can I combine multiple nested Substitute functions in Excel? – Stack Overflow
Posted by jpluimers on 2015/11/20
You can’t: How can I combine multiple nested Substitute functions in Excel? – Stack Overflow.
Share this:
Posted in Excel, Office, Office 2007, Office 2010, Office 2013, Power User | Leave a Comment »
How to aggregate (count/sum/average) cells and ignore the #div/0! ‘s – via: list of functions by Excel version
Posted by jpluimers on 2015/11/19
I bumped into a #DIV/0! result for average functions when processing large sets of data.
It is actually very easy to spot the error in small results, sets, but hard in big ones, as you cannot see the #DIV/0!
So there are average functions that can ignore certain outcomes. COUNT already does that (there is no COUNTIF), the others have a *IF equivalent, but not in all Excel versions:
- COUNT – 2003+
- COUNTIF – 2003+
- COUNTIFS – 2007+
- SUM – 2003+
- SUMIF – 2003+
- SUMIFS – 2007+
- AVERAGE – 2003+
- AVERAGEIF – 2007+
- AVERAGEIFS – 2007+
- MAX – 2003+
- MIN – 2003+
Note there is a small SUMIF/SUMIFS/AVERAGEIF/AVERAGEIFS in Excel 2010 (not in 2007, and maybe not in 2013) glitch when the criteria are in a different sheet.
The seemingly easy workaround of summing columns A and B, then doing the division fails: it returns different results as it forgets to ignore faulty rows:
SUM/AVERAGE versus SUMIF/AVERAGEIF (click to enlarge)
Leermomentje (techable moment comes close)…
–jeroen
via:
- How to sum cells and ignore the #div/0! ‘s ? – Microsoft Community.
- List of all functions (by category).
- List of ALL Excel Functions by Version – Super User.
Share this:
Posted in Development, Excel, Office, Office 2003, Office 2007, Office 2010, Office 2013, Power User, Software Development | 2 Comments »
Excel: get content of a cell given the row and column numbers (ADDRESS, INDIRECT, ROW, COLUMN)
Posted by jpluimers on 2015/08/28
A while ago, I needed to do calculations on partially absolute cell references: for some number of rows, the cells needed to be fixed to the top row of each row group.
For a pure absolute cell reference, you’d prepend a dollar sign to the row or column of a cell. So A1 would become $A1 (to make column A absolute), A$1 (to make row 1 absolute) or $A$1 (to make both column A and row 1 absolute).
There is a nice short cut function key F4 to do this.
Excel does not have a built-in partially absolute cell reference solution.
To solve this, I used these addressing functions: ADDRESS, INDIRECT, COLUMN, ROW.
For all these functions, the ROW and COLUMN numbering starts at 1 (one) not 0 (zero).
The way I solved it was to added the below columns (first the reference:heading, then the formula).
The values in the formulas are for ROW 2 (cells A2..XFD2).
- AF: Calculation
- =IF(D2=”*”;INDIRECT(AG2)&X2;””)
- AG: ZLookup
- =ADDRESS($AH2;COLUMN(Z2))
- AH: Row
- =2+12*TRUNC((ROW()-2)/12)
Column AH
Calculates the fixed row of the row group. There are 12 rows per group. ROW numbers start at 1, and there is one heading row, hence the 2+ and the -2.
Without TRUNC, the ROW result would be rounded (that is the default floating point to integer conversion that Excel uses).
There is no need to reference a specific row when calling ROW: if you leave it out, it will return the number of the current row.
Column AG
returns the address of the calculated ROW (from AH) combined with the
Column AF
Depending on the value of the D column, it calculates the outcome by combining
–jeroen
via:
- Excel: get content of a cell given the row and column numbers – Stack Overflow.
- ADDRESS function – Excel.
- INDIRECT function – Excel.
- COLUMN function – Excel.
- ROW function – Excel.
- TRUNC function – Excel.
- Use an absolute cell reference – Get to know Excel: Enter formulas.
- Excel functions alphabetical list.
Share this:
Posted in Algorithms, Development, Excel, Floating point handling, Office, Office 2003, Office 2007, Office 2010, Office 2013, Power User, Software Development | 2 Comments »
Excel: Set the printing order of worksheet pages; printing row/coumn headings
Posted by jpluimers on 2015/08/21
I keep forgetting these two settings, most likely because I hardly use spreadsheets that are both wide and tall.
First the print order
Basically there are two orders:
- Prefer horizontal over vertical (Excel calls this “Over, then down”) so it prints columns first, then rows
- Prefer top to bottom (Excel calls this “Down, then over”) so it prints rows first, then columns
Here is how to do it in Excel 2007 and up:
- Ensure your worksheet is active.
- Click the “Page Layout” tab.
- In the “Page Setup” group, click the “Dialog Box Launcher” on the bottom right of the group (this tiny icon:
). - In the “Page Setup” dialog that appears, click on the “Sheet” tab.
- Under “Page order”, select either of these two options (the preview will change, see the images below).
- Down, then over
- Over, then down
- Click OK to close the “Page order” dialog.
Then the row/colum headings
This is actually much easier, and also works this way from Excel 2007 and up:
- Ensure your worksheet is active.
- Click the “Page Layout” tab.
- In the “Sheet Options” group, under “Headings”, select the “Print” check box.
–jeroen
via:
- Set the printing order of worksheet pages.
- Add and delete page numbers on worksheets.
- Print row and column headings.
Image thanks to Microsoft:
Down, then over.
Over, then down.
Share this:
Posted in Excel, Office, Office 2007, Office 2010, Office 2013, Power User | Leave a Comment »
Locate and reset the last cell on a worksheet
Posted by jpluimers on 2015/08/19
The trick:
When you open the workbook again, the last cell of the data should be the last cell on the worksheet.
Which means that the “Last cell” only changes after a reload.
–jeroen
via Locate and reset the last cell on a worksheet.
Share this:
Posted in Excel, Office, Power User | Leave a Comment »
Excel: replace function is named SUBSTITUTE – via: Stack Overflow
Posted by jpluimers on 2015/08/03
Thanks DarkAjax for answering this:
what you’re looking for is
=SUBSTITUTE(A2,"Author","Authoring")Will substitute Author for Authoring without messing with everything else.
I always forget that SUBSTITUTE is a synonym for replace.
There are REPLACE and REPLACEB, but these replace content of a cell, not of a string.
–jeroen
via Excel: replace part of cell’s string value – Stack Overflow.
Share this:
Posted in Excel, Office, Office 2003, Office 2007, Office 2010, Office 2013, Power User | Leave a Comment »
Download link for Office 2013 Language Pack Options
Posted by jpluimers on 2015/07/20
In case I need to re-download some language related Office 2013 things again: Office 2013 Language Pack Options.
–jeroen
Share this:
Posted in Excel, Office, Office 2013, Outlook, Power Point, Power User, Word | Leave a Comment »
Multiple office versions on one computer: it is possible, but you should not do it
Posted by jpluimers on 2014/08/01
Wow, I didn’t even know this was possible, but I recently came across a few people that had actually done this: run multiple versions of Office on one computer.
Microsoft even has a couple of knowledge base articles on it and indicate it is not recommended (wow!), installation/update orders, and potential issues you will face.
I’ve added the respective office version ranges for each link:
- How to run multiple versions of Office on one computer: 4.5, 95, 97, 2000
- How to run multiple versions of Office on one computer: 4.2/4.3, 95, 97, 2000, XP
- Information about running multiple versions of Office with Office 2003: 97, 2000, XP, 2003
- Information about using 2007 Office suites and programs on a computer that is running another version of Office: 2000, XP, 2003, 2007
- Information about how to use Office 2010 suites and programs on a computer that is running another version of Office: XP, 2003, 2007, 2010
- Information about how to use Office 2013 suites and programs (MSI deployment) on a computer that is running another version of Office: x86 2003, 2007, 2010, 2013; x64 2010, 2013
–jeroen
Share this:
Posted in Excel, Office, Office 2003, Office 2007, Office 2010, Office 2013, Outlook, Power Point, Power User, Word | Leave a Comment »
The Wiert Corner - irregular stream of stuff 