Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
{=max(if(........))} Array Function not working after range length of $A1:$A339
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
{=max(if(........))} Array Function not working after range length
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 "Shrini" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
{=max(if(........))} Array Function not working after range length
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" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
{=max(if(........))} Array Function not working after range length
On Jun 27, 3:26 pm, Barb Reinhardt
wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
{=max(if(........))} Array Function not working after range length
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 "Shrini" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
{=max(if(........))} Array Function not working after range le
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 "Billy Liddel" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
{=max(if(........))} Array Function not working after range le
On Jun 27, 5:10 pm, Billy Liddel
wrote: 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 "Billy Liddel" wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range inside Array not working | Excel Programming | |||
Modify array function length | Excel Worksheet Functions | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Question about working with range array | Excel Programming | |||
Assigning range to array not working | Excel Programming |