How to create a name range in mac excel 2013
![how to create a name range in mac excel 2013 how to create a name range in mac excel 2013](https://howtech.tv/wp-content/images/001748/07.jpg)
This will open a ‘PivotTable’ dialog, select the data range and check the checkbox that says ‘Add this data to Data Model’ and finally click ‘Ok’ button.
![how to create a name range in mac excel 2013 how to create a name range in mac excel 2013](https://i.ytimg.com/vi/mqF8isv6qAA/maxresdefault.jpg)
![how to create a name range in mac excel 2013 how to create a name range in mac excel 2013](https://www.techonthenet.com/excel/pivottbls/images/name2007_001.png)
The integration of power pivot with excel (known as Data Model), has provided some powerful features to the users. Method – 3: Using PivotTable (Only works in Excel 2013 and above) This array is fed to the FREQUENCY function which returns how often values occur within the set of data and finally the outer IF function sets each unique value to 1 and duplicate value to FALSE.Īnd the final result comes out to be 4. So, in our case the resulting array would be like: The resulting array contains a set of numbers combined with False for the blank cells. So, we are excluding the empty cells with data "". The reason IF function is required because MATCH will return a #N/A error for empty cells. If there are any values that are duplicate, then MATCH will return only the position of the first occurrence of that value in the data range.Īfter the MATCH function, there is an IF Statement. Starting from inside, the MATCH function in this formula gives us the first occurrence or position number of each item that appears in the data range. So, here our first objective is to convert the values into a set of numbers. This formula uses Frequency function to count the unique values, but the problem here is that FREQUENCY function is only designed to work with numbers. Let’s Try To Understand How This Formula Works: Note: This is an array formula so, after writing the formula press ‘Control-Shift-Enter’ and the formula will get surrounded by curly braces as shown below. Here, ‘data’ represents the range that contains the values.Īnd ‘firstcell’ represents the first cell of the range. = SUM( IF( FREQUENCY( IF(data "", MATCH(data,data,0)), ROW(data) - ROW(firstcell) +1),1)) To fix this, you can use the following variant of the above formula that ignores the blank cells: In such cases the formula throws a ‘divide by zero’ error.īecause, CountIF function generates a ‘0’ for each blank cell and when 1 is divided by 0 it returns a divide by zero error. This formula woks nicely on a continuous set of values in a range, however if your values contain empty cells, then this formula fails. Read these articles for more details on CountIF Function or SumProduct Function. This means that, if a value appears 4 times in the range then its value will become as 1/4 = 0.25 and value that is present only once will become 1/1 = 1įinally, the SumProduct formula adds up all the elements in the array and returns the final result. This modifies the result of CountIF formula such that values that only appear once in the array become 1 and the numbers corresponding to duplicate values become fractions corresponding to the multiple. Next, the results of CountIF formula are used as a divisor with 1 as numerator. The result is an array of numbers and in our example it might look something like this. The CountIF function looks inside the data range (B2:B9) and counts the number of times each value appears in data range.
Let’s first understand the role of inner CountIF function. This formula consists of two functions combined together. ‘data’ – data represents the range that contains the values