Explainry Tutorials to Expert in IT

Category: Example

Return different values based on condition

To return different values based on condition e.g. student’s result based on numbers obtained, use the CHOOSE function. For example, you want student grade corresponding to score obtained. For that, type the CHOOSE function with the following conditions: =CHOOSE(((B9>=90)+(B9>=80)+(B9>=70)+(B9>=60)+(B9>=50)),”Fail”,”Poor”,”Fair”,”Good”,”Excellent”) 2- Result. Explanation. The CHOOSE function has two arguments i.e. index number argument which uses to […]

VDB function: First month depreciation formula in Excel

The VDB function calculates depreciation of an asset, for a specified period like the first day, first monthly or first year etc. In this example, we will calculate the first-month depreciation of an asset in Excel. Example Suppose, you purchased an asset with the initial cost of $5,000, the asset is assumed to have the useful life […]

LEN function in Excel

The LEN function in excel counts number of characters, letters, numbers and all spaces, in a given text string. For example, the length of a text string such as “Shift” is “5”, which is counted through the LEN function. Example In the above example, the length of each text string is given in 3rd column, named […]

Sum values specified by a year (Sum if by year)

To sum values specified by a year, The SUMIFS function will be used with two criteria. In our example, we will sum year wise sold units of a product. 1– Type ‘=SUMIFS’ and press ‘tab’ button. 2– Select values to sum against criteria. 3– Press ‘,‘ symbol and define the range to be tested against […]

Add different numerical values specified by criteria (Sum if between)

Use the SUMIFS function to add different numerical values specified by a set of conditions or criteria. For example, in our example, we will sum all those values which are greater than 100 and smaller than 200. Follow these steps to get the desired result: 1– Select any cell, type ‘=SUMIFS’ and press ‘tab’ button to […]

Days in month

By using both the Day function and the EOMONTH function, we can find the number of days in a given month e.g. to find how many days are in the month of Oct. Example Formula: Result: How the formula works The formula used above is: =DAY(EOMONTH(A4,0)) where the DAY function is used to find days […]

How to get nth largest value in Excel

Use the LARGE function to get the nth largest value from a set of values e.g. 2nd largest, 3rd largest, and, so on. In our example, we will largest and second largest values. How the formula works The formula used to find the largest value in the example is: =LARGE(A5:D5,1)  where, array (A5:D5) – The range […]

Copyright © 2016 - 2020 Explainry.com | All Rights Reserved