{=max(if(........))} Array Function not working after range length of $A1:$A339

[Shrini]

Hello there,

I have a excel database, in which a equipment Name and there
utlisation date is given. I want to check the last date on which the
equipment was used and then plan new test on the equipment ahead of
this last available date. I have tried to use function like this

{=MAX(IF(Test_Equip=A1,Finish_Date))}

The function works for the ranges up to 339 rows. thereafter it gives
the formula result as #N/A.

Can any body help me out in this. I have tried to use direct range and
named ranges too. The actual data base is continuously increasing and
hence i have kept the lenght of named range at 50000 rows.

Regards,

Shriniwas.

 

[Guest]

1. Does the length of TestEquip = the length of FINISH_Date?
2. Did you commit the array formula with CTRL SHIFT ENTER to get the
brackets?

HTH,
Barb Reinhardt

 

[Guest]

You may also want to check that the date at 339 or 340 rows is really a date
and not text. In an adjacent column, enter =ISNUMBER(Cell_Address). If you
get FALSE, you have text instead of a date.

 

[Shrini]

1. Does the length of TestEquip = the length of FINISH_Date?
2. Did you commit the array formula with CTRL SHIFT ENTER to get the
brackets?

HTH,
Barb Reinhardt

Dear Barb,

Yes for both. Actually both the fields are formulae results
The Test Equipment Field is derived by a vlookup formula and the date
field is derived from a cell link

 

[Guest]

Shrini

I could not reproduce your error. If I went outside the range the formula
returned
01/01/1900 or 0 formatted to general. with the test data Utilisation DAte
is in column B and test Equipment was in A, so using a name of data for both
columns I could repeat your result with the following formula

=VLOOKUP(A1,Data,2,0)

I know this does not answer your question I'll look forward to other replies.

Peter

 

[Guest]

I looked at Barbara's sugestion about dates being entered as text and added a
space before a couple of dates. this gave the zero answer as before with the
array function. If this is the case you can convert these with a macro.
select the dates and run this but you will then have to re-enter your formula.

Sub T()
For Each c In Selection
c.Value = Trim(c)
Next
End Sub

However, you can ignore these errors with the lookup function that will
return the text in say, D1 Then use something like:
=IF(TRIM(D1)>TODAY()-10,"Activate","OK") to highlight what action to make.

Hope this helps

Peter

 

[Shrini]

I looked at Barbara's sugestion about dates being entered as text and added a
space before a couple of dates. this gave the zero answer as before with the
array function. If this is the case you can convert these with a macro.
select the dates and run this but you will then have to re-enter your formula.

Sub T()
For Each c In Selection
c.Value = Trim(c)
Next
End Sub

However, you can ignore these errors with the lookup function that will
return the text in say, D1 Then use something like:
=IF(TRIM(D1)>TODAY()-10,"Activate","OK") to highlight what action to make.

Hope this helps

Peter

Dear all,

I could solve the problem by avoiding the cell result to #N/A since
the Vlookup formula was giving #N/A error due to blank lookup cell.

But thanks for your inputs as it made me rethink on the same.

regards,

Shriniwas.