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

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

"Erika" wrote:

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

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

"Erika" wrote:

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

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" wrote:

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

"Erika" wrote:

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

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.

"Erika" wrote:

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" wrote:

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

"Erika" wrote:

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

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

Enter the formula using CTRL + SHIFT + ENTER


Gord Dibben MS Excel MVP

On Thu, 1 Oct 2009 09:37:01 -0700, Erika
wrote:

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" wrote:

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

"Erika" wrote:

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

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

"Sean Timmons" wrote:

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.

"Erika" wrote:

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" wrote:

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

"Erika" wrote:

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

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.

"Sean Timmons" wrote:

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.

"Erika" wrote:

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" wrote:

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

"Erika" wrote:

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

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

"Gord Dibben" wrote:

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

Enter the formula using CTRL + SHIFT + ENTER


Gord Dibben MS Excel MVP

On Thu, 1 Oct 2009 09:37:01 -0700, Erika
wrote:

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" wrote:

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

"Erika" wrote:

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

Try this instead:

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

"Erika" wrote:

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.

"Sean Timmons" wrote:

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.

"Erika" wrote:

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" wrote:

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

"Erika" wrote:

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

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

"Sean Timmons" wrote:

Try this instead:

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

"Erika" wrote:

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.

"Sean Timmons" wrote:

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.

"Erika" wrote:

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" wrote:

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

"Erika" wrote:

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

As long as it works, we're happy! :-)

"Erika" wrote:

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

"Sean Timmons" wrote:

Try this instead:

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

"Erika" wrote:

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.

"Sean Timmons" wrote:

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.

"Erika" wrote:

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" wrote:

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

"Erika" wrote:

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