Thursday, November 21

Tag: excel formula and function

Mar312020 by excelhelp13No Comments
NESTED IF Condition In Excel

NESTED IF Condition In Excel

FORMULAS, Logical
'Nested If Condition is used to check multiple criteria in the different columns, rows or for the same cell reference. And if the criteria matches you can define what should be the outcome basis on your requirement. Here we will study the nested if condition to check the grade base upon the marks obtained in the 4 subject. We will check the percentage column to identify the percentage outcome and on that basis we will provide the grade. Distinction - First Class - Average - Bad… Here we have used a simple example to understood the nested if condition easily. Here we only applied the condition on single cell to find out the grade on the base of percentage. But nested if condition can also be used for the different cell reference to get the outcome accordingly. '
Read More
Mar312020 by excelhelp13No Comments
Convert Text To Lowercase Propercase & Uppercase

Convert Text To Lowercase Propercase & Uppercase

FORMULAS, Text
LOWER CASE:- With the help of Lower Case formula you can convert the text into lower case from Upper case or Proper case or Improper text available in any cell. It means every letter will be convert to lower case. (a b c d) UPPER CASE:- With the help of Upper Case formula you can convert the text into Upper case from Lower case or Proper case or Improper text available in any cell. It means every letter will be convert to Upper case. (A B C D) PROPER CASE:- With the help of Proper Case formula you can convert the text into Proper case from Upper case or Lower case or Improper text available in any cell. It means every letter will be convert to Proper case. (a b c d ) TO Abcd PARAMETER:- =UPPER ( Reference Cell No) / =LOWER( Reference Cell No) / =PROPER ( Ref...
Read More
Mar312020 by excelhelp13No Comments
SUBTOTAL In Excel

SUBTOTAL In Excel

FORMULAS, Maths
Excel SUBTOTAL function returns the subtotal of the numeric values in a column from a database. Excel SUBTOTAL function is used as part of a formula in a cell of a worksheet for column or row according to your database. Subtotal has several different types of option available with unique code which you can use it with some of them are SUM, COUNT, COUNTA and many more for auto calculate.
Read More
Mar312020 by excelhelp13No Comments
INDEX And MATCH Function

INDEX And MATCH Function

FORMULAS, Lookup & Reference
The INDEX MATCH is the combination of two functions in Excel. It looks both the side right and left while Vlookup only lookup in right direction. So its very useful for dynamic lookup reference for the large database. =INDEX() returns the value of a cell in a table based on the column and row number and =MATCH() returns the position of a cell in a row or column.' You can also create a drop down for Product Sku and Model Name / Price
Read More
Mar262020 by excelhelp13No Comments
IF Condition In Excel (>) (<) (=) (>=) (<=)

IF Condition In Excel (>) (<) (=) (>=) (<=)

FORMULAS, Logical
IF condition is used when you want to find the particular value or data on the basis of your requirement. For Ex:- Greater than ( > ) Less than ( < ) Equal To ( = ) Greater Than and Equal To ( >=) Less Than Equal To ( <= ) and many more... So if condition plays a vital role when you want to find the data on Basis of matching condition. If the condition match then the output should be this and that. :) PARAMETER : ( Logical Test, Value If True. Value If False ) USE: IF ( Cell Value > Some Amount or Less than or Equal to , Yes , NO ) Formula Ex: =IF ( B2 > 500,"Yes","No") / =IF ( A3 < 500,"Yes","No" ) / =IF ( A4 >= 500,"Yes","No" ) / =IF ( A5 <= 500,"Yes","No" )
Read More
Mar262020 by excelhelp13No Comments
COUNTIFS In Excel

COUNTIFS In Excel

FORMULAS, Statistical
COUNTIFS- is used to determine how many time the Same value is available in the particular selected database in sheet. It will give the result by counting that this Criteria or Value or Name is available this many time in this database. This comes in use when you want to check more then one criteria. EX: how many data are there those ID - NAME AND GENDER are Same Basically the Countifs is used when you want to count on the basis of more than one criteria. You can also find on the basis of different sheet reference simply you have to provide the reference same as you do in single sheet. Countifs is really a best function in excel. PARAMETER:- (Criteria_range_1, Criteria 1, Criteria_range_2, Criteria 2 and so on ........... FORMULA =COUNTIFS(B2:D9,"Ram") ...
Read More
Mar262020 by excelhelp13No Comments
Vlookup And Countif In Excel

Vlookup And Countif In Excel

FORMULAS, Lookup & Reference
VLOOKUP & COUNTIF -plays a vital role when they both are applied together. Vlookup has limited power. Suppose the lookup value is available more then one time in the specific table or a range then vlookup will pick the the first matching value it found and so as a result you won't get the exact match in some cases or you might be unaware of duplicate value available in a database. Here Countif plays a vital role Countif function check the lookup value in the database and as result it shows how many times that value occurs in a particular database or for a given range. NOTE: The lookup value must be the exact text or numeric number. PARAMETER VLOOKUP: - (Lookup Value, Table Reference, Column Index, 0) 0 - For Exact Match) PARAMETER COUNTIF: - (Range , Criteria) FOR...
Read More
Mar172020 by excelhelp13No Comments
LEN Function In Excel

LEN Function In Excel

FORMULAS, Text
LEN function determine the total length of a string, word, number, or sentence available in excel cell. Please note that it also count the space between the string, words, number or sentence.Please see example below in image. FORMULA: =LEN(A2)
Read More
Mar172020 by excelhelp131 Comment
INDEX Function In Excel

INDEX Function In Excel

FORMULAS, Lookup & Reference
INDEX Function give result the value at a given position in a range or array. Index is used to get single values or entire rows and columns. Index function extract the specified row and column value from a table (database) available in excel. How to use : INDEX(reference, row_num, [column_num], [area_num]) <----(Optional) . reference:- Select the entire database from which you want to get the value. row num:- Then enter the row number from which you want to get the value. column_num : Then enter the column number from which you want to get the value. [area_num]) is (Optional) . Example shown in image below. FORMULA: =INDEX(B1:E5,3,4)
Read More