Showing posts with label Excel Formulas. Show all posts
Showing posts with label Excel Formulas. Show all posts

HOW TO CREATE UNIQUE DYNAMIC LIST WITH ARRAY FORMULA IN EXCEL

Create Unique List Using Array Formula

How To Create Unique list Using Array Formula


We have value in column A but all values are more than one. how we can remove duplicate value and create a dynamic list using array formulas.

Create Dynamic Unique List Using Array Formula

Input this formula
=INDEX($A$2:$A$26,MATCH(0,COUNTIF($B$1:B6,$A$2:$A$26),0)) and Press Ctrl + Shift + Enter

 Blank row In Data

Blank Row In Data Remove Using Array Formula

In case A7 rows are blank in database unique list show one blank row on B6. how we can solve it, it's very easy with ISBLANK function. 

=INDEX($A$2:$A$26,MATCH(0,IF(ISBLANK($A$2:$A$26),1,COUNTIF($B$1:B2,$A$2:$A$26)),0))


Watch Video For Easy Learning

TOP 5 EXCEL FORMULAS IN EXCEL TO CHANGE YOUR LIFE


Top 5 Micorsoft Excel Functions


Excel Is Fun And Fun is Life. In this Post we have to learn top 5 most useful function in Microsoft Excel who can change your job life.

1. how to use index function with match function 

 

Index With Match Function

2. how to use subtotal function in excel 

 



Subtotal Function 

3. how to use upper lower and proper function 

 




4. how to use AverageIfs Function

 


Sumifs Countifs and Aveageifs 

5. how to use Rept Fucntion 



Rept function

HOW TO USE INDEX / MATCH FUNCTION IN EXCEL

HOW TO USE INDEX AND MATCH FUNCTION IN EXCEL

Users Heavily use Vlookup or Lookup Function for Reference. I proved INDEX / MATCH much powerful and easy function against VLOOKUP. how to use INDEX and MATCH function.

How to use INDEX Function 

The INDEX Formula returns either the value from range or table.
The INDEX function have 2 Syntaxes

Syntax 

=INDEX(array,row_num,[column_num])

array: A Range of a table or Cells
row_num: The row number in the array to use to return the value.
column_number: Optionally, it is the column number in the array to use to return the value

How To Use Index Function In Excel

 in this example our required is sales value of code 1004 or product Book, in Cell C10 first syntax array is C2:C7 value. Second syntax is row_number after indexed code 1004 position is 4.

How to use MATCH Function

MATCH function looks for an item in a list and shows its position. it can be used with text and numbers. it can look for an exact match or an approximate match. 

Syntax


=MATCH(lookup_value,lookup_array,[match_type])

lookup_value. The value that you want to match in lookup_array.
lookup_array. The range of cells being searched.
match_type Optional. The number -1, 0, or 1.



The match-type either 0, 1 or -1

using 0 will look for an exact match. if no match is found the #N/A error will be shown.

using 1 will look for an exact match, or the next lowest number if  no exact match exists. if there is no match or next lowest number the error #N/A is shown. The list of values being examined must be sorted for this to work correctly 

using -1 will look for an  exact match, or the next highest number if no exact match exists. if there is no exact match or next highest number the error #N/A is shown. The list must be sorted for this to work properly. 

How To use match Function in Excel

INDEX With MATCH best alternative function of VLOOKUP, HLOOKUP or LOOKUP fucntions.

How To Use INDEX with Match Watch Complete Video For Easy Learning


HOW TO USE SUBTOTAL FUNCTION IN EXCEL

Subtotal Function In Excel


SUBTOTAL FUNCTION IN EXCEL


Subtotal is a most useful function against AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT,... more option. Subtotal has a ability to use a verity of functions, In additional, the subtotal function can either exclude and include values are not visible in rows. you can also use SUBTOTAL to find sum of filtered values.


Subtotal Function_num are


Function
Include hidden
Ignore hidden
AVERAGE
1
101
COUNT
2
102
COUNTA
3
103
MAX
4
104
MIN
5
105
PRODUCT
6
106
STDEV
7
107
STDEVP
8
108
SUM
9
109
VAR
10
110
VARP
11
111

Subtotal Function Vs Sum

you have two Cells A10 and A20 using sum formulas. If you apply one more sum  function in Cell A30 E.g =Sum(A1:A29) these two cell values include in results. but if do you use SUBTOTAL function in A10 and A20 cells and you also apply SUBTOTAL in cell A20 e.g =subtotal(109,A1:A29), these formula is not included A10 and A20 value in result.

Subtotal Ignore Hidden Rows

In case you have to filtered your data. SUBTOTAL ignores all cells that  are hidden. All cells will not be included in calculation that are filtered out.

Must Watch Video For Easy Learning and understanding

HOW TO USE CONCATENATE TRIM LEN FUNCTION IN EXCEL

LEN TRIM AND CONCATENATE FUNCTION

HOW TO USE CONCATENATE FUNCTION IN EXCEL

This function concatenate / joins separate pieces of text into one item. This function is most use full function in excel for joined two or more than two texts in one cell

Syntax

=CONCATENATE(Text1,Text2,Text3...Text255)



