Blog - Page 6 of 10 - Office PowerUps

26Aug 2013 by Charlie Nichols No Comments

Date Values in Excel Explained

100 level, Excel

Date values in Excel are numbers In Excel, a date is the number of days since January 1, 1900 starting with January 1, 1900 being “1”. Each date after that, Excel adds one more number to that sequence. So August 26, 2013 is 41512, or 41,512 days since January 1, 1900. The integer part of the number is used for the days. The decimal part of the number is the fractional part of the day -- or the time. So .5 would be 50% of the way thru the day, or 12:00 noon. That makes 41,512.5 to be equivalent to 12:00 noon on August 26, 2013. The date number pattern is a follows. DDDDD.TTTTTT So as you might imagine, adding 3 to the date value of August 30, 2013 results…

21Aug 2013 by Charlie Nichols No Comments

Percentage Change Between Two Numbers

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] I was asked what the Excel function was for finding the percentage of change between two numbers in Excel. It turns out there is no dedicated function for that. That's OK. The formula for calculating the change between two numbers in Excel is really simple (and there is one less function in Excel that you'd need to learn). The formula for % change between two numbers The simple formula is shown below. =(A2/A1)-1 In this example, cell A2 contains the new number and cell A1 contains the original number. The formula will return a value that represents the percentage change between the two numbers. Negative changes will show up as negative numbers. If you want to see the value as a percentage, just click the percent sign button "%" in…

13Aug 2013 by Charlie Nichols 1 Comment

How to Open Excel Files in Separate Windows Automatically

400 level, Excel

I was asked how set up Excel so that opening individual Excel files would open in separate Excel Windows. By default, it seems Excel 2007 and 2010 open separate files in the same instance of Excel. In technical terms, Excel uses MDI instead of SDI. I searched and found several posts that basically said "can't be done" or "not supported". I found some others that outlined some Registry tweaks. They were slightly different in their instructions and a little hard to follow. I tried some of the changes and have documented what has worked for me on multiple systems with different versions of Windows as well as Excel. Setting up your system to open Excel files in separate windows If you have Excel 2007 or Excel 2010, you can…

07Aug 2013 by Charlie Nichols No Comments

Calculator function equivalents in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Excel calculator functions I took a look at a basic scientific calculator I had in a drawer and I thought it might be interesting to show how each of the buttons on the calculator would work in Excel. So below is my simple inventory of the scientific calculator functions and their equivalent functions in Excel. In the examples below, just assume that the value of “x” is in cell A1. Calculator function list Inverse function $latex x^{-1}$ Excel formula(s) Each of the formulas below achieves the same thing in Excel. =1/A1 =A1^-1 Trig functions $latex sin(x)$ $latex cos(x)$ $latex tan(x)$ $latex sin^{-1}(x)$ $latex cos^{-1}(x)$ $latex tan^{-1}(x)$ Excel formula(s) Each of the formulas below achieves the same thing in Excel as the corresponding calculator button above. =SIN(A1) …

30Jul 2013 by Charlie Nichols No Comments

How to convert text to number values in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] In Excel, if you have text that represents a number that is not recognized as a number value some Excel formulas may not work correctly. In order to convert a text number into a number value you have a few options. Convert text to number First, you can just multiply the value by 1. This will force the conversion of the text into a numerical value and return the equivalent numerical value. =A1*1 Next, you can use the VALUE formula. You wind up with the same result. =VALUE(A1) Convert Roman Numeral text to number If your text is a Roman Numeral you can convert that to an equivalent numerical value (in decimal). Excel 2013 introduces a function called ARABIC that will convert roman numerals into decimal value equivalents.…

25Jul 2013 by Charlie Nichols No Comments

How to get the formula of a cell in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to get the formula in a cell To get the formula in a cell, the FORMULATEXT function was introduced in Excel 2013. It's as simple as the following. Just reference the cell with the formula you're interested in. =FORMULATEXT(A1) Need to do the same, but in older versions of Excel? If you need to get the formula in a cell in Excel 2003, 2007 or 2010 you can use the pwrFORMULATEXT function. pwrFORUMLATEXT is available in an Excel addin you can download from the link below. You use it the same way as the new function in 2013. =pwrFORMULATEXT(A1) Get the free addin Overall, there are about 50 new worksheet functions in Excel 2013. The addin linked below adds 46 of the 50 functions so your legacy version…

15Jul 2013 by Charlie Nichols 14 Comments

Create a trip calculator with Excel and web services

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Using Excel, you can create a trip calculator that uses web services to provide dynamic information that is up to date. I created this post after reading a post with a similar theme from Glen Gilchrist. In the example below, you'll see that you can create the trip calculator using Excel functions and without needing to write any code. Note: The illustrations below will work with Excel versions 2003 - 2010 by installing the Excel PowerUps Premium Suite. If you have Excel 2013, you can create this trip calculator without the add-in -- just remove the "pwr" prefix from the function names in the examples below. Excel 2013 already has built in WEBSERVICE and FILTERXML functions! Here's the gist of how we'll put this together For the calculator,…

09Jul 2013 by Charlie Nichols 66 Comments

Send email when specific field changes in SharePoint

SharePoint, SharePoint Designer

[wp_ad_camp_1] [wp_ad_camp_4] How to send email if a specific field changes in SharePoint (versus just any field in the list item) You need to send email when specific field changes in SharePoint, based on a field changing instead of just any field in the list item. SharePoint has provision for users to set up alerts when items in a SharePoint list change. However, sometimes you only want an alert when a specific field changes. For example, you may only want to know when a field such as Target Delivery Date changes. You can set up this email trigger by performing the following (each described more fully further below). 1. Set up a “helper” column that will serve as the basis for keeping track of any pre-change state. 2. Set up…

02Jul 2013 by Charlie Nichols No Comments

Determine whether an InfoPath form is for a new item or existing

InfoPath

Determining whether an InfoPath form is for a New or Existing item Do you need to have a slightly different InfoPath form view depending whether you are showing an existing list item or a new entry? This turned out to be pretty straightforward. In my case I just want to show one of two submit buttons. One that said “Submit Changes” and the other that said “Submit New”, depending on whether this was a new item or existing. I created a new formatting rule for each of the items. On one of them, I triggered the rule when the created date was empty, which indicated that the item hadn’t been created (aka “new”). In the formatting section, I just checked the box for Hide this control. This is what I needed…

25Jun 2013 by Charlie Nichols 40 Comments

Show the most recent comment from SharePoint text fields instead of “view items”

SharePoint

[wp_ad_camp_1] [wp_ad_camp_4] How to show the most recent comment instead of ‘view items’ in SharePoint lists When you set up a SharePoint list you can create a text column that keeps a history of previous entries. You enable this by selecting Yes for the Append Changes to Existing Text column setting. This is handy for a comments field where you need to keep track of user comment history, for example. The problem I’ve had is that when you put this column in a SharePoint view you wind up seeing a hyperlink with the text “view items”. This is not helpful. Further, the link always shows up – even when there are no comments or text in the column. This just makes for way too much clicking back and forth. The…