by ·0 Comments

Using Excel IFS and SWITCH functions.


Using Excel IFS and SWITCH Functions

          Making Rent vs. Buy decisions always seemed like a no-brainer to me. If you hold on to your purchase long enough, you will be saving money: be it a car or a computer software. Case in point: it costs only $ 109.99 to purchase a license for Excel 2016 vs $69.99/year to lease Office 365 Personal, which includes Excel, as well as other Office products. Assuming you only need to use one product that truly matters, and you will use it for more than 19 months, buying is cheaper than renting. The longer you use the product without upgrading, the more money you save. This held true until February of this year, when Microsoft introduced 6 new functions available exclusively to Office 365 subscribers: TEXTJOIN, CONCAT, IFS, SWITCH, MAXIFS and MINIFS. This addition alone, coupled with introduction of Funnel charts might steer more users towards the subscription model. In this post we will review the IFS and SWITCH functions. Let’s say goodbye to nested if functions , we have already discussed on this blog.
Continue reading

by ·0 Comments

Top 15 Excel shortcuts for 2015.


Using Excel keyboard shortcuts - Shift+F2 - Create/edit a cell comment

          To celebrate the end of this year, let’s look at some of the best Excel shortcuts that I have been sharing on Twitter almost every Tuesday in 2015 – #TuesdayShortcuts. Adding them to your Excel repertoire shall increase your productivity. It might be hard to remember these shortcuts, but the more shortcuts you know, the more time you will save. If you use a multiple screen set up, your time savings will even be greater, since you will save a lot of unnecessary hand movement. Still not convinced? What about health benefits? No, using shortcuts will not help you shed those unwanted pounds, but they might spare you the pain (literal pain, not an imaginary one) of this unpleasant thing called tendinitis – inflammation of your wrist’s tendon. And that mental effort of exercising your memory, shall probably help you stay mentally fit as you grow older and want to keep up with the newest features of Excel v50… You are welcome!

Continue reading

by ·0 Comments

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

by ·0 Comments

AT&T iPhone6 Upgrade: 2-Year contract vs. Next plans

iPhone6Upgrade Options with AT&T

         

         If you are getting ready to upgrade to iPhone6 through AT&T, you might be in for a surprise or two. The first surprise is that back in July of last year, AT&T changed its phone upgrade eligibility date from 20 months to 24 months.

         Using a 2-year contract option, this change most likely means that you did not qualify for an iPhone6 pre-order last Friday, and will probably have to wait till after September 19th’s official release to place your upgrade order. If you are not willing to wait, you can pay AT&T an upgrade surcharge ($40 if your upgrade date is later this month) to upgrade immediately.

         Alternatively, you can take a completely different route, and go with a Next 12 or Next 18 installment plans and forget about service contracts and early termination fees altogether. At first glance, it’s pretty hard to follow AT&T’s case for the Next plans.

         In this post I will present my own calculations and conclusions pertaining to different scenarios when either a 2-year contract or one of the two Next plans works better. Since Samsung Galaxy S5 is offered at the same price as iPhone6, these calculations will work for all of the Android lovers out there as well.

Continue reading