Formulas

Automatically update the data from one sheet to another in Excel

Automatically update the data from one sheet to another in Excel The following procedure describes the approach on how to automatically update the data from one sheet to another sheet. There are various procedures. However, please refer to the following quick procedures. Enter any value in sheet1. Select sheet2, and press “=”. Then, select...

Sum text values like numbers in Excel

Sum text values like numbers in Excel Please refer to the following procedure to sum text values like numbers in excel.  There will be a separate table located to get the value using array vlookup formula. Sample formula is as follows: {=SUM(INDEX(K1:K3,N(IF(1,MATCH(A1:E1,J1:J3,0)))))} J1:K3 is the table to refer. Where column K contains the numbers....

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