This formula works but I don't understand...

[wizardmagu]

I am using this formula but I don't understand the logic. It is an array formula

I have column BC that has many duplicated cells. Column H is a date field and it returns a "1" on my new column on the most recent (MAX) date based on column BC. What I dont understand is the part in the formula where the * is at. Any help would be appreciated.

{=IF(MAX(($BC$2:$BC$7728=BC2)*$H$2:$H$7728)=H2,1,0)}

 

[6StringJazzer]

This is an array formula. You can tell by the {braces} around it that you see in the formula box. An array formula processes every element in an array. It's a little bit like a loop.

Here is how it works from the inside out. This part

$BC$2:$BC$7728=BC2

checks every cell in $BC$2:$BC$7728 to see if if is equal to BC2. If it is equal to BC2, that expression returns TRUE. When that result is used in arithmetic, it acts like a 1; when it's FALSE it acts like a 0.

($BC$2:$BC$7728=BC2)*$H$2:$H$7728

Now multiply either 0 or 1 from the result above times each corresponding cell in $H$2:$H$7728. This creates an array. For the ones that are equal to BC2, it populates the array with the corresponding cell in $H$2:$H$7728; otherwise it populates a 0. This array is an intermediate result internal to the formula; you will never actually see it.

MAX(($BC$2:$BC$7728=BC2)*$H$2:$H$7728)

Now it takes the maximum value of that array. So that is the maximum of all values from $H$2:$H$7728 where the same rows in BC matches BC2.

If that maximum value equals 2, return 1, otherwise 0.

It's hard for me to give you a better explanation that is relevant to what your data means, because you didn't describe what your data means. But hopefully this helps.

 

[wizardmagu]

Thanks for the response. Explanation does help. I just wasn't sure if I needed to have the * in that formula and what it was doing.

 

[6StringJazzer]

Yes you need it, and it is how the formula associates a matching cell to the corresponding date.