#1
|
|||
|
|||
look up and display last max value in a column
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 |
#2
|
|||
|
|||
How have you determined that B15 (26) is the last max value ?
|
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 ! |
#5
|
|||
|
|||
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. |
#6
|
||||
|
||||
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: Code:
=INDEX(B5:B1000,MATCH(MAX(IF(B5:B1000>INDEX(B5:B1000,MAX((B5:B1000<>"")*(ROW(B5:B1000)))-ROW(B5)+1),ROW(B5:B1000))),IF(B5:B1000>INDEX(B5:B1000,MAX((B5:B1000<>"")*(ROW(B5:B1000)))-ROW(B5)+1),ROW(B5:B1000)),0)) A slight variant, array-entered into cell G5 to give the date: Code:
=INDEX(A5:A1000,MATCH(MAX(IF(B5:B1000>INDEX(B5:B1000,MAX((B5:B1000<>"")*(ROW(B5:B1000)))-ROW(B5)+1),ROW(B5:B1000))),IF($B$5:$B1000>INDEX(B5:B1000,MAX((B5:B1000<>"")*(ROW(B5:B1000)))-ROW(B5)+1),ROW(B5:B1000)),0)) 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. Last edited by p45cal; 01-10-2024 at 04:27 PM. |
#7
|
|||
|
|||
Quote:
|
#8
|
|||
|
|||
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
|
#9
|
||||
|
||||
Quote:
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)) Code:
=INDEX(B5:B1000,MATCH(G5,A5:A1000,0)) |
#10
|
|||
|
|||
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
|
#11
|
|||
|
|||
His last suggested solution works here.
|
#12
|
|||
|
|||
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
|
|
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 |