Dummy Essentials: Cell Referencing

Using Large Function In MS Excel 2007

Many of us would have faced this problem of sorting out the top 5 or top 10 or top x numbers from an array. One way to do is to sort it in a descending order. But this will involve additional work of putting those top numbers in a different place. Moreover this will alter the way the data is put up because the data will lose its actual position in the array.

For this we use the LARGE function in excel to select the top numbers in the array. Let us consider the data below for Daily Cash in order to bring out the Top 7 daily cash.

Now in order to bring out the Top 7 daily cash let us write down numbers from 1 to 7 vertically. This will be helpful using the LARGE function.

As you see above we have written down numbers from 1 to 7. Now let us use the LARGE function as shown below. The LARGE function is written as =Large(array,k). That means array is the range from which one needs to bring out the top numbers. Here is how we use and select the array.

Once the array is selected we will put a comma and select the number next to our formula. This will suffice the ‘k’ part of the formula which means if I select 1 it will give me the 1st highest number, when I select 2 it will give me the second highest number and so on. Also make sure that one does the array an Absolute Cell Referencing.

Once this is done. Press enter you will get the fist largest number. Now because we have done the Absolute Cell referencing for the array. Just copy paste the formula upto number 7 and you will get the Top 7 largest Daily cash.

As you see above we have got Top 7 Largest Daily Cash.

Read On 3 comments

Cell Referencing In Excel 2007

Cell referencing is a methodology in which the user can give reference to a particular cell in a sheet or to another sheet or a workbook can give reference to a particular cell in a sheet or to another sheet or a workbook. There are two types of cell referencing: 1. Relative cell referencing 2. Absolute cell referencing Relative cell referencing: while doing a relative cell referencing when you copy to another cell the formula will change relative to that cell address. Absolute cell referencing: while doing an absolute cell referencing when you copy the formula to another cell the formula will NOT change relative to that cell address. The formula remains constant to the cell which the user has made an absolute reference. Below Dummy Essentials explains both the formats of cell referencing. Relative & Absolute cell referencing: Example: If salary of people A to D is to be apportioned into savings on the percentage decided, Then we need to keep the percentage same (absolute) and the salary needs to change for every person (relative). Hence when entering the formula we need to lock the cell address of the percentage of savings. A cell can be locked by using the “$” symbol in the formula.

In the above formula we can see that the cell E5 has been locked which will keep the cell E6 as an absolute cell reference. Now once you copy the formula below the respective salaries will get multiplied to the cell E6. The cell E6 does not change once you copy the formula below.

In a given case if you need to provide the savings of the highest salary to the fixed percentage you need to do an absolute cell referencing i.e. both the cells needs to be fixed.

Now where ever you copy this formula you will get the same value in all the cells. Its primarily because both the cells have been made an absolute reference.

The logic and concept behind making a cell absolute and relative is the row and the column address in the cell. Any row or column can be made absolute and relative. For instance you want to keep the column same but need to change the rows put a “$” sign before the column name only.

In the above picture you can see the “$” sign is only stated. Now once the formula is copied in the next column “F” as well the values do not change. It happens because the column reference has been made a absolute.