Tuesday 29 August 2023

INDIRECT function in Excel

 

How to use INDIRECT function in Excel

How to use INDIRECT function in Excel

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:

How to use INDIRECT function in Excel
Indirect function in excel
  1. 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.
  2. 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
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
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
Indirect function
  1. In cell A3, type a number (505 in the above example).
  2. In cell B2, type the following formula:
       =INDIRECT(“A3”)
  3. Press the Enter key, and the formula will return the number in cell A3.
  4. In cell C2, type the following formula:
       =A3
  5. Press the Enter key, and the formula will return the number in cell A3 same as the above example. 
  6. To insert a row, right-click on the Row 3 button, and from the pop-up menu, choose Insert.
  7. 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.
  8. Cell C2, which contains the link, still returns 505, because its formula has changed to =A4.
Indirect function
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
Indirect function in excel

Create a Reference to a Different Sheet:

One more example of using reference but to a different sheet.

Indirect function
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.

Indirect function
EXCEL Indirect function

I am providing a link to make more clarity on this.


Saturday 12 February 2022

TO_NUMBER function in Oracle


How to use row_number in SQL | SQL RANK() function | dense_rank SQL

Contents,Table of Contents, Page Contents [hide]

How to rank in SQL

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 create SQL 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.

CREATE TABLE emp_name (
  empno    NUMBER(4) CONSTRAINT pk_emp PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2)
);

Describe function in SQL

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
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.

INSERT INTO statement
INSERT INTO statement

SQL RANK over

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
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.

DENSE_RANK() OVER([ query_partition_clause ] order_by_clause)

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
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.

row_number() OVER([ query_partition_clause ] order_by_claus

It is very clear from the above SQL syntax that syntax is almost the same. Now let’s understand how to write SQL queries for row_number in oracle.

SELECT empno,
       deptno,
       sal,
       row_number() OVER (PARTITION BY deptno ORDER BY sal) AS myrownumber
FROM   emp_name;
row_number in oracle
row_number in oracle

Here we can see that it just provides a sequence number and no number is skipped or repeated.

Difference between ROW_NUMBER(), RANK(), and DENSE_RANK() function in SQL

The difference boils down to the fact when ties happen. In the case of the tie,

  1. ROW_NUMBER() will give unique row numbers,
  2. the rank will give the same rank, but the next different rank will not be in sequence, there will be a gap.
  3. In the case of dense_rank, both rows in the tie will have the same rank and there will be no gap. The next different rank will be in sequence.

Saturday 19 November 2016

Excel INDIRECT Function

INDIRECT Function

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:
  1. 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.
  1. 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 Excel

Sunday 6 November 2016

Excel Function-Match

 Excel Function:Match
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:


  

Tuesday 1 November 2016

Different COUNT function in Excel


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.
Syntax: =COUNT(Range1,Range2,Range3...................,RangeN)


Monday 24 October 2016

History of MS Excel

                                      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: