# I am struggling to understand what this formula is doing?

1. ## I am struggling to understand what this formula is doing?

Hi,

I am struggling to understand what this formula is doing ``Please Login or Register  to view this content.``
Can someone please explain if you know?
Is there a much more simple way of doing what it is trying to do?

Thanks,  Register To Reply

2. ## Re: I am struggling to understand what this formula is doing?

Maybe practice with =maxifs()  Register To Reply

3. ## Re: I am struggling to understand what this formula is doing?

this is pretty urgent so won't have time to practice but I'll give it a go.
Is the current formula replaceable using MaxIfs?  Register To Reply

4. ## Re: I am struggling to understand what this formula is doing?

First, you cannot use MAXIFS if you are truly using Excel 2010, as your profile states.

Second, in Excel 2010, a slightly simpler syntax would be:

{ =MAX(IF(\$A\$1:\$A\$4543=A4, IF(\$G\$1:\$G\$4543<G4, \$G\$1:\$G\$4543))) }

You do not type the curly braces. Instead, you must be careful to array-enter the formula by pressing ctrl+shift+Enter instead just Enter after typing the formula =MAX(...) .

(And note that the left curly brace is left of "=", not right of it as you wrote.)

To answer your question (understand the formula), the formula calculates the max of only those values in G1:G4543 in rows where the corresponding value in A1:A4543 equals A4 __and__ the value in G1:G4543 is less than G4.

Since A4 and G4 are relative references, they will change to A5 and G5, A6 and G6 etc as the formula is copied down a column.

We can __not__ use simpler syntax like =MAX(IF(AND(\$A\$1:\$A\$4543=A4,\$G\$1:\$G\$4543<G4),\$G\$1:\$G\$4543)) in a normally-entered (or array-entered) formula. It will "work"; read: not produce an error. But it will not select from G1:G4543 based on a row-by-row AND of the conditions, as intended. So it usually returns the wrong value, except by coincidence.

If you can use =MAXIFS(\$G\$1:\$G\$4543, \$A\$1:\$A\$4543, A4, \$G\$1:\$G\$4543, "<"&G4) , you are using a later version of Excel than Excel 2010. You should update your profile, or at least mention the Excel version in your posting.  Register To Reply

5. ## Re: I am struggling to understand what this formula is doing?

joeu2004 - thank you, that is a brilliant and a very helpful explanation. Much appreciated.  Register To Reply