200 level Archives - Office PowerUps

14Nov 2018 by Charlie Nichols No Comments

Get IFBLANK and IFZERO added to Excel

[wp_ad_camp_1] [wp_ad_camp_4] Do you wish you had either IFBLANK or IFZERO available in Excel? Sometimes it is handy to be able to test for a blank cell or a zero value in the same manner you would use the IFERROR function. Instead of doing something like this: =IF(SUM(A1:Z1)=0,"The value is zero",SUM(A1:Z1)) You can now do this: =pwrIFZERO(SUM(A1:Z1), "The value is zero.") As your test value or expression gets more complex, you can see how this simpler formula would be much easier to read, write, and maintain. Similarly, you can test for a blank cell using the pwrIFBLANK function. For example: =pwrIFBLANK(A1, "The cell is blank.") instead of: =IF(ISBLANK(A1), "The cell is blank", A1) Check out the pwrIFZERO and pwrIFBLANK functions in the Excel PowerUps Premium Suite add-in. You'll get the…

26Jan 2016 by Charlie Nichols No Comments

How to pick a random value from a range of cells in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to pick a random number from a range of cells Here's how to pick a random number from a range of cells in Excel. And actually, it doesn't need to be a number. If you have a range that contains data (names, numbers, dates, whatever) and you need to randomly select a value from that range, you can use the pwrRANDFROMRANGE function. It's as easy as selecting the range. See below. Example Let's say you have data contained in a range of cells between A1 and D200. Let's say this range contains a set of names and you want to randomly select one. You would use the following formula in Excel. =pwrRANDFROMRANGE(A1:D200) If your data is contained in a single column. No problem. pwrRANDFROMRANGE works just as well with…

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…

05Oct 2015 by Charlie Nichols No Comments

How to fix a VLOOKUP error in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] VLOOKUP error in Excel Ever have this happen? You write your VLOOKUP formula and have one of the following values returned: #VALUE! #NAME? #N/A #REF! Other UNEXPECTED result! Below you’ll find some common pitfalls that cause each of the errors above. VLOOKUP function syntax First, here’s a recap of the usage syntax for the VLOOKUP function in Excel. I’ll be referring to the parts in the explanations below. VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup]) For more detail on the arguments, see the function's documentation page. VLOOKUP errors in Excel #VALUE! error causes in VLOOKUP column_index_number is less than 1 In an effort to retrieve a value that is to the left of the lookup column, you may be tempted to put in a value less than one. However, VLOOKUP only finds…

27Sep 2015 by Charlie Nichols No Comments

Excel Financial Functions tutorial

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Excel as a Financial Tool Excel provides a number of functions to make calculating financial data very easy. You can find a great deal of examples and documentation on each of the functions. For this tutorial, we will use five commonly used Excel financial functions and use them together to answer some common questions. Excel Financial Functions The Excel financial functions that we will use in this tutorial are the following. PMT RATE NPER PV FV Each of these can be use to answer a likely question in the scenario for our tutorial. In this example, we'll consider the purchase of a used car and answer the following questions. What is my payment going to be? If I pay more than the minimum payment, how much earlier would…

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.

07May 2015 by Charlie Nichols No Comments

How to calculate compound interest in Excel

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Compound Interest Compound interest is interest added to the principal and interest earned in previous periods. This addition of interest to the principal is called compounding. Just below you can see how to calculate compound interest in Excel. The formula for calculating compound interest is =PV*(1+R)^N How to calculate compound interest in Excel The formula above is entered in Excel as =PV*(1+R)^N In the formula, PV is the present value. R is the interest rate. And N is the number of periods (such as annually) that you want to compound. Example Suppose you have $1000 to start with. Your present value (PV) would be 1000. If you expect to earn 7% annual interest, your rate (R) would be .07. To see how much you would have after 10…

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…