look up and display last max value in a column

oscarlimerick · #1 01-10-2024, 12:09 PM

Quote:

Originally Posted by ArviLaanemets

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.

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

p45cal · #2 01-10-2024, 06:35 PM

Quote:

Originally Posted by oscarlimerick

I dont really want to check row wise, I want to check by the date

Try array-entering in cell G5:

Code:

=MAX(IF(((A5:A1000<INDEX(A5:A1000,MATCH(MAX(A5:A1000),A5:A1000,0)))*(B5:B1000>INDEX(B5:B1000,MATCH(MAX(A5:A1000),A5:A1000,0)))),A5:A1000))

and in cell E5:

Code:

=INDEX(B5:B1000,MATCH(G5,A5:A1000,0))

No dates with with no score.

oscarlimerick · #3 01-11-2024, 12:44 PM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Display of predecessor and successor column	tropaj	Project	3	04-03-2022 04:12 PM
display a change in column cells counting the number of months	oscarlimerick	Excel	4	03-14-2022 12:22 AM
Display an additional date column in Gantt chart	ds1	Project	4	04-17-2017 01:34 PM
Embedded Excel in Powerpoint - custom animation, display column by column	andytheanimal	PowerPoint	2	01-20-2015 06:30 AM
Excel Fomula to search for a string and display value from different column	zeeshanbutt	Excel	1	07-29-2012 12:48 AM