Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula works on first row only
Greetings
I have an array formula that I have entered (using CTRL+SHIFT+ENTER) as follows: =IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA()) This works fine on the first row I entered it into and gives a result. However, when I copy it down so it references A3, A4 etc, it always returns #N/A for every subsequent row. Possibly useful other information: 1. The numbers in column I are formatted as text, hence the VALUE function. 2. Thinking the CONCATENATE might be the problem I created another column with the concatenated result and referenced that instead, but to no avail. Any ideas why it doesn't work? Thanks Murray |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula works on first row only
Try entering it as a regular (non-array) formula and copying it down. Works
for me. Hope this helps, Hutch "Murray" wrote: Greetings I have an array formula that I have entered (using CTRL+SHIFT+ENTER) as follows: =IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA()) This works fine on the first row I entered it into and gives a result. However, when I copy it down so it references A3, A4 etc, it always returns #N/A for every subsequent row. Possibly useful other information: 1. The numbers in column I are formatted as text, hence the VALUE function. 2. Thinking the CONCATENATE might be the problem I created another column with the concatenated result and referenced that instead, but to no avail. Any ideas why it doesn't work? Thanks Murray |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula works on first row only
On Feb 5, 10:05*am, Tom Hutchins
wrote: Try entering it as a regular (non-array) formula and copying it down. Works for me. Hope this helps, Hutch "Murray" wrote: Greetings I have an array formula that I have entered (using CTRL+SHIFT+ENTER) as follows: =IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA()) This works fine on the first row I entered it into and gives a result. However, when I copy it down so it references A3, A4 etc, it always returns #N/A for every subsequent row. Possibly useful other information: 1. The numbers in column I are formatted as text, hence the VALUE function. 2. Thinking the CONCATENATE might be the problem I created another column with the concatenated result and referenced that instead, but to no avail. Any ideas why it doesn't work? Thanks Murray- Hide quoted text - - Show quoted text - Thanks Tom, but that only seems to work if the row number of the value in column A matches its counterpart in rows 2-1900. I seem to have found a solution by changing it to =SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2 ,VALUE(I$2:I$1900), 0)) but I'm still unsure as to why this works and the other one does not. Murray |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula works on first row only
=SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A 2,VALUE(I$2:I$1900),0))
The numbers in column I are formatted as text hence the VALUE function. Try this normally entered formula: =SUMPRODUCT(--($C$2:$C$1900&$D$2:$D$1900=$A2),--I$2:I$1900) -- Biff Microsoft Excel MVP "Murray" wrote in message ... On Feb 5, 10:05 am, Tom Hutchins wrote: Try entering it as a regular (non-array) formula and copying it down. Works for me. Hope this helps, Hutch "Murray" wrote: Greetings I have an array formula that I have entered (using CTRL+SHIFT+ENTER) as follows: =IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA()) This works fine on the first row I entered it into and gives a result. However, when I copy it down so it references A3, A4 etc, it always returns #N/A for every subsequent row. Possibly useful other information: 1. The numbers in column I are formatted as text, hence the VALUE function. 2. Thinking the CONCATENATE might be the problem I created another column with the concatenated result and referenced that instead, but to no avail. Any ideas why it doesn't work? Thanks Murray- Hide quoted text - - Show quoted text - Thanks Tom, but that only seems to work if the row number of the value in column A matches its counterpart in rows 2-1900. I seem to have found a solution by changing it to =SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2 ,VALUE(I$2:I$1900), 0)) but I'm still unsure as to why this works and the other one does not. Murray |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula works on first row only
Thanks Biff
With a few modifications to deal with errors induced by blank values, and array entering it, it worked really well. Murray On Feb 5, 12:27*pm, "T. Valko" wrote: =SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A 2,VALUE(I$2:I$1900),0)) The numbers in column I are formatted as text hence the VALUE function. Try this normally entered formula: =SUMPRODUCT(--($C$2:$C$1900&$D$2:$D$1900=$A2),--I$2:I$1900) -- Biff Microsoft Excel MVP "Murray" wrote in message ... On Feb 5, 10:05 am, Tom Hutchins wrote: Try entering it as a regular (non-array) formula and copying it down. Works for me. Hope this helps, Hutch "Murray" wrote: Greetings I have an array formula that I have entered (using CTRL+SHIFT+ENTER) as follows: =IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA()) This works fine on the first row I entered it into and gives a result.. However, when I copy it down so it references A3, A4 etc, it always returns #N/A for every subsequent row. Possibly useful other information: 1. The numbers in column I are formatted as text, hence the VALUE function. 2. Thinking the CONCATENATE might be the problem I created another column with the concatenated result and referenced that instead, but to no avail. Any ideas why it doesn't work? Thanks Murray- Hide quoted text - - Show quoted text - Thanks Tom, but that only seems to work if the row number of the value in column A matches its counterpart in rows 2-1900. I seem to have found a solution by changing it to =SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2 ,VALUE(I$2:I$1900), 0)) but I'm still unsure as to why this works and the other one does not. Murray- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula works on first row only
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Murray" wrote in message ... Thanks Biff With a few modifications to deal with errors induced by blank values, and array entering it, it worked really well. Murray On Feb 5, 12:27 pm, "T. Valko" wrote: =SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A 2,VALUE(I$2:I$1900),0)) The numbers in column I are formatted as text hence the VALUE function. Try this normally entered formula: =SUMPRODUCT(--($C$2:$C$1900&$D$2:$D$1900=$A2),--I$2:I$1900) -- Biff Microsoft Excel MVP "Murray" wrote in message ... On Feb 5, 10:05 am, Tom Hutchins wrote: Try entering it as a regular (non-array) formula and copying it down. Works for me. Hope this helps, Hutch "Murray" wrote: Greetings I have an array formula that I have entered (using CTRL+SHIFT+ENTER) as follows: =IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2,VAL UE(I$2:I$1900),NA()) This works fine on the first row I entered it into and gives a result. However, when I copy it down so it references A3, A4 etc, it always returns #N/A for every subsequent row. Possibly useful other information: 1. The numbers in column I are formatted as text, hence the VALUE function. 2. Thinking the CONCATENATE might be the problem I created another column with the concatenated result and referenced that instead, but to no avail. Any ideas why it doesn't work? Thanks Murray- Hide quoted text - - Show quoted text - Thanks Tom, but that only seems to work if the row number of the value in column A matches its counterpart in rows 2-1900. I seem to have found a solution by changing it to =SUM(IF(CONCATENATE($C$2:$C$1900,$D$2:$D$1900)=$A2 ,VALUE(I$2:I$1900), 0)) but I'm still unsure as to why this works and the other one does not. Murray- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Bar works | Excel Discussion (Misc queries) | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
Array formula that works columnwise? | Excel Worksheet Functions | |||
IF formula works one way and not the other | Excel Worksheet Functions | |||
How do I convert works file to excel without works software? | Excel Discussion (Misc queries) |