Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula Error
Hello -
I have a data set similar to below Date Field 1 Field 2 Field 3 1/1/09 x x o 1/5/09 o x x 1/14/09 o o x I also have an array formula in a separate cell that looks for the row with the closest match to today's date and is supposed to return the row number that is the closest match. The formula is: =MATCH(MIN(ABS(A1:A29-TODAY())),ABS(A1:A29-TODAY()),0) I keep getting the following error in the array formula: #VALUE! Is it because the format of the date in column A? I am not sure what I'm doing wrong. Any help would greatly be appreciated. Thanks for your time and effort. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula Error
Your formula works fine for me whether the dates are Excel dates or text
dates. Is your data all in column A? That would generate the #VALUE error you describe. If that is the case, use Data Text to Columns to split the data into four columns so the dates are alone in column A. Hope this helps, Hutch "CecesWorking" wrote: Hello - I have a data set similar to below Date Field 1 Field 2 Field 3 1/1/09 x x o 1/5/09 o x x 1/14/09 o o x I also have an array formula in a separate cell that looks for the row with the closest match to today's date and is supposed to return the row number that is the closest match. The formula is: =MATCH(MIN(ABS(A1:A29-TODAY())),ABS(A1:A29-TODAY()),0) I keep getting the following error in the array formula: #VALUE! Is it because the format of the date in column A? I am not sure what I'm doing wrong. Any help would greatly be appreciated. Thanks for your time and effort. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula Error
I entered you data into A1:D3 and copied your formula
As expected I Got result #N/A When I opened the cell with the formula for editing and committed it with CTR+SHIFT+ENTER I got answer 3 - the correct value. You must use the three-fingered commit as this is an array formula. But you get #VALUE! The format of the date cells will have no effect (dates are stored as serial numbers like 39838 and can be displayed in various ways) as long as they are real dates. In a blank cell (say G1) enter = A1+1; do you get 1/2/2009? Test all the dates this way. Tell what you find best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "CecesWorking" wrote in message ... Hello - I have a data set similar to below Date Field 1 Field 2 Field 3 1/1/09 x x o 1/5/09 o x x 1/14/09 o o x I also have an array formula in a separate cell that looks for the row with the closest match to today's date and is supposed to return the row number that is the closest match. The formula is: =MATCH(MIN(ABS(A1:A29-TODAY())),ABS(A1:A29-TODAY()),0) I keep getting the following error in the array formula: #VALUE! Is it because the format of the date in column A? I am not sure what I'm doing wrong. Any help would greatly be appreciated. Thanks for your time and effort. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula Error
Hello Tom -
Thanks for the help. Only the Date data lives in column A. I tried to further specify the formula he =MATCH(MIN(ABS($A:$A-TODAY())),ABS($A:$A-TODAY()),0) But now I receive a new error: #NUM! Is it because I'm using Excel 2003? I'm not sure at all what I'm doing wrong. Any help would greatly be appreciated. Thank you again for your time and effort. "Tom Hutchins" wrote: Your formula works fine for me whether the dates are Excel dates or text dates. Is your data all in column A? That would generate the #VALUE error you describe. If that is the case, use Data Text to Columns to split the data into four columns so the dates are alone in column A. Hope this helps, Hutch "CecesWorking" wrote: Hello - I have a data set similar to below Date Field 1 Field 2 Field 3 1/1/09 x x o 1/5/09 o x x 1/14/09 o o x I also have an array formula in a separate cell that looks for the row with the closest match to today's date and is supposed to return the row number that is the closest match. The formula is: =MATCH(MIN(ABS(A1:A29-TODAY())),ABS(A1:A29-TODAY()),0) I keep getting the following error in the array formula: #VALUE! Is it because the format of the date in column A? I am not sure what I'm doing wrong. Any help would greatly be appreciated. Thanks for your time and effort. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula Error
There's nothing wrong with your original formula - the problem must be in the
dates. Text in any of the cells in your column A range would cause the #VALUE error. An empty cell won't cause the error, but a cell with a one or more spaces (which looks empty) will cause the error. Check your column A range for any cells with text. If your data was imported from another system, you might have hidden characters in one or more cells causing the problem. Hutch "CecesWorking" wrote: Hello Tom - Thanks for the help. Only the Date data lives in column A. I tried to further specify the formula he =MATCH(MIN(ABS($A:$A-TODAY())),ABS($A:$A-TODAY()),0) But now I receive a new error: #NUM! Is it because I'm using Excel 2003? I'm not sure at all what I'm doing wrong. Any help would greatly be appreciated. Thank you again for your time and effort. "Tom Hutchins" wrote: Your formula works fine for me whether the dates are Excel dates or text dates. Is your data all in column A? That would generate the #VALUE error you describe. If that is the case, use Data Text to Columns to split the data into four columns so the dates are alone in column A. Hope this helps, Hutch "CecesWorking" wrote: Hello - I have a data set similar to below Date Field 1 Field 2 Field 3 1/1/09 x x o 1/5/09 o x x 1/14/09 o o x I also have an array formula in a separate cell that looks for the row with the closest match to today's date and is supposed to return the row number that is the closest match. The formula is: =MATCH(MIN(ABS(A1:A29-TODAY())),ABS(A1:A29-TODAY()),0) I keep getting the following error in the array formula: #VALUE! Is it because the format of the date in column A? I am not sure what I'm doing wrong. Any help would greatly be appreciated. Thanks for your time and effort. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Formula Error
Hi,
The NUM error is occuring because Excel will not allow you to use entire columns in this type of array until version 2007. So you need to change A:A to A$1:A$65535 or something smaller. -- If this helps, please click the Yes button Cheers, Shane Devenshire "CecesWorking" wrote: Hello Tom - Thanks for the help. Only the Date data lives in column A. I tried to further specify the formula he =MATCH(MIN(ABS($A:$A-TODAY())),ABS($A:$A-TODAY()),0) But now I receive a new error: #NUM! Is it because I'm using Excel 2003? I'm not sure at all what I'm doing wrong. Any help would greatly be appreciated. Thank you again for your time and effort. "Tom Hutchins" wrote: Your formula works fine for me whether the dates are Excel dates or text dates. Is your data all in column A? That would generate the #VALUE error you describe. If that is the case, use Data Text to Columns to split the data into four columns so the dates are alone in column A. Hope this helps, Hutch "CecesWorking" wrote: Hello - I have a data set similar to below Date Field 1 Field 2 Field 3 1/1/09 x x o 1/5/09 o x x 1/14/09 o o x I also have an array formula in a separate cell that looks for the row with the closest match to today's date and is supposed to return the row number that is the closest match. The formula is: =MATCH(MIN(ABS(A1:A29-TODAY())),ABS(A1:A29-TODAY()),0) I keep getting the following error in the array formula: #VALUE! Is it because the format of the date in column A? I am not sure what I'm doing wrong. Any help would greatly be appreciated. Thanks for your time and effort. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with DIV/0 error in an AVERAGE array formula | Excel Worksheet Functions | |||
Array Formula returns error but only for specific columns | Excel Discussion (Misc queries) | |||
Simplifying array formula which evaluates as error. | Excel Worksheet Functions | |||
Trouble shooting#NA error in Array formula | Excel Discussion (Misc queries) | |||
Changing a range of an array in a SUMPRODUCT formula gives a #N/A error | Excel Discussion (Misc queries) |