DataWitzz is a Dedicated Community for Data Analysis for learners & professionals, and aims at Bringing Together professionals and Learners to Augment Knowledge.
In this blog post, we will try to understand about INDIRECT function of excel. The excel INDIRECT function returns a reference to a range. We can use this function to create a reference that won’t change if rows or columns are inserted in the worksheet. Or, cause it to create a reference from letters and numbers in other cells. The INDIRECT function in excel has two arguments as seen in the below picture:
Indirect function in excel
ref_text: A cell reference or text string (or both), that creates the range reference. The referenced range can be a cell, a range of cells, or a named range.
a1: TRUE or FALSE. Does the reference use A1 reference style? If this argument is TRUE or omitted, the ref_text is A1 style. If the argument is FALSE, the ref_text is R1C1 style.
Indirect function in excel
If the INDIRECT formula in excel refers to a different workbook, that workbook must be open otherwise the formula will return #REF! error.
Implementation and use case:
If we create a simple link to a cell, e.g. =A3 in the below image, and we insert a row above row 3, our formula will automatically change to =A4. To prevent this change, we can use the INDIRECT function.
Indirect function
To demonstrate the difference, we’ll create two formulas –> one that uses a simple link and one that uses a text string with an INDIRECT function in excel.
Indirect function
In cell A3, type a number (505 in the above example).
In cell B2, type the following formula: =INDIRECT(“A3”)
Press the Enter key, and the formula will return the number in cell A3.
In cell C2, type the following formula: =A3
Press the Enter key, and the formula will return the number in cell A3 same as the above example.
To insert a row, right-click on the Row 3 button, and from the pop-up menu, choose Insert.
As shown below, cell B2, which uses the INDIRECT function, now returns 0, because cell A4 is empty. The text string, “A3”, in the INDIRECT formula did not change when the row was inserted.
Cell C2, which contains the link, still returns 505, because its formula has changed to =A4.
Indirect function
Create a Reference from a Cell Value and Text:
In the below example we are trying to fetch data from cell A4 or from some cell whose value will be changing based on some criteria. So we put that criterion in cell C3 and take this in by INDIRECT function as highlighted in red color. The benefit of using this is that we don’t need to adjust or reference every time.IT will be constant.
Indirect function in excel
Create a Reference to a Different Sheet:
One more example of using reference but to a different sheet.
Indirect function
Refer to a Named Range:
In this example, we used NAME RANGE to refer and sum it to get value. Here we named cell B2 to E2 as Excel. And in the formula, we used INDIRECT to capture this in an array. Please note if we use directly Excel INDIRECT then we need to use a double quote otherwise we will follow the example below.
EXCEL Indirect function
I am providing a link to make more clarity on this.
This tutorial shows you how to rank in SQL your data. We will first understand what is ranking function in SQL are then dense_rank SQL. Then also understand how to use row_number in SQL. We will first createSQL table and then insert our data to understand these three functions in SQL i.e. rank function SQL, oracle dense_rank, oracle row_number.
Create table in SQL
First, we will create table SQL emp_name which will have 8 columns, and the first column has a primary key. To create a table in SQL we will use CREATE TABLE statement. We have mentioned Query SQL create table as below. You can read more on how to create SQL table from this link.
After creating the table use describe function in SQL to check if SQL table was created correctly. Check the below image to understand it.
how to use row_number in SQL
INSERT in SQL
As we have created Oracle table now the time has come to INSERT INTO table in oracle. We will use the below SQL query to insert in SQL table. You can read more on SQL INSERT from this link.
INSERT INTO emp_name VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp_name VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp_name VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp_name VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp_name VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp_name VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp_name VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp_name VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO emp_name VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp_name VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp_name VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO emp_name VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp_name VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp_name VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;
SQL SELECT
After INSERT SQL statement check data by using SQL SELECT statement.
Let’s first understand rank over SQL. How it works and its SQL syntax.
RANK() OVER ([ query_partition_clause ] order_by_clause)
In the above oracle query, we used first RANK() to tell that we are ranking the data then OVER with query_partition_clause means by which columns partition by SQL will be done. And in the last order_by_clause which will do ascending order or descending order by which column.
SELECT empno,
deptno,
sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal) AS myrank
FROM emp_name;
Now in the final rank SQL we can see we partitioned our data by deptno and order by sal. It means SQL column data will be arranged by deptno first then sal column data will be rank in SQL. we have not provided anything for ORDER BY which means it will be arranged in ascending order. Now see the final result of this SQL query in the below image.
what does rank mean
In the above image, we can see that column DEPTNO is arranged first and the values in SAL ranked after that. One important point to be noted here is for DEPTNO 30 the salary of 1250 ranked twice as 2 but after that rank value 3 got skipped followed by 4. So we can conclude that if multiple no. repeats then SQL rank will remain same for them and after that new values will number of repetition plus number before repetition.
SQL dense_rank
Now it’s time to understand dense_rank oracle and how it works. But before that let’s understand its SQL syntax.
we can see that the SQL syntax for dense_rank is the same as SQL syntax for oracle SQL rank. Then what is difference between rank and dense_rank? To know that let’s understand it by running the below SQL query.
SELECT empno,
deptno,
sal,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) AS mydenserank
FROM emp_name;
SQL ranking
In the final result, we can see it works like SQL ranking function but instead of skipping repeated numbers, it assigned next number after using the same number for repeated numbers. for DEPTNO 30 and SAL 1250 ranking value 2 was used. But after that value 3 starts means it didn’t skip 3. This is the main difference between rank and dense_rank in SQL.
SQL row_number
Now in the final step, we will understand how row_number in SQL works and the difference between rank dense_rank and row_number in SQL. Syntax for row_number in SQL is given as below.
The indirect function in excel returns a reference to a range. We can use this function to create a reference that won't change if row or columns are inserted in the worksheet. Or, cause it to create a reference from letters and numbers in other cells. The INDIRECT function has
two arguments:
ref_text: A cell reference or text string
(or both), that create the range reference. The referenced range can be a
cell, a range of cells, or a named range.
a1: TRUE or FALSE. Does the
reference use A1 reference style? If this argument is TRUE, or omitted,
the ref_text is A1 style. If the argument is FALSE, the ref_text is R1C1
style.
This DataWitzz tutorial will explain you about MATCH function of excel.Let us first start with the functionality MATCH,it searches for a value in an array and returns the relative position of that value. It is built-in function of excel and will come under lookup/reference category. Syntax:=Match(lookup value,array in which we want to find the position,[Match_Type]) Match_Type is optional and three values can be used as mentioned below:
COUNT is very important function in excel and it is being used very frequently.There are five COUNT(count,counta,countblank,countif,countifs) functions which is being used in excel.I will explain every function one by one and have also shared a video with full explanation.
Count:
This function counts the number of numeric entries in a list. It will ignore
blanks, text and errors.
A Brief History of Microsoft Excel Journey of Excel started in 1987 with Excel 2.0 as the first version for Windows. There never was an Excel 1.0 for DOS or Windows.Microsoft released the first version of Excel for the Macintosh on September 30, 1985, and the first Windows version was 2.0 (to synchronize with the Macintosh version 2.2) in November 1987. Excel 3.0 launched in 1990's and it included tool-bars, drawing capabilities, outlining, add-in support, 3D charts among the other features. Excel 4.0 launched in 1992 and it introduced auto fill options in Excel. Excel 5.0 launched in 1993 includes VBA and it had the ability to automate the tasks in excel and has the capacity to create user defined function(UDF) for use in worksheet. Excel 95 (v7.0) launched in 1995 and it was more faster and stable than the previous versions.There is no Excel 6.0 Excel 97 (v8.0) was launched in 1997.It introduces the paper clip office assistant,validation and a new interface for VBA developers. Excel 2000 (v9.0) was launched in 1999.It was introduced with the self repairing function of excel and improvement in clipboard function to hold multiple objects at a time. Excel 2002 (v10.0) it was a part of office XP.It added multiple feature but most of them are minor one.The most significant was saving the file while it crashed and recover corrupt workbook file and also capability of background formula auditing which identifies potential errors in the formula. Excel 2003 (v11.0) it was a part of office 2003.Most significant was improved support for XML i.e import and export of XML files and mapping the data in specific cells of worksheet.Also a new "list range" feature was introduced. Excel 2007 (v12.0) it was released in 2007.It introduces major changes(upgrade) from the previous version.The following features were introduced:
