Share via

Excel IF MAX function

Anonymous
2022-03-16T18:21:08+00:00

Hello,

I have an Excel file with date and value, I need help with a IF lookup array function that will return a Max value in a subsequent rows.

Max value in this case is 537, so every rows after return 537. Thank you.

Here is example data.

Date value
3/1/22 429
3/2/22 429
3/3/22 435
3/4/22 448
3/5/22 0
3/6/22 0
3/7/22 500
3/8/22 501
3/9/22 451
3/10/22 480
3/11/22 537
3/12/22 0
3/13/22 0
3/14/22 497
3/15/22 431
3/16/22 475

Result needed:

Date value
3/1/22 429
3/2/22 429
3/3/22 435
3/4/22 448
3/5/22 0
3/6/22 0
3/7/22 500
3/8/22 501
3/9/22 451
3/10/22 480
3/11/22 537
3/12/22 537
3/13/22 537
3/14/22 537
3/15/22 537
3/16/22 537
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-03-18T20:39:38+00:00

    thank you Bernie. That work for now. I might have to change a different criteria later.

    0 comments
  2. Anonymous
    2022-03-16T19:36:07+00:00

    For a table starting in A1, use this formula in C2:

    =IF(C1=MAX(B:B),C1,B2)

    and copy down.

    Date value Formula
    3/1/2022 429 429
    3/2/2022 429 429
    3/3/2022 435 435
    3/4/2022 448 448
    3/5/2022 0 0
    3/6/2022 0 0
    3/7/2022 500 500
    3/8/2022 501 501
    3/9/2022 451 451
    3/10/2022 480 480
    3/11/2022 537 537
    3/12/2022 0 537
    3/13/2022 0 537
    3/14/2022 497 537
    3/15/2022 431 537
    3/16/2022 475 537
    0 comments