Excel: Find Max Date Returns Zero - Formula Results Correct

Here is the HTML output for the article:

Last Tenant Rent Increase Summary Using MAX Function in Excel

In this article, we will learn how to summarize the last tenant rent increase using the MAX function in Excel. The data we will be working with consists of tenant names, dates of the last price increase, and the corresponding increase amounts.

Preparing the Data

Before we dive into the formula, let's make sure our data is properly formatted. Here's an example of what the data might look like:

| Tenant Name | Last Price Increase Date | Last Price Increase Amount | |-------------|--------------------------|----------------------------| | John Doe | 01/01/2022 | 100 | | Jane Smith | 02/01/2022 | 50 | | Bob Johnson | 03/01/2022 | 75 | | ... | ... | ... |

Using the MAX Function

Now, let's create a formula to find the maximum rent increase amount for the last date. We'll assume the data is in columns A, B, and C, starting from row 2 (since row 1 contains headers).

1. Click on an empty cell where you want the result to appear.
2. Type the following formula:

=MAX(IF(B2:B10=MAX(B2:B10), C2:C10, ""))

3. Press Enter.

This formula works by finding the maximum date in column B (using the MAX function on B2:B10), and then finding the corresponding maximum increase amount in column C among all rows where the date in column B is equal to the maximum date found. The "" in the formula ensures that Excel treats the dates as text, which is necessary for the IF function to work correctly.

Result

The cell containing the formula should now display the maximum rent increase amount for the last date. In our example, the result would be 100 (John Doe's rent increase).

Summary

In this article, we learned how to summarize the last tenant rent increase using the MAX function in Excel. This technique can be extended to handle larger datasets and more complex scenarios.

References

• Microsoft Support: MAX Function (https://support.microsoft.com/en-us/office/max-function-2c2b323e-479f-431d-814d-914e2448d8a5)
• Excel Easy: IF Function (https://exceleasy.com/examples/if-function.html)
• Excel Jet: Using MAX Function with IF Function (https://www.excel-easy.com/data-analysis/max-if.html)