17 Excel Functions to use in 2017.


17 Excel Functions for 2017

          Versatility of Excel’s built-in functions is undeniably one of the main reasons behind this program’s popularity. As users, we have the flexibility to compose complex formulas incorporating multiple functions in our solution to achieve substantial gains in productivity. Most people have their own go-to Excel functions, be it: financial, date & time, math & trigonometry, statistics, look up & reference, database, text (manipulation), or logical. We’ve covered some of these functions already, but below is a compilation of 17 relatively under-used Excel functions you might want to add to your professional repertoire in the new year. Fair warning, you might need to have Office 365 version of Excel for all of the functions to work.

          1. PMT
          2. DATEDIF
          3. LARGE
          4. REPT
          5. LEN
          6. TRIM
          7. SUBSTITUTE
          8. TEXT
          9. IFS
          10. CONVERT
          11. NETWORKDAYS
          12. HLOOKUP
          13. INDIRECT
          14. OFFSET
          15. TRANSPOSE
          16. DSUM
          17. RAND
          BONUS: SUMPRODUCT

Continue reading

Summarizing Excel PivotTables with GETPIVOTDATA Function.


Using Excel's GETPIVOTDATA Function

          Have you ever tried to select a cell within Excel’s PivotTable to create a regular link, only to realize that such formula cannot be easily copied over? You might be generating GetPivotData function without realizing it. As this Microsoft’s help page tells us: GETPIVOTDATA function “returns data stored in a PivotTable report. You can use GETPIVOTDATA to retrieve summary data from a PivotTable report, provided the summary data is visible in the report.” Main takeaways are the following: 1) GETPIVOTDATA is a summary function, and 2) it only works with visible PivotTable data. It’s syntax includes: Data_field – required field referencing data field of interest; Pivot_table – reference to any cell or range of cells withing a PivotTable report; and optional Field/Item combination, with text values enclosed in quotations. While, GetPivotData feature is activated by default, you can easily turn it off by selecting the Options menu on the ANALYZE Ribbon, and checking off “Generate GetPivotData” selection:

Continue reading

TIME conversions in Microsoft Excel.


Performing TIME calculations in Microsoft Excel

          We’ve covered a lot of ground with posts on date calculations in Excel: using the DATEDIF function, calculations without the DATEDIF function , and in the most recent post – business day calculations with the NETWORKDAYS funtion. Now it’s turn to perform some TIME conversions and calculations in Excel. We will use TIME, TIMEVALUE, NOW, HOUR, MINUTE, SECOND, TEXT, and MOD functions to perform various time operations…

          NOW function displays the current date and time. Depending on your cell formatting, it might be date only, or if your cell format is General, it could even be serial number equivalent of your current date and time. While this function has no arguments, its syntax still calls for a set of parenthesis: =NOW(). We already know that Excel uses the whole value of 1, in reference to the “beginning of time”, as far as Microsoft is concerned – January 1, 1900. Similarly, today’s date has the value of 42,124 The decimal point value references the fractional time portion of any date. As an example, 0.5 denotes NOON, while 0.75 refers to 6 PM. [0.75*24 = 18] Similarly, one minute, is 1/60th of an hour or 1/1440th of a day, calculating to be 0.069(4). Keep in mind that, whenever date/time value starts with a 0, the date portion has no value, and we are working with the time value only.

Continue reading

Performing business days calculations in Excel, with NETWORKDAYS function.


Calculate number of whole days between two dates, - NETWORKDAYS function

          We already did some date calculations with a DATEDIF function, as well as without one . However, the limitation of both methods was the fact that they focused on calendar day calculations; in this post, we will perform business day calculations. To accomplish this task, we will need to use NETWORKDAYS, and possibly NETWORKDAYS.INTL functions.

          Following our tradition, let’s turn to Microsoft’s own documentation to introduce the NETWORKDAYS function: “Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.”

Continue reading