Formulas

OR logic in Sumproduct function in Excel

OR logic in Sumproduct function in Excel Sumproduct usually will be applied with AND logic. OR logic in sumproduct function can be implemented as detailed below. A) AND condition example is as follows: =Sumproduct((A3:A159=B3:B159)*(D3:D159="Target A")) =Sumproduct((A3:A159=B3:B159)*(D3:D159="Target B")) B) There are ways to add OR condition using “+” symbol. The “plus sign” (+) is for OR in...

Display Times as Hours/Minutes in Excel

Display Times as Hours/Minutes in Excel Usually, Excel stores time as decimal values and displaying times as hours or minutes. Ex: A1=200 B1=A1/1440 (1440 = 24*60) = 0.1389 Now, use custom format as h:mm (or) h” hours and “m” minutes” There is an another option in Excel using Convert function.  =Convert(A1,”mn”,”hr”) -Ref: https://www.accountingweb.com/technology/excel/display-times-as-hoursminutes-in-excel

Minimum if multiple criteria using Excel

Minimum if multiple criteria using Excel Get the minimum value by checking multiple criteria from different columns. Minimum if multiple criteria is calculated with the following array formulas including nested if conditions. A) The following formula checks different criteria and get the minimum values from value column. =MIN(IF(rng1=criteria1,IF(rng2=criteria2,values))) Press Ctrl+Shift+Enter to convert to array...

Check, if a cell is in the list of ranges or not in Excel?

Check, if a cell is in the list of ranges or not in Excel? There are ways to search a text in range of cells using countif formula. The range can be specified using named ranges / specifying the ranges directly / using array formats. Here are the following examples that lists different ways...

Presence of number in a string using Excel Formulas

Presence of number in a string using Excel Formulas Use Find to check each occurrence of number in the array and count at the end. If the value is greater than 0, then it can be concluded that, cell B1 has number in it. The result is TRUE or FALSE. =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},B1))>0 Few examples are...

How to calculate SUM in Excel?

How to calculate SUM in Excel? Sum in Excel can be performed with “+” symbol. However, when large data set is present, it is better to go with sum function and you will see the advantages below. Sum can include numbers, cell references, ranges, arrays, and constants. Limited to 255 individual arguments. Sum function is used...

Match next highest value

Match next highest value It finds the next value based on the number provided. =INDEX(data,MATCH(lookup,values)+1) https://exceljet.net/formula/match-next-highest-value

Average (Mean), Median and Mode in Excel

Average (Mean), Median and Mode in Excel Average / Mean: The average is only used in a data file in which there are minimal differences Median: The MEDIAN function calculates the middle value of the number.  The median is used for data files that have large differences. Mode: More frequent occuring number in the data set...

Advanced Filters in Excel

Advanced Filters in Excel Advanced filtering options techniques Filter it based on DRange and Dsum formulas. Advaned filter options available in the Excel, where you can filter by various properties of the cell. http://www.contextures.com/xladvfilter01_2003.html

Published On

December 2018
M T W T F S S
« Nov    
 12
3456789
10111213141516
17181920212223
24252627282930
31