Posts

Normally, we would use the UNIQUE function to extract unique values. However, UNIQUE is not case-sensitive so it won’t work in this situation. Thevrepresents the value of each element in the array.Calculationis the formula logic that creates the final accumulated result. At each new value,v, the custom LAMBDA function checks ifvis already in the accumulator,a. Ifvis already ina, the current value ofais returned. Ifvis not already present ina, the function combinesaandvwith theVSTACK function....

However, with the introduction of theVSTACK function, the solution is straightforward. UNIQUE function TheUNIQUE functionmakes it very easy to extract unique values from a range. The challenge in this example is to provide more than one range to UNIQUE at the same time. The solution is to use the VSTACK function to combine ranges first, before invoking UNIQUE. This is done with the VSTACK function. VSTACK function TheVSTACK functioncombines arrays or rangesverticallyinto a singlearray....

Explanation This example uses the UNIQUE function together with the FILTER function. For more examples of operators in formula criteriasee this page. UNIQUE then removes duplicates, and returns the final array: UNIQUE and FILTER are dynamic functions. If data in B5:B16 changes, the output will update immediately. The output from FILTER is dynamic. If source data or criteria change, FILTER will return a new set of results. This makes FILTER a flexible way to isolate and inspect data without altering the…

Explanation This example uses the UNIQUE function together with the FILTER function. UNIQUE then removes duplicates, and returns the final array: UNIQUE and FILTER aredynamic functions. If data in B5:B16 or C5:C16 changes, the output will update immediately. The output from FILTER is dynamic. If source data or criteria change, FILTER will return a new set of results. This makes FILTER a flexible way to isolate and inspect data without altering the…

Explanation This example uses the UNIQUE function together with the FILTER function. UNIQUE then removes duplicates, and returns the final array: UNIQUE and FILTER aredynamic functions. If source data changes, output will update immediately. The output from FILTER is dynamic. If source data or criteria change, FILTER will return a new set of results. This makes FILTER a flexible way to isolate and inspect data without altering the…

Explanation Although FILTER is commonly used to filter rows, you might also filter columns. The trick is to use anincludeargument that operates on columns instead of rows. Note: thenamed rangedatais used for convenience only; a regular range reference will also work fine. This array is delivered to theUNIQUE function, which can automatically extract unique values from data with adjacent columns. The SORT function is optional and can be removed. This is a nice example ofnestingone function inside another....

The input data appears in column B. Note: the formula explained below builds on morebasic examples explained here. In addition, OFFSET has an optionalwidthand heightarguments, which specify the size of the reference to be returned. The next argument isrows, which indicates the desired row offset from the starting reference. TheROW functionis used to get the row number for cell D1. We start with D1, because we want to start with the number 1....

UPPER function takes just one argument,text, which can be a text value or cell reference. UPPER changes any lowercase characters in the text value to uppercase. Numbers, punctuation, and spaces are not affected. UPPER will convert numbers to text withnumber formattingremoved. Numbers, punctuation, and spaces are not affected. UPPER Function The Excel UPPER function converts a text string to all uppercase letters. Numbers, punctuation, and spaces are not affected. PROPER Function The Excel PROPER function capitalizes each word in a given text string....

I first saw a reference to this data on the interestingNews, in Datasite. Also, unlike column charts, they can handle way many more data points without becoming cluttered. The example up top actually plots 3 more years of data, going back to 1999. It would be interesting to show the average street price of heroin during this same period. If you have a source for that data, let me know in a comment below....

However, a quick disposable chart is a great way to check and explore data. The result is a messy column chart, but there’s a lot of good information here. First, I can see there are over 700 transactions total. Without category labels to work with, the horizontal axis is simply a count of values or rows. Looking at the vertical axis, I can see that most transactions are $500 or lower....

Aused rangein an Excel worksheet is a concept that defines which cells in a worksheet have been used. In any given worksheet, the current used range can impact how many cells are involved in certain calculations. Sometimes, a worksheet will have a used range that is much larger than expected. This can cause performance problems and other unexpected behavior. One way to “reset” a used range is to delete all unused columns and rows....

The Font tab in Format Cells has more options, but the ribbon has advantages for basic controls. Let’s take a look. The Font tab of the Format Cells dialog box contains all of these options and several more. So, you could use Format Cells for almost all of your font formatting needs. For example, the font menu is just a plain list and only displays six fonts at a time....

Explanation This formula is a good example ofnestingone function inside another. If the value in B5 exists in the range E5:E9, COUNTIF will return 1. If not, COUNTIF will return zero. Excel’s standard behavior is to evaluateanynon-zero number as TRUE, and zero as FALSE. This is because COUNTIF will not accept an array constant as the range argument. Note: The fact that COUNTIF won’t accept an array for the range argument is not widely understood....