300 level Archives - Office PowerUps

08Feb 2017 by Charlie Nichols No Comments

Simulating an Event Happening by Using Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] TRUE AND TRUE Isn't Always TRUE From a Boolean perspective, of course TRUE AND TRUE = TRUE. However, sometimes you may want to model something in a simple manner that may be much more complex then a couple of inputs. For example, imagine you wanted to simulate whether it was going to snow on a given day. Perhaps you have one value that identifies whether it is cloudy or not. In another value you have identified whether it is freezing cold outside. If CLOUDY = TRUE, and FREEZING = TRUE, you still can't say SNOW = TRUE. The final factor may be whether or not there will be any precipitation on that day. Lets say there was a 60% chance of precipitation. The chance of snow would then be…

15Sep 2016 by Charlie Nichols 2 Comments

How to Create a Burn Down Chart in Excel (Excel Burndown Chart)

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] What’s an Excel Burndown Chart For? In agile or iterative development methodologies such as Scrum an Excel burndown chart is an excellent way to illustrate the progress (or lack of) towards completing all of the tasks or backlog items that are in scope for the current iteration or sprint. Excel can be an effective tool to track these iterations, or sprints, as well as report on the progress using a burndown chart in Excel.We’ll break the task of creating an Excel burndown chart into four main groups. Set up the sprint’s information.Set up the work backlog.Set up the burn down table.Create the chart. Setting up the Sprint Information for Your Excel Burndown Chart The key thing the Burn Down chart will show is a plot of the amount of…

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…

01Jun 2016 by Charlie Nichols 1 Comment

SWITCH Function for Excel 2013, 2010, and 2007

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Now Available: SWITCH Function for Excel 2013, 2010, and 2007 Excel 2016 introduced a new function called SWITCH. You can add a SWITCH function to your copy of Excel 2013, 2010, or 2007 with the Excel PowerUps add-in. The SWITCH function allows you to specify an expression and a series of values and results. Each of the values are compared to the expression and the first one to match has its corresponding result returned. You can also specify a default value to be returned if none of the values matches the expression. If you have ever have to fight keeping track of nested IF statements, you'll definitely appreciate the simplicity of the SWITCH function. Example of the SWITCH function This is an example of the SWITCH function in Excel…

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…

24Sep 2014 by Charlie Nichols 2 Comments

How to Show an Excel Message Box or Alert without Code

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Show an Excel Message Box Occasionally, you need to alert the user of a specific condition or value in your Excel workbook. For example, if a value gets higher or lower than a desired level you may wish to make that more apparent to the user. You can use the Conditional Formatting features in Excel to, say, make the field bright red. However, you would be relying on the user to view that tab or potentially scroll to that part of the worksheet to actually see the value in the first place. Alternatively, you can pop up a message box to alert the user of the condition or value. One method is to use the embedded scripting system within Excel and write some custom code to pop up…

16Aug 2014 by Charlie Nichols 1 Comment

Excel Burndown Chart Template

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] A basic Excel burndown chart template is available. Usage of the template can be found in the How to Create a Burn Down Chart in Excel article. Download File Download the Excel burndown chart template here. There you go.

26May 2014 by Charlie Nichols No Comments

Excel Add-in Update: Version 1.6.4 Adds New Capability

300 level, Excel

A new version of the Excel add-in Excel PowerUps Premium Suite has been released. Like other releases, this Excel add-in version has a few new and enhanced functions along with some bug fixes and tool additions. Go to the download page. Enhanced Functions pwrFILTERXML pwrFILTERXML has been extended to directly open XML files or call XML web services. This allows pwrFILTERXML to work without having to first call the WEBSERVICE function in another cell. This also means you will not be limited by Excel's 32k per cell character limit. This is key for utilizing web services that return more than 32k of text. pwrWEBSERVICE pwrWEBSERVICE has also been extended to directly open XML files. This allows you to reference local XML files. You will still be limited by Excel's built-in 32k limit per…

24May 2014 by Charlie Nichols No Comments

Work Beyond Excel’s 32k Per-Cell Character Limit

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] The Character Limit in Excel can be Limiting - Don't let it be There is a character limit in Excel. Excel has a character limit (per cell) of 32,767 characters. And in most cases this is ample space for your needs. Sometimes You Need to Work with XML Blocks Larger Than 32k. You may have a need that resembles one of the following scenarios. You need to consume a web service that returns more than the 32k limit. You need to open a local XML file, and that file may also be larger than the 32k character limit. Meet New and Improved WEBSERVICE and FILTERXML Functions Each of the functions, including their “power” equivalent functions of pwrWEBSERVICE and pwrFILTERXML, has been enhanced in the latest version of the Excel…

17Dec 2013 by Charlie Nichols 1 Comment

Excel WEBSERVICE Function, plus ENCODEURL and FILTERXML Make a Useful Trio of Web Functions

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Now, Add This New Excel 2013 Trio of Functions to Your Version of Excel Excel 2013 introduced a trio of new functions that make it easy to extend your worksheets to use XML web services. The Excel WEBSERVICE function, ENCODEURL function, and FILTERXML function work nicely together to make the task of working with web services straightforward within Excel 2013. This functionality is made available in Excel 2010, Excel 2007, and Excel 2003 in the Excel PowerUps Premium Suite add-in for Excel. This can be downloaded for free. There are many posts and videos posted on YouTube illustrating how you may use the features and seeing how many there are give you a sense for how popular of an addition these functions are to Excel. The sections below…