• 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

U

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
 
KenU said:
@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
Click to expand...
Thanks for good explanation.
 
You must log in or register to reply here.
Back
Top