Good day Excel experts. I have attached an example spreadsheet. It is a spreadsseet I intend to update daily with the date being column A, and a given numerical value in column B. What I want to do here is have the spreadhseet display in cell E5, the LAST maximum value in column B, and the corresponding date of that value in cell G5. Obviously using "=max(b:b)" in cell E5 will return the value 50 as this is the maximum numerical value in column B, but I want the spreadsheet to look up and display in cell E5, the last maximum value in column B,which in my example spreadsheet can be found in cell B15, or 26. Then I want to know what formula to use in cell G5 to return the date when this value occured, or 2024-01-11, as in cell A15.

Then each day as the list is updated, I want the values in cell E5 and G5 to recalculate and display the last occurring maximum value that is in column B, based on the date, so it should look back and find the previous date with the maximum value of column B and display that If the current cell on a given date is equal to or already the maximum value in column B, then display that value and date in E5 and G5. Please let me know if any questions, and thanks for your help and suggestions

Attached Files

EXCEL FORUM EXAMPLE.xlsx (9.7 KB, 4 views)

How have you determined that B15 (26) is the last max value ?

Because on the last date entered so far Jan 14, the value is 21. So if I start looking back, on the previous date, Jan 13, the value is 9, so this is less that 21. Then on Jan 12 the value is 13, also less than 21. But... on Jan 11, the value was 26, which is greater than 21, so I want to capture this 21 value in cell E5, along with the date Jan 11 in cell G5.

As I enter more dates and values for each date, I want excel to compare the current score value in cloumn B, with the previous cells, and work its way up to where it finds a value greater than the current value in column B. Hope that makes sense

This will require a strong formula. Regretfully, formulas are not my strong suite.

Can someone else with a good knowledge of formulas please step in and assist ?
Thank you !

So what you really want, is the last value greater than current one. The max previous value can be somewhere before Jan 11.
Another thing is, you are speaking about dates, but you are checking row-wise. What is 'previous' for you - the previous date, or previous row. What happens when the table is rearranged (sorted) by some other column. In case you check not date-wise, but row-wise, your formula's results most probably will be entirely different.

Looks like you're still on Excel 2013; can you confirm?

If so, then this is not straightforward. I've put together one solution, but it's not great. If I think of a better solution I'll post again.
So, in cell E5: which should be entered onto the sheet using Ctrl+Shift+Enter, not just plain Enter (called array-entering).

A slight variant, array-entered into cell G5 to give the date:Note that as @ArviLaanements pointed out, this one uses previous row, not previous date.

Also, it currently looks only at rows 5 to 1000, you need to adjust it if you need more rows (or fewer too, perhaps).
Note that it depends on there being all empty cells in that range in column B below the last score.

If you're on Office 365 the formula can be much shorter.

See attached.

Attached Files

msofficeforums51894EXCEL FORUM EXAMPLE.xlsx (10.6 KB, 5 views)

You are correct, the max value can be before Jan 11 and is in my example, but the defining criteria I am interested in capturing the last max value, which is cell B15 or 26. So to answer, I dont intend to sort any of the columns, and I dont really want to check row wise, I want to check by the date and display the last max value as well as the date it occured. Thanks

Unfortunately I am still using excel 2013. Thganks for the suggestion, I will try it out and reply whether it worked for me or not

Try array-entering in cell G5:and in cell E5:No dates with with no score.

Hello, I havent tried some of the previous suggestions yet, but I did try this one (I entered both formulas as instructed) and it didn't work unfortunately. These formulas retuned a value of #N/A in cell E5 and a 0 value in cell G5. Did these formaulas work for you? Maybe I'm doing something else wrong or missing a step? Do I need to do anything special to enter the formulas? I just copied and pasted them ion to E5 and G5. Thanks

His last suggested solution works here.

You are correct, this solution does work, I just need to follow instructions better. I need to do an array enter for cell G% for this towork properly, I missed that the first time. Now I just need to go thru the fornulas and figure out how and why they work. Many thanks to all who provided suggested solution(s). I will close out the thread now