This article basically compares excel’s vlookup and sumif array functions and explains which one to use, when and why?

Read the rest of this entry »

Popularity: 34%

Suppose you have the marks of each student and you want to generate a frequency distribution ie total number of students who obtained marks within a given interval.
You can do this using either Excel frequency function or by applying logic.
One of the con of using set functions like “Frequency” is that you have to familiarize yourself with the syntax first.
Like the syntax of the Frequency function is:
Frequency(Data_Array,Bins_Array)
So you should know what are data and bins array.

Data_array is an array of or reference to a set of values for which you want to count frequencies. Bins_array is an array of or reference to intervals into which you want to group the values in data_array.
The following example would make it clear.
Here is a list of marks and the interval for which you need a frequency.

So you want to find out how many students got marks below or equal to 20, how many got between 21 and 30 and so on.

Since it is array formula, so we first need to select the cells where we want the frequency i.e. E5:E10, then enter the data and bin array and hit CTRL+SHIFT+ENTER.

So, 2 students got marks below or equal to 20, 2 got between 21 and 30 and so on.

We can also do this using SUM(IF()) array formula.

This function uses two criteria and “* “in array formula denotes the second condition. So it says sum column B if marks are greater than 0 and less than or equal to 20. As you would notice, the answer is same using both options.

This function can also handle multiple frequencies which “Excel frequency function” can not. For eg. if 3 students got the same score 12 and 4 students got 47. Then if we want to do the above exercise using frequency function we will have to enter the same marks thrice or four times whatever be the case. Whereas if we use the above mentioned SUM(IF()) array formula we can simply enter these in column B as shown below:

Popularity: 23%