Posts

The multiple to use for rounding is provided as thesignificanceargument. The MROUND function takes twoarguments,numberandsignificance.Numberis the numeric value to round. Thesignificanceargument is the multiple to whichnumbershould be rounded. Nearest 15 minutes MROUND can be used to round time. CEILING works like theMROUND function, but CEILINGalways rounds up. FLOOR Function The Excel FLOOR function rounds a given number down to the nearest specified multiple. FLOOR works like the MROUND function, but FLOORalways rounds down....

A multi-cell array formula is anarray formulathat returns multiple results to more than one cell at the same time.

The result in each case is an array of TRUE and FALSE values. F5 and G4 are entered asmixed referencesso that the formula can be copied through the table without modification. you could use INDEX to retrieve individual values, or entire rows and columns. MATCH supports approximate and exact matching, andwildcards(* ?)

The AND function returns TRUE only if all values in the array are TRUE. In all other cases, AND will return FALSE. Case-sensitive option The formula above is not case-sensitive. Like the formula above, this is anarray formulaand must be entered with control + shift + enter. AND returns TRUEonly if all the conditions are met. If any conditions are not met, the AND function returns FALSE.

Explanation This formula relies on the standard behavior of the COUNTIF function. If the count is zero, the formula returns TRUE. If the count is anything but zero, the formula returns FALSE. AND returns TRUEonly if all the conditions are met. If any conditions are not met, the AND function returns FALSE. The AND function is commonly used with other…

SUMPRODUCT Function The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products. COUNTA does not count empty cells.

The VLOOKUP function will throw an #N/A error when a value isn’t found. By nesting multiple VLOOKUPs inside the IFERROR function, the formula allows for sequential lookups. If the first VLOOKUP fails, IFERROR catches the error and runs another VLOOKUP. If the second VLOOKUP fails, IFERROR catches the error and runs another VLOOKUP, and so on. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements....

A count of zero returns TRUE, and any other number returns FALSE. This isnotan array formula, so it does not require special handling. AND returns TRUEonly if all the conditions are met. If any conditions are not met, the AND function returns FALSE. COUNTIF can be used to count…

The result of IF looks is this array: which goes into the TEXTJOIN function as text1. TEXTJOIN is configured to use a comma as the delimiter, and to ignore empty values. You will also need toenable text wrapping. you’re free to use boolean logic like this for AND: Explained in more detail here.

This is sometimes referred to as a “pivot” operation. Modern approach Several new functions in the current version of Excel make this task easier. As the formula is copied down column F, the reference to E5 isrelativeand changes at each new row. As a final result, we have all names in each group together in the same row. However, Excel formulas won’t currently return anarray-of-arraysso this won’t work. The trick is that SMALL is working with an array that is already filtered by group....

This is sometimes referred to as a “pivot” operation. Modern approach Several new functions in the current version of Excel make this task easier. This formulaspillsthe four unique group names into the range E4:H4. With the groups in place, we now have what we need to retrieve the names in each group. As the formula is copied across, the reference to E5 isrelativeand changes at each new row. The result is all names in each group are together in the same column....

Explanation This formula is designed to be copied throughout the interior of the multiplication table without change. They are harder to set up, but make formulas much easier to enter.

The unit matrix is also called the identity matrix. The MUNIT function takes just one argument,dimension, which should be a positive integer. The resulting matrix contains ones on the main diagonal and zeros in every other position. The result from MMULT is anarraythat contains the same number of rows asarray1and the same number of columns asarray2. MINVERSE Function The Excel MINVERSE function returns the inverse matrix of a given array....