excelhelp.in Archives - Page 9 of 11

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.

Apr22020 by excelhelp13No Comments

IFERROR With Vlookup

FORMULAS, Logical

IFERROR is used to add a custom remarks or note when no data found or any error occurs for any formula used in excel. Simply you have to add IFERROR before any formula and at the end just add your custom text into double quote and it will not show any #N/A, Error False.

Apr22020 by excelhelp13No Comments

INDIRECT Function In Excel

FORMULAS, Lookup & Reference

INDIRECT function is used in Excel to change over a content string into a legitimate range reference. For example: if you want to find the value of cell A2 then simply use =INDIRECT("a2") or if you have a reference in any cell as we have here in F column simply select that and it will give you the value available in that reference. Clarification: the equation above lessens to =SUM(INDIRECT("a2:c2")). The INDIRECT function add the value of both cells and the content string "a2:c2" converts to a legitimate range reference

Mar312020 by excelhelp13No Comments

PERCENTAGE In Excel

FORMULAS, Maths

There is a Simple way of finding the percentage. Multiply the obtain marks , score or anything you have scored out of the total scoreboard then multiply the value into 100 and divide the sum of total scoreboard value and as a result the value found will be your percentage. For example there are 4 subject of 100 marks each so sum of total marks = 400 and assume that 1 student got 60 marks each in every 4 subject so in total he obtain 240 marks from sum of total 400 marks. So as a result the Formula will be Obtain marks * 100 / Total Marks. = 240 *100 / 400 = 60% According to math for finding percentage is to divide the numerator by the denominator then multiply the output with 100. Suppose you want to find the percentage of 50/100 then divide 50 with 100 the answer will be 1/2 the multip...