• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Max function

Ufoo

Member
Hello excel ninjas,

I have struggled to understand the highlighted portion in the following formula, of course from Chandoo.org. Why can't the MAX portion work alone without being equal to C5:C17="Jamie")*ROW(C5:C17? I have struggled to find out. I will appreciate your assistance because I am still very knew to excel. There is a lot of basic stuff that I still need to learn. Thanks. =SUMPRODUCT((MAX((C5:C17="Jamie")*ROW(C5:C17))=(C5:C17="Jamie")*ROW(C5:C17))*1,E5:E17)
 
@Ufoo,

It appears that the formula in question is designed to return the value in column E corresponding to the last row (i.e., max row number) of all rows for which column C = "Jamie".

The part you have highlighted does the following:
  • (C5:C17="Jamie")*ROW(C5:C17) --> produce an array of either zero (when column C is not "Jamie") or the row number (when column C equals "Jamie")
  • MAX((C5:C17="Jamie")*ROW(C5:C17)) --> returns the maximum row number from the array above
  • (MAX((C5:C17="Jamie")*ROW(C5:C17)) = (C5:C17="Jamie")*ROW(C5:C17))*1 --> returns an array of either ZERO or ONE for the row of the max row number containing "Jamie" in column C
  • SUMPRODUCT((MAX((C5:C17="Jamie")*ROW(C5:C17)) = (C5:C17="Jamie")*ROW(C5:C17))*1,E5:E17) --> returns the SUMPRODUCT of ZERO or ONE times the corresponding values in column E
As you are learning it may help to evaluate parts of an unfamiliar formula separately. You can use the Formula Auditing > Evaluate Formula feature or select a portion in the formula bar and use F9 to evaluate it (be sure to hit ESC when done so you do not accidentally change the formula).

I hope that helps.

Regards,
Ken
 
@Ufoo,

It appears that the formula in question is designed to return the value in column E corresponding to the last row (i.e., max row number) of all rows for which column C = "Jamie".

The part you have highlighted does the following:
  • (C5:C17="Jamie")*ROW(C5:C17) --> produce an array of either zero (when column C is not "Jamie") or the row number (when column C equals "Jamie")
  • MAX((C5:C17="Jamie")*ROW(C5:C17)) --> returns the maximum row number from the array above
  • (MAX((C5:C17="Jamie")*ROW(C5:C17)) = (C5:C17="Jamie")*ROW(C5:C17))*1 --> returns an array of either ZERO or ONE for the row of the max row number containing "Jamie" in column C
  • SUMPRODUCT((MAX((C5:C17="Jamie")*ROW(C5:C17)) = (C5:C17="Jamie")*ROW(C5:C17))*1,E5:E17) --> returns the SUMPRODUCT of ZERO or ONE times the corresponding values in column E
As you are learning it may help to evaluate parts of an unfamiliar formula separately. You can use the Formula Auditing > Evaluate Formula feature or select a portion in the formula bar and use F9 to evaluate it (be sure to hit ESC when done so you do not accidentally change the formula).

I hope that helps.

Regards,
Ken
Thanks for good explanation.
 
The file is attached. Thanks

This formula in below, as per the attachment, is to return the Last Net Sale amount from Jamie :

=SUMPRODUCT((MAX((C5:C17="Jamie")*ROW(C5:C17))=(C5:C17="Jamie")*ROW(C5:C17))*1,E5:E17)

However, you can try this shorter LOOKUP formula instead.

=LOOKUP(2,1/(C5:C17="Jamie"),E5:E17)

Regards
Bosco
 
Last edited:
Back
Top