Dave Bruns

At the core, this is a simple formula that simply divides the total amount by the number of months given:

=amount/months

The trick is to "cancel out" this amount in months where it doesn't apply.

To do this, we use this logical...Read more

This formula uses the LOOKUP function to find and retrieve the last matching file name. The lookup value is 2, and the lookup_vector is created with this:

1/(ISNUMBER(FIND(G6,files)))

Inside this snippet, the FIND function looks for the...Read more

This formula uses COUNTIF with an expanding range to first check if the current row is the first occurrence of a given invoice number:

COUNTIF($B$5:B5,B5)=1

This expression only returns TRUE when...Read more

The RAND function generates a decimal number between zero and 1. So, you might get output like this from RAND() in three cells:

0.54739314
0.919767722
0.633760119

Dates in Excel are defined as simple numbers, where 1 = 1 day. This means you can simply divide 1 by...Read more

In this example, the goal is to return a list of the locations for a specific value in a 2D array of values (i.e., a table). The target value is entered in cell N5, and the table being tested is in the range C4:L16. The coordinates are supplied from row 4 and column B, as seen in the...Read more

The SMALL function retrieves the smallest values from data based on a given rank. For example:

=SMALL(range,1) // smallest
=SMALL(range,2) // 2nd smallest
=SMALL(range,3) // 3rd smallest

In the...Read more