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
    Next

End Sub

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

Advertisements

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.

e.g.
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)
68			
85			
87			
45			
86			
31			
94			  
60			  
45			  
61			 
61			                                      
55			
36			
84

The order of values given in bins_array is immaterial, as illustrated in the example below.
e.g.
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
                                       7