excel example of formula Archives

Mar302023 by excelhelp13No Comments

Multiple IF Condition With IFS Function In Excel

FORMULAS, Logical

IFS function is used when we want to test / check multiple criteria for the same cell value as we have used in the below mentioned example. This condition is also known as Nested IF condition. But The IFS condition make it much easier to use in comparison to Normal Nested IF condition. Note : In IFS function if the result is not TRUE or we can say none of the passed condition/criteria matched it returns #N/A error. Syntax : IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2],[logical_test3, value_if_true3]… ) FORMULA =IFS(B4<35,"FAIL",B4<=40,"D",B4<50,"C",B4<=60,"B",B4>70,"A")

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

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 multi...

Mar312020 by excelhelp13No Comments

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. '

Mar312020 by excelhelp13No Comments

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...

Mar312020 by excelhelp13No Comments

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.

Mar312020 by excelhelp13No Comments

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

Mar262020 by excelhelp13No Comments

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" )