Posts Tagged ‘Array Formula’

At times we need to change cell values in a range based on a calculation. The most common way of doing it is using loop.

e.g.  We have numeric data in range A1:B10 of Sheet1 and we need to multiply all cells of this range by 2.

Sub Test()

    Dim rngData     As Range
    Dim rngCell     As Range

    Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")    
    For Each rngCell In rngData
        rngCell = rngCell * 2

End Sub

In the above example, Evaluate can be used instead of looping through cells, it works faster than a loop. (more…)

FREQUENCY Function in Excel

Posted: April 16, 2013 by Transformer in Excel
Tags: , , ,

Frequency function calculates the number of values in a dataset that fall within a particular interval. Generally it is used to group the values of a dataset in intervals.
It requires two arguments, the first one is a dataset and the other is a list of intervals.

                         Frequency (data_array, bins_array)

Data_array: it is an array or a range of values for which you want the frequency (Dataset)
Bins_array: it is an array or a range of intervals based on which you want to group the data_array (Intervals)
This function returns an array of numbers (count of frequencies). The size of the returned array is one more than the size of bins_array, the extra element being the count of elements in the data_array that are greater than the max value of the bins_array. It must be entered as an array formula.

We have a list of students’ marks and we want to group them in intervals as given below. Here, the list of marks is the data_array and bins_array is the upper limits of the intervals.

Scores	 Interval   bins_array   Results
30	 <=20        20           0(No element <= 20)   
97	 21-40	     40           4(4 elements between 21 to 40)  
62	 41-60	     60           5(5 elements between 41 to 60)  
45	 61-80	     80           4(4 elements between 61 to 80)  
94	                          7(extra element that tells how many 
32			            elements lie outside the intervals)

The order of values given in bins_array is immaterial, as illustrated in the example below.
In the same dataset above, if we change the order of elements in bins_array then the function returns same values against each element of bins_array as it would have done if the bins_array was sorted.

Unsorted (bins_array)               Results
  40                                   4
  20                                   0
  80                                   4
  60                                   5