excel formula example Archives

Apr52020 by excelhelp13No Comments

VLOOKUP In Excel using Different Sheet

TO use V-lookup on different sheet and extract the data from different sheet please follow the mentioned step below. V-LOOKUP is a vertical lookup formula in excel. V-LOOKUP only looks from left to right. It always take a lookup reference from first column and give result from the column index selected. Let's understand with the example. In this example we need to extract the EMAIL ID From sheet 2 with the help of V-lookup. STEP-1 Select THE CELL STEP-2 =Vlookup( Lookup value , go to sheet 2 and select the table, provide the column index number where Email exist, 0 ( For exact match) HIT ENTER.

Apr52020 by excelhelp13No Comments

Extract Text Using Left – Right – Find – and LEN Function In Excel

FORMULAS, Text

For extracting specific text from a particular sentence or a word please follow the step shown below. TO EXTRACT FROM LEFT Here we have a list of Emails in the excel sheet and we want to find and extract the text before the character @ from the email id Example01@hHotmail.comFor that we need 2 function LEFT and FINDLEFT FUNCTION Looks and start from left side.FIND FUNCTION find the specific character from the reference text.Use Formula =LEFT(A2,FIND("@",A2,1) But this will extract the character "@" too. Result = Example01@But not to worry for that simply minus (-) 1 character. Formula =LEFT(A2,FIND("@",A2,1)-1) Result = Example01For example please see the image and video provided below. TO EXTRACT FROM RIGHT Here we have a list of Emails in the excel sheet and we want to find ...

Apr42020 by excelhelp13No Comments

EXTRACT TEXT With LEN() and RIGHT() Function

FORMULAS, Text

Suppose you only want a specific text from a particular cell and avoid extra label like "Name:-" or whatever your database contain. To apply it and get the result you will have to follow the few step as mentioned below. First find out the total length of the particular cell text by using =LEN(A2) formula and apply in next to name column OR you can directly apply into the LEN function like this =RIght(A2,LEN(A2)-7) NAME:- <------(7) character. .Remember it always depends on your criteria so apply according to your criteria baseNOTE: LEN() function count the space too.Follow the step shown in image and video provided below. IMAGE EXAMPLE ONE : IMAGE EXAMPLE TWO :

Apr42020 by excelhelp13No Comments

NESTED SUMIF With Drop Down List

FORMULAS, Maths, Text

Create a Drop Down List and then apply SUMIF formula in next cell. Just follow the below step to create drop down menu and then apply the Sumif formula First we need to create a drop down list.Select a cell where you want to have your drop down list.Go to Data TabGo to Data Validation and click on it and one dialog box will pop up.Select List from that dialog box and go down one step into source row in that dialog box.Now select the range for the data you want to create a drop down list. For Ex:- A2:A10Once Selected just hit ok button in the dialog box and your drop down list is prepared.Then in the next cell apply the NESTED SUMIF formula.For Example see the step shown in the image and a video provided below.

Apr42020 by excelhelp13No Comments

DROP DOWN List In Excel

FORMULAS, Statistical, Text

For Creating a Drop Down List just follow the few step shown below. Select a cell where you want to have your drop down list.Go to Data TabGo to Data Validation and click on it and one dialog box will pop up.Select List from that dialog box and go down one step into source row in that dialog box.Now select the range for the data you want to create a drop down list. For Ex:- A2:A10Once Selected just hit ok button in the dialog box and your drop down list is prepared.For Example see the step shown in the image and a video provided below.

Apr32020 by excelhelp13No Comments

AND Function In Excel

FORMULAS, Logical

AND Function is a logical function in excel. Basically AND Function is used to fulfill more than 1 criteria and show result in the form of TRUE or FALSE. If a single criteria is not matching then as a result it will give you false. Suppose if you want to find the logical condition true or false as per example shown in image You can also use this AND function with other function in excel like SUM, Count, Countif, Countifs and many more.

Apr32020 by excelhelp13No Comments

SUM Function With OFFSET Function In Excel

FORMULAS, Lookup & Reference, Maths

OFFSET Function is used to move or skip either row or column. Suppose you want to know what data available after every 2 rows or column the offset function is the best choice to get perfect result. It also used with other fucntion available in excel to make it more powerful. For example =SUM(OFFSET(A1,2,2,2,2))

Apr32020 by excelhelp13No Comments

OFFSET Funtion In Excel

FORMULAS, Lookup & Reference

OFFSET Function is used to move or skip either row or column or both. Suppose you want to know what data available after every 2 rows or column the offset function is the best choice to get perfect result. It also used with other available in excel to make it more powerful. For example =Sum(offset(A2,2,3,1,2)

Apr32020 by excelhelp13No Comments

SUBTOTAL With COUNT

FORMULAS, Maths, Statistical

SUBTOTAL is a great function available in excel. It only look the visible data and ignore the hidden data. So when you want to apply a Count function for only visible data only, when you are finding the data by applying filter or you have some hidden rows. The Count function count the number of numeric data available in the selected range. Use the formula at the end of your database where you want to apply this both function as shown in image and video example..

Apr32020 by excelhelp13No Comments

Vlookup with IF Condition

FORMULAS, Logical, Lookup & Reference

When using V-lookup with If logical condition it will determine that when a v-lookup should process and display a result. For Ex: with the help of If condition a user can decide that if a certain value exist then only apply a V-lookup else display a custom message or error. So its very much benefit to use both the function together.