ROW

“ROW”

Used for – “ROW” function in MS Excel is used to get the absolute position of the row of the reference cell.

Syntax – ROW([reference])

             [reference] – This is an optional field as it is written in square brackets. It is a address of any cell for which we want to know the absolute position. The result of the this function is a numeric. If we do not put any reference it will consider the cell in which this function is typed and give the absolute position of the same.

                       

Example – Following example will clear the ROW function:-

In below table formulas are shown which which reflects the various reference type for the function ROW:-

=ROW(A1)	=ROW(AA1)	=ROW()
=ROW(A11)	=ROW(BA1)
=ROW(A9999)	=ROW(TAA1)

Below table shows the result of the above function if copied to cell A1 to C3 in any worksheet of workbook:

1	1	1
11	1
9999	1

Below is a typical summary of the Column Function in the pictorial form with more advance typical information:

Summarized Column Function

Note:
-Here it should be noted that reference should be used for single cell. If a range is selected then it will give the absolute row position of the top left cell of the range.

Read More

COLUMN

“COLUMN”

Used for – “COLUMN” function in MS Excel is used to get the absolute position of the column of the reference cell.

Syntax – COLUMN([reference])

             [reference] – This is an optional field as it is written in square brackets. It is a address of any cell for which we want to know the absolute position. The result of the this function is a numeric. If we do not put any reference it will consider the cell in which this function is typed and give the absolute position of the same.

                       

Example – Following example will clear the COLUMN function:-

In below table formulas are shown which which reflects the various reference type for the function COLUMN:-

=COLUMN(A1)	=COLUMN(AA1)	=COLUMN()
=COLUMN(A11)	=COLUMN(BA1)
=COLUMN(A9999)	=COLUMN(TAA1)

Below table shows the result of the above function if copied to cell A1 to C3 in any worksheet of workbook:

1	27	3
1	53
1	13547

Below is a typical summary of the Column Function in the pictorial form with more advance typical information:

Summarized Column Function 

Note:
-Here it should be noted that reference should be used for single cell. If a range is selected then it will give the absolute column position of the top left cell of the range.

Read More

LAST AND UNIQUE VALUE

Hey Guys!!!

Here's are some questions related to data and must read this post if all your answers will take to next question :-

• Have you ever require to get filter Last and unique values from a data ? 
• If yes what would you like to do then ? 
• Do you do it manually ? 
• But if it is in whole data and regular job then what would you do?

Here's a example in which you can find last and unique value from a data table using simple formulas and take decision based upon it. See how it is being done:-

You can learn the formulas used and described in the picture with there details and limitation by clicking on the following :-

1. IF
2. COUNTIF
3. ""

Read More

SUM FOR FIRST AND UNIQUE VALUE

Hey Guys!!!

Here's are some questions related to data and must read this post if all your answers will take to next question :-

• Have you ever require to get filter first and unique values from a data ? 
• If yes have you ever need to find total for those value ?
• If yes what would you like to do then ? 
• Do you do it manually ? 
• But if it is in whole data and regular job then what would you do?

Here's a example in which you can easily find sum for first and unique value from a data table using simple formulas. See how it is being done:-

You can learn the formulas used and described in the picture with there details and limitation by clicking on the following :-

1. MATCH
2. IF
3. SUMIF
4. ""

Read More

FIRST AND UNIQUE VALUE

Hey Guys!!!

Here's are some questions related to data and must read this post if all your answers will take to next question :-

• Have you ever require to get filter first and unique values from a data ? 
• If yes what would you like to do then ? 
• Do you do it manually ? 
• But if it is in whole data and regular job then what would you do?

Here's a example in which you can find first and unique value from a data table using simple formulas and take decision based upon it. See how it is being done:-

You can learn the formulas used and described in the picture with there details and limitation by clicking on the following :-

1. MATCH
2. IF
3. ""

Read More

BREAK and COMBINE Name's

Hey Guys!!!

Perhaps some time you find that you have to break name into Prefix, First Name, Middle Name and Last Name or you have to combine all these to get Name.If it is one time then you can do it manually. But if it is in whole data then what would you do?

Here's a example in which you can break or combine a name fields using formulas. See how it is being done:-

Read More

CONVERT DATE FORMAT

Hey Guys!!!

Perhaps some time you find that you have to convert the date format as shown below:-

24/02/2018  >>>>>>>  24th February, 2018. 

and If it is one time then you can do it manually. But if it is in whole data then what would you do?

Here's a example in which you can change it using formulas. See how it is being done

Read More

DATEVALUE

 “DATEVALUE”

Used for – “DATEVALUE” function in MS Excel is used to find or convert a date into a serial number that MS Excel recognizes as a date.

Syntax – DATEVALUE(date_text)

             date_text – It is any date/reference cell that contains date, you typed or calculated which you want to convert a number. It is a compulsory field and if you did not fill any value excel will not proceed further.

                       

Example – Following example will clear the DATEVALUE function:-

In below table column A contains some dates in different formats and column G contains the DATEVALUE function.

	A	B	C	D	E	F	G
1
2	=DATEVALUE("01/01/2018")
3	=DATEVALUE("01-JAN-2018")
4	=DATEVALUE("01-JAN")
5	=DATEVALUE("2018/01/01")
6	=DATEVALUE(B6&"/"&C6&"/"&D6)	1	1	2018
7

And result you get as under:-

	A	B	C	D	E	F	G	H	I
1
2	43101
3	43101
4	42736
5	43101
6	43101	1	1	2018
7

NOTE :- Please do care of following points when you use DATEVALUE :-

• Date_text must be entered in double quotation i.e. “”. otherwise it will give #VALUE! error.
• Do not mark any cell whose value is already in date format it will return #VALUE! error.
• When we used only date and month system will consider current year based on the system date. See result of A4 cell. It consider the year 2017 as the system year is 2017.

Read More