Excel plugin Archives - Office PowerUps

17Sep 2019 by Charlie Nichols No Comments

Excel Functions Missing in Office 2016: IFS, MAXIFS, MINIFS, SWITCH, CONCAT, TEXTJOIN

Excel

[wp_ad_camp_1] [wp_ad_camp_4] Where did the IFS function go? (same for MAXIFS, MINIFS, SWITCH, CONCAT and TEXTJOIN) Can't find IFS function in Excel 2016? Does it seem like you were once able to use the IFS function in Excel with your worksheets? Can't find the IFS function in Excel 2016? Do you have a standalone license (a non-Office 365 subscription) of Office? Do your function names now resolve to "_xlfn.IFS"? (same for MAXIFS, MINIFS, SWITCH, CONCAT and TEXTJOIN) In looking at various posts online, one user notes in his version 16.0.6568.2025 the IFS function worked. After an update from Microsoft to version 16.0.6868.2048 the IFS function no longer worked. (Lost new functions like "ifs" & "switch" & "maxifs" after upgrade to 16.0.6868.2048 at today) Other posts such as Excel new functions…

12Feb 2017 by Charlie Nichols No Comments

Subscription Pricing Now Available

Excel

Subscription Pricing Now Available for Excel PowerUps Premium Suite Since the Excel PowerUps is an add-in, it needs to be installed on each PC that needs access to the new or enhanced functionality provided by the add-in. Volume purchase discounts were a way to get a break on the overall cost of installing on multiple PCs. Now, several annual subscription options have been made available to help lower the initial cost and better support installation on multiple PCs at a lower cost. Note: This does not affect existing license holders as there is no change to the perpetual licenses and ability to update to newer versions. Solo Artist Are you the only one among your colleagues that needs or uses the Excel PowerUps add-in? The Solo Artist subscription tier is…

03Jun 2016 by Charlie Nichols No Comments

IFS Function in Excel 2016, 2013, 2010 and 2007

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] You can add the IFS Function to Excel 2016, 2013, 2010, and 2007 Excel 365 or Excel 2019 introduced a new function called IFS. You can add an IFS function in Excel 2016, 2013 or your copy of Excel 2010, or 2007 with the Excel PowerUps add-in. This IFS function in Excel 2016 (or earlier) allows you to specify a series of conditions easily in a single function without having to nest several IF functions. For example: =IF([test], IF([test2], IF([test3], IF([test4],[value4_test4_true],[value4_test4_false]), [value3_test3_false]), [value2_test2_false]), [value_test_false]) Each condition of the IFS function is followed by the value to be returned if the condition is true. The value returned will be for the first condition that is true. You can also specify a default value to be returned if none of the…

21May 2016 by Charlie Nichols No Comments

TEXTJOIN, SWITCH, IFS, MAXIFS, MINIFS, CONCAT Available for Excel 2013, 2010, 2007

Excel

[wp_ad_camp_1] [wp_ad_camp_4] New Functions Available for Excel 2013, 2010, 2007 The following functions are available for Excel 2013, Excel 2010, and Excel 2007 in the Excel PowerUps Premium Suite. CONCAT IFS MAXIFS MINIFS SWITCH TEXTJOIN These functions were introduced in a recent update for Excel 2016 and are now available for Excel 2013, 2010, and 2007 in the Excel PowerUps Premium Suite. In addition to these six functions, the Excel PowerUps Premium Suite also includes 50 functions that were added to Excel 2013. Similarly, these are available for Excel 2010 and Excel 2007. CONCAT The CONCAT function is similar to the CONCATENATE function. In addition to text strings, you are able to select multiple ranges of text to concatenate them. IFS The IFS function is a option to creating a bunch…

04Nov 2015 by Charlie Nichols No Comments

Concatenate a range of cells in Excel – Easily

200 level, Excel

