The result is the sum of the Roman numbers in B5:B15. The Arabic numbers in C5:C15 are shown for reference only. Note: the ARABIC function was introduced in Excel 2013. The solution is to use the ARABIC function to convert the Roman numbers to regular numbers, then sum the result. The ARABIC function takes a valid Roman number and returns its Arabic equivalent. For example: Notice the Roman numbers are provided as text strings....

The result is $9.54, the subtotal of the visible values in column F. Sum with SUBTOTAL In the worksheet shown above, the goal is to sum the values in column F that are visible. The formula in F4 is: The first argument, function_num, specifies sum as the operation to be performed. SUBTOTAL automatically ignores the 3 rows hidden by the filter and returns $9.54, the subtotal of the visible values in column F....

To convert these fractional values to decimal hours, just multiply by 24. For example .5 * 24 = 12 hours, .24 * 24 = 6 hours, etc. Hours between times To calculate hours between times, you can simply subtract the start time from the end time when both times are in the same day. For example, with start time of 9:00 AM and an end time of 3:00 PM, you can simply use this formula:...

The key is to understand that time in Excel is just a number. 1 day = 24 hours, and 1 hour = 0.0412 (1/24). That means 12 hours = 0.5, 6 hours = 0.25, and so on. Because time is just a number, you can add time to days and display the result using a custom number format, or with your own formula, as explained below. In the example shown, the formula in cell F5 is: On the right side of the formula, the TIME function is used to assemble a valid time from its component parts (hours, minutes, seconds)....

which outputs the 5 unique values seen in D5:D9. The <> symbol is a logical operator that means “does not equal”. For more examples of operators in formula criteria see this page. FILTER returns an array of values, excluding empty strings: This array is returned directly to the UNIQUE function as the array argument. UNIQUE then removes duplicates, and returns the final array: UNIQUE and FILTER are dynamic functions. If data in B5:B16 changes, the output will update immediately....

where key is the named range B5:C9. This formula returns standard output when the score >= 60, and “x” for scores less than 60. This formula is based on a simple grading example explained in detail here. For a given score, VLOOKUP uses a existing table, the named range key (B5:C9), to calculate a grade. Note match mode is set to approximate. To override output, VLOOKUP is nested in an IF statement: The literal translation of this formula is: If VLOOKUP returns “F”, return “x”....

As the formula is copied down the column, rows where there is a value are numbered and empty rows are skipped. The IF function first checks if cell C5 has a value with the ISBLANK function: If C5 is empty, ISBLANK returns TRUE and the IF function returns an empty string ("") as the result. If C5 is not empty, ISBLANK returns FALSE and the IF function returns COUNTA function with an expanding reference like this: As the formula is copied down, the range expands, and COUNTA returns the “current” count of all non-blank cells in the range as defined in each row....

where data (C5:E16) and group (B5:B16) are named ranges. The result is the average of values in group “A” for all three months of data. As the formula is copied down, it calculates an average for each group in column G. The FILTER function is a new function in Excel. See below for alternatives that will work in older versions of Excel. Note: I was inspired to create this example after watching a good video by Excel expert Chandoo....

rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: per - the period we want to work with. Supplied as 1 since we are interested in the the principal amount of the first payment. pv - The present value, or total value of all payments now. In the case of a loan, this is input as a negative value by adding a negative sign in front of C5 to supply -5000....

As the formula is copied down, the results in column E correspond to the percentages in column D. Note percentages must be formatted with the percentage number format, as explained below. The first step is to format the cells in the range D5:D12 with the percentage number format. You can use the keyboard shortcut Control + Shift + %. Once the cells are correctly formatted, you can enter the numbers without the % operator....

where data is an Excel Table in the range (C5:B16). As the formula is copied down, it returns a count of birthdays per year as shown. Video: What is an Excel table. Note: this example has been updated below to show how to create an all-in-one formula with dynamic arrays in the latest version of Excel. SUMPRODUCT function The easiest way to solve this problem is to use the SUMPRODUCT function together with the YEAR function like this in cell F5: Working from the inside out, we use the YEAR function to extract the year from each birthday: Because there are 12 dates in the list, the YEAR function returns 12 values in an array like this: Next, each value in this array is compared against the year in E5, which is 1999....

where “dates” is the named range B5:B104. To get the first day of the month, we use the EOMONTH function like this: EOMONTH returns the last day of the previous month, to which 1 is added to get the first day of the current month. In a similar way, we use EOMONTH to get the first day of the next month: EOMONTH returns the last day of the current month, to which 1 is added to get the first day of the next month....

where data is an Excel Table in the range B5:C16. As the formula is copied down, it returns a new count in each row using the Start and End values in columns E and F to determine a count. COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Conditions are supplied to COUNTIFS in the form of range/criteria pairs — each pair contains one range and the associated criteria for that range: Using this pattern we can count ages in brackets like this: We are using the structured reference data[Age] for both ranges, since data is an Excel Table....

where range1 (B5:B16) and range2 (D5:D13) are named ranges. Note: this formula does not care about the location or order of the items in each range. Note: Both formulas below use the SUMPRODUCT function to “tally up” the final count, because SUMPRODUCT handles arrays natively in Legacy Excel. In the current version of Excel, which supports dynamic arrays, you can use the SUM function instead. COUNTIF function The COUNTIF function counts values in a range that meet supplied criteria....

For example, if a user inputs 15%: If a user enters 15.5%, the formula evaluates like this This formula doesn’t validate anything else, for example that percentages are less than 100%. Additional conditions can be added with the AND function. Notes: Data validation rules are triggered when a user adds or changes a cell value. Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case B5....

Examples With a single cell reference, COLUMN returns the associated column number: When a reference is not provided, COLUMN returns the column number of the cell the formula resides in. For example, if the following formula is entered in cell D6, the result is 4: When COLUMN is given a range, it returns the column numbers for that range: In Excel 365, which supports dynamic array formulas, the result is an array {5,6,7} that spills horizontally into three cells, starting with the cell the formula resides in....

Times can be supplied to the HOUR function as text (e.g. “7:45 PM”) or as decimal numbers (e.g. 0.5, which equals 12:00 PM). To create a time value from scratch with separate hour, minute, and second inputs, use the TIME function. The HOUR function will “reset” to 0 every 24 hours (like a clock). To work with hour values larger than 24, use a formula to convert time to decimal hours....

Examples ISREF returns TRUE when value is a reference and FALSE if not: Some functions, like INDEX and OFFSET, can return a reference. As long as the reference is valid, ISREF returns TRUE: To evaluate a reference as text (i.e. “A1”), use the INDIRECT function: Notes Use the INDIRECT function with ISREF to evaluate a reference as text. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

Example 1 | Example 2 | Example 3 | More examples In computer programming, the term LAMBDA refers to an anonymous function or expression. An anonymous function is a function defined without a name. In Excel, the LAMBDA function is first used to create a generic (unnamed) formula. Once a generic version has been created and tested, it is ported to the Name Manager, where it is formally defined and named....

The MAX function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MAX ignores empty cells, text values, and the logical values TRUE and FALSE. Basic Example The MAX function returns the largest numeric value in supplied data: When given a range, MAX returns the smallest value in the range:...