HOW TO USE CONCATENATE FUNCTION IN EXCEL

=CONCATENATE(A12,B12)

In this Example Cell C12 Join A12 And B12. You can achieve the same result by using the & operator.


ALTERNATIVE CONCATENATE FUNCTION


HOW TO USE TRIM FUNCTION IN EXCEL

TRIM function removes unwanted spaces from a piece of text. The space before and after the text will be removed completely. Multiple spaces within the text will be trimmed to a single space.

Syntax 

=TRIM(TextToTrim)



HOW TO USE TRIM FUNCTION IN EXCEL

=TRIM(A4)

In this Example Cell B4 removed space before the text shown in cell A4. In Cell B5 Removed Multiple spaces within the text will be trimmed to a single space.

HOW TO USE LEN FUNCTION IN EXCEL 

Len function is used to count numbers of characters in a cell. It also includes spaces.

Syntax 

 =LEN(text)

HOW TO USE LEN FUNCTION IN EXCEL


Select Cells B20:B22 And type the formula =len(A20) Hit Ctrl + Enter to put the formula to all range.




Download Sample File Click Here

HOW TO USE LEFT RIGHT MID FUNCTION IN EXCEL

how to use left right mid function in excel


HOW TO USE LEFT FUNCTION IN EXCEL

LEFT formula is most useful formula in Excel Formulas. LEFT Function Return a specified number of characters from the beginning of a string.

Syntex 

=LEFT(text,[num_chars])

Note

Text means any text or value or any cell & number characters shows that how many characters you need to extract. This argument is optional if you leave this option blank excel will extract 1 character from the left side of your text which means default value is 1.

Left Formula In Excel

In this Example ABC COMPANY account codes are XX-YYYY-ZZ where:
XX represents the business unit
YYYY represents the account
ZZ represents the department

My Transection detail has the full account code, but I want the business unit only. Select Cell A2 and Type
=LEFT(A2,2) and copy paste.


HOW TO USE RIGHT FUNCTION IN EXCEL


RIGHT Function returns a specified number of characters from the end of a string

Syntex

=RIGHT(text,[num_chars]) 
  

Note 

Text means any text of value or any cell and number character shows that how many characters you need to extract. This argument is optional. if you leave this option blank excel will extract 1 Character from the RIGHT side of your text which means default value is 1.

Right formula In Excel


In This Example Picture ABC COMPANY product codes are XX-XXX where:

XX is the product category
XXX is the product code

Select Cell C2 and Type "=RIGHT(A2,3)" 

HOW TO USE MID FUNCTION IN EXCEL

Excel MID function returns a specified number of characters beginning at any position within  a string

Syntex 

=MID(text, start_num, num_chars)

Text

Text means any text of string

Start Number

Start number means position in a  text string you need to extract characters from.

Number Characters

Number Characters means Number of Characters you need to extract

Mid Formula In Excel

In this picture ABC COMPANY Employee code are X-YY-ZZZZ
X represents the Business Units
YY represents the Department
ZZZZ represents Employee Code

I Want Department Code Only means YY Value. Select Cell B2 and type "=MID(A2,3,2)" 


 



Download Sample File Click Here

HOW TO USE IF FUNCTION WITH "AND OR NOT" FUNCTIONS

HOW TO USE IF FUNCTION WITH AND OR NOT FUNCTION


How to use "IF" function with "And / Or / Not" functions in excel

IF Function In Excel

if function in excel 2013
=IF(C4>=$C$1,"Allow","Not Allow")

In this Example picture defines us to when salesperson Achieved target to Allow Bonus Otherwise Not Allow. if c4 cell value is 460,027 and target value is 500,000 in cell c1. if c4 greater than or equal to c1 result True if the value is true if a function is shown result "Allow" if the value is false shown result "Not Allow"

And Function In Excel With IF Function


And function in excel

=IF(AND(C15>=$C$11,D15>$C$12),"Allow","Not Allow")

in the picture use "AND" function. "AND" function is very useful logical_test Function in Excel. In this function all arguments evaluate is true result is True. One and more than one arguments evaluate is false result is False.

=And(Logical1, Logical2....)
=And(True, True, True) = "True"
=And(True,True,False) = "False" 

Or Function In Excel with If Function

Or function in excel www.coursedots.com

=IF(OR(C25>=$C$21,D25>$C$22),"Allow","Not Allow")

in the picture use "OR" function. "OR" function is very useful logical_test Function in Excel. In this function one and more than one arguments evaluate is True result is True.

=Or(Logical1, Logical2....)
=Or(True, True, True) = "True"
=Or(True,True,False) = "True"
=Or(False,False,False) = "False"

Not Function Use With if Conditions


not function use in excel www.coursedots.com

=IF(NOT(AND(C35>=$C$31,D35>$C$32)),"Not Allow","Allow")

In the picture use "NOT" Function. "NOT" Function Reverses your Result. that mean True Value convert in False and False value convert in True. Not function use when you want to make sure a value is not equal to one particular value.

Video

 

Download Sample File Click Here

you have any idea about Excel, blogger., WordPress, photoshop, Seo Experts, other like this simply create your own article and submit your own post. your post will publish as soon on www.coursedots.com