Formula Help

[Erika]

I use to have this formula working in Office 2003 and now I am getting a
value error - anyone see what is going wrong?

=IF(K2=MAX(($A$2:$A$20000=A2)*$K$2:$K$20000),"Max","")

It is suppose to look through the list find the hight value and put the work
max in the cell with the highest value

 

[Sean Timmons]

Sounds like one of the cells in either A or K is #VALUE. works fine for me...

 

[Erika]

Got it!!! I have no idea where I got A2:A20000 but that should be K - sorry!

 

[Erika]

Actually I am not getting it to work I am getting a #Value error - the
forumla is erroring out in the first line
K2=MAX(($A$2:$A$20000=A2)*$K$2:$K$20000 it is trying to find the highest
value for each unit the units are in column A.

Why would I be gettingthe #value error?

 

[Sean Timmons]

And you verified none of the cells in column A and column K have a #VALUE in
them... I don't see any error in the formula that would cause a #VALUE.

 

[Gord Dibben]

=MAX(($A$2:$A$20000=A2)*$K$2:$K$20000)

Enter the formula using CTRL + SHIFT + ENTER


Gord Dibben MS Excel MVP

 

[Erika]

Do I just press enter to exit the cell with the formula or do I need to press
ctrl+shift+enter?

 

[Erika]

The Values in column A are unit numbers and the values in column K are values
that were entered they are not the result of a formula? When I double click
in each cell and go into edit mode and then press enter to exit all of the
results are changing from #value to Max. However they all say Max.

 

[Erika]

Even with that formula, =IF(MAX(($A$2:$A$20000=A2)*$K$2:$K$20000),"Max","") I
still get Max for all the cells.

 

[Sean Timmons]

Try this instead:

=IF(K2=SUMPRODUCT(MAX((A2:A20000=A2)*(K2:K20000))),"Max","")

 

[Erika]

For whatever reason this formula is working perfectly where the other one was
not thank you all for your assistance.

 

[Sean Timmons]

As long as it works, we're happy!

For whatever reason this formula is working perfectly where the other one was
not thank you all for your assistance.