[wp_ad_camp_1] Concatenate a Range of Cells in Excel Concatenate a range of cells in Excel without having to individually select every cell that you want to concatenate. The pwrCONCATENATE function (part of the Text Analyzer Assistant in the Excel PowerUps Premium Suite) lets you select a range of cells to concatenate. Additionally, you can add a delimiter between the concatenated cells if you wish. Also, you have control over whether to concatenate rows first or columns first. You can have empty cells in the range. They'll just be skipped. For example, you can use the following formula. =pwrCONCATENATE(E3:G30, " ", "COL") This will concatenate the range of cells between cells E3 and G30. Each cell will have a space (" ") character inserted between and the concatenation will go in the…

21Sep 2015 by Charlie Nichols No Comments

Regular Expressions in Excel – Test Tool

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Regular expressions in Excel Regular expressions in Excel are made easy with the pwrREGEXMATCH function. As a demonstration of the function, the example file pictured below is set up to evaluate a regular expression entered in cell D2 against text entered in cell D4. The result is contained in cell D8. The formula used in Excel (cell D8) is the following: =pwrREGEXMATCH(D4, D2) That's it. Download the example worksheet (link below) or just create one on your own. Using this, you can easily try out different regular expressions against some target text to massage into the perfect regex for your need. [caption id="attachment_3430" align="aligncenter" width="908"] Sample Excel template demonstrating use of regular expressions in Excel thru the pwrREGEXMATCH add-in function[/caption] Download the sample workbook here.

03Feb 2015 by Charlie Nichols No Comments

How to compare two strings in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to Compare Two Strings in Excel for Similarity Excel comes loaded with several useful text or string comparison functions. Out of the box, Excel provides a function named EXACT that will return whether two strings are exact matches. That's great, unless you actually need to find strings that are really close or very similar. The good news is when you want to compare two strings in Excel you aren't limited to exact matches only. Using a function such as pwrSIMILARITY you can compare two strings and get a rating back (a percentage) that indicates the degree of similarity between the two strings. You actually have a great deal more flexibility with pwrSIMILARITY. For example, the EXACT function is case sensitive so comparing "Abc" and "abc" would return…

22Jan 2015 by Charlie Nichols No Comments

How to Generate Random Text in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] A Little About Lorem Ipsum "Lorem Ipsum" text is commonly used in the printing or design industry as dummy text used as a placeholder for "real" text. The Lorem Ipsum text is originally from Latin literature that is over 2,000 years old. It is not Latin-looking gibberish as often believed. Generate Random Text in Excel You can use the pwrRANDOMTEXT function in Excel to generate random text. The random text generated will be based on the Lorem Ipsum text commonly used as filler. This function is available as part of the Random Data Assistant contained in the Excel PowerUps Premium Suite add-in for Excel. In order to use this function you simply use it in your worksheet as you would any other function. Examples If you want to…

21Oct 2014 by Charlie Nichols No Comments

Return Random Value from List in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Two Functions to Make Returning Random Data Easy Using the pwrRANDFROMSET and pwrRANDFROMRANGE functions it's easy to get a random value back to your formula. Random Values from a Set The pwrRANDFROMSET function returns a value from a set defined within the function while the pwrRANDFROMRANGE function returns a value from a defined range in Excel. If you wanted to return a random value from a set of colors you can use the equation below as an example. =pwrRANDFROMSET("red", "pink", "green", "blue", "black", "orange", "yellow") This will return a random value from the set of colors in the formula. Random Values from a Range The pwrRANDFROMRANGE function returns a value from a range of cells. This range can be multiple columns and rows as long as they are…

24Sep 2014 by Charlie Nichols No Comments

How to Collect Excel User Input and Return to Formulas

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Collecting Excel User Input Using the Excel PowerUps Premium Suite you can easily collect basic user input and return that input to your formula. You can use the function called pwrINPUTBOX to prompt the user to provide some information. Since this is a function made available to you in Excel, you don't need to write any code. Example Here's an example. Imagine you need to have the user provide some information if a value is beyond a given threshold. Let's say if a key performance metric falls below a specific level you want to capture a brief note regarding why from the user. So let's say we want to capture an explanation if the KPI falls below 85%, and that the KPI is in cell A1. The formula…