|
Copy/Paste array formulae from the newsgroup
Hi Group,
Can anyone explain this behaviour? Often when I copy and paste an array formula from the newsgroups, the formula will work straight away even though it hasn't been committed with Ctrl+Shift+Enter and there are no curly braces around it. If I then do an F2+Enter the formula will fall down and requires a C+S+E to recommit it. Take this example from a recent Bob Phillips post. =MAX(IF(A1:A1000<"",ROW(A1:A1000))) If I paste it anywhere outside column A, and then start putting data in column A, the formula will correctly return last row that has a number in col A, even though it is an array formula and has not been committed with C+S+E and there are no curly braces around it. Any ideas why? Regards Martin |
Copy/Paste array formulae from the newsgroup
Are you sure it's working *exactly* as described?
If I enter the formula (normally entered) in E4 and I then enter a random number in A1 the result of the formula is 0. If I then start entering random numbers in cell A2 and continue down the column, the formula continues to return 0 until there is an entry in cell A4. Then the formula result is 1 which is correct. This due to what's called the implicit intersection rule in regards to arrays. The formula and a piece of data are entered on the same row (could also be a column) and the data cell is within the referenced range of the formula. This is called an implicit intersection. Since the formula references an array but is not array entered it only evaluates the first element of the array. =MAX(IF(A1<"",ROW(A1))) Here's another example of an implicit intersection. Enter the numbers 1,2,3,4,5 in A1:A5. (Normally) enter this formula in B6: =OFFSET(A1,,,5) The result will be an error. Now, drag the formula up to B5 and note the result, then drag up to B4, B3, B2, B1. Now, drag the formula back down to B5 then F2CSE. Note the result. These were 2 different formulas doing 2 totally different things so the results are different but the implicit intersection rule dictated those results. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Hi Group, Can anyone explain this behaviour? Often when I copy and paste an array formula from the newsgroups, the formula will work straight away even though it hasn't been committed with Ctrl+Shift+Enter and there are no curly braces around it. If I then do an F2+Enter the formula will fall down and requires a C+S+E to recommit it. Take this example from a recent Bob Phillips post. =MAX(IF(A1:A1000<"",ROW(A1:A1000))) If I paste it anywhere outside column A, and then start putting data in column A, the formula will correctly return last row that has a number in col A, even though it is an array formula and has not been committed with C+S+E and there are no curly braces around it. Any ideas why? Regards Martin |
Copy/Paste array formulae from the newsgroup
Thanks for the reply Biff,
As you say when you enter the formula normally it will return 0 until you put data in A5 and it will then return 1. However if you copy the formula from my post and paste it into F4 and go through the same trial you will see it correctly returns the row number of the last value. If I then put a value in A37 the normally entered formula in E4 will show 1, but the pasted version in F4 shows 37 the same as it would if it was committed properly with CSE. I'll have a play with your implicit intersection examples and see if I can get a handle on that. Thanks Martin "T. Valko" wrote in message ... Are you sure it's working *exactly* as described? If I enter the formula (normally entered) in E4 and I then enter a random number in A1 the result of the formula is 0. If I then start entering random numbers in cell A2 and continue down the column, the formula continues to return 0 until there is an entry in cell A4. Then the formula result is 1 which is correct. This due to what's called the implicit intersection rule in regards to arrays. The formula and a piece of data are entered on the same row (could also be a column) and the data cell is within the referenced range of the formula. This is called an implicit intersection. Since the formula references an array but is not array entered it only evaluates the first element of the array. =MAX(IF(A1<"",ROW(A1))) Here's another example of an implicit intersection. Enter the numbers 1,2,3,4,5 in A1:A5. (Normally) enter this formula in B6: =OFFSET(A1,,,5) The result will be an error. Now, drag the formula up to B5 and note the result, then drag up to B4, B3, B2, B1. Now, drag the formula back down to B5 then F2CSE. Note the result. These were 2 different formulas doing 2 totally different things so the results are different but the implicit intersection rule dictated those results. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Hi Group, Can anyone explain this behaviour? Often when I copy and paste an array formula from the newsgroups, the formula will work straight away even though it hasn't been committed with Ctrl+Shift+Enter and there are no curly braces around it. If I then do an F2+Enter the formula will fall down and requires a C+S+E to recommit it. Take this example from a recent Bob Phillips post. =MAX(IF(A1:A1000<"",ROW(A1:A1000))) If I paste it anywhere outside column A, and then start putting data in column A, the formula will correctly return last row that has a number in col A, even though it is an array formula and has not been committed with C+S+E and there are no curly braces around it. Any ideas why? Regards Martin |
Copy/Paste array formulae from the newsgroup
However if you copy the formula from my post
I did! I can't reproduce what you're describing. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Thanks for the reply Biff, As you say when you enter the formula normally it will return 0 until you put data in A5 and it will then return 1. However if you copy the formula from my post and paste it into F4 and go through the same trial you will see it correctly returns the row number of the last value. If I then put a value in A37 the normally entered formula in E4 will show 1, but the pasted version in F4 shows 37 the same as it would if it was committed properly with CSE. I'll have a play with your implicit intersection examples and see if I can get a handle on that. Thanks Martin "T. Valko" wrote in message ... Are you sure it's working *exactly* as described? If I enter the formula (normally entered) in E4 and I then enter a random number in A1 the result of the formula is 0. If I then start entering random numbers in cell A2 and continue down the column, the formula continues to return 0 until there is an entry in cell A4. Then the formula result is 1 which is correct. This due to what's called the implicit intersection rule in regards to arrays. The formula and a piece of data are entered on the same row (could also be a column) and the data cell is within the referenced range of the formula. This is called an implicit intersection. Since the formula references an array but is not array entered it only evaluates the first element of the array. =MAX(IF(A1<"",ROW(A1))) Here's another example of an implicit intersection. Enter the numbers 1,2,3,4,5 in A1:A5. (Normally) enter this formula in B6: =OFFSET(A1,,,5) The result will be an error. Now, drag the formula up to B5 and note the result, then drag up to B4, B3, B2, B1. Now, drag the formula back down to B5 then F2CSE. Note the result. These were 2 different formulas doing 2 totally different things so the results are different but the implicit intersection rule dictated those results. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Hi Group, Can anyone explain this behaviour? Often when I copy and paste an array formula from the newsgroups, the formula will work straight away even though it hasn't been committed with Ctrl+Shift+Enter and there are no curly braces around it. If I then do an F2+Enter the formula will fall down and requires a C+S+E to recommit it. Take this example from a recent Bob Phillips post. =MAX(IF(A1:A1000<"",ROW(A1:A1000))) If I paste it anywhere outside column A, and then start putting data in column A, the formula will correctly return last row that has a number in col A, even though it is an array formula and has not been committed with C+S+E and there are no curly braces around it. Any ideas why? Regards Martin |
Copy/Paste array formulae from the newsgroup
I'll try to explain it step by step what I am doing
as it certainly is strange....to me anyway. I'm using XL2000 though that shouldn't matter (I think) In a fresh worksheet I copy and paste the formula separately into E4 F4 and G4. I then, Select E4, press F2 and commit with Enter Select F4, press F2 and commit with C+S+E Leave G4 as pasted. All three formulae are showing 0 I then put a 1 in A1 which shows E4 = 0 F4 = 1 G4 = 1 I continue to put 1 down col A, when I get to A4 the formulae are showing E4 = 1 F4 = 4 G4 = 4 I then skip to A37 and put a 1 (or any number), the formulae now show E4 = 1 F4 = 37 G4 = 37 Skip again to A739 and put a number in, shows E4 = 1 F4 = 739 G4 = 739 The pasted formula in G4 works in perfect unison with the CSE formula in F4 even though it has not been committed as an array. Can you duplicate that or have I got something really screwy happening here? Regards Martin "T. Valko" wrote in message ... However if you copy the formula from my post I did! I can't reproduce what you're describing. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Thanks for the reply Biff, As you say when you enter the formula normally it will return 0 until you put data in A5 and it will then return 1. However if you copy the formula from my post and paste it into F4 and go through the same trial you will see it correctly returns the row number of the last value. If I then put a value in A37 the normally entered formula in E4 will show 1, but the pasted version in F4 shows 37 the same as it would if it was committed properly with CSE. I'll have a play with your implicit intersection examples and see if I can get a handle on that. Thanks Martin "T. Valko" wrote in message ... Are you sure it's working *exactly* as described? If I enter the formula (normally entered) in E4 and I then enter a random number in A1 the result of the formula is 0. If I then start entering random numbers in cell A2 and continue down the column, the formula continues to return 0 until there is an entry in cell A4. Then the formula result is 1 which is correct. This due to what's called the implicit intersection rule in regards to arrays. The formula and a piece of data are entered on the same row (could also be a column) and the data cell is within the referenced range of the formula. This is called an implicit intersection. Since the formula references an array but is not array entered it only evaluates the first element of the array. =MAX(IF(A1<"",ROW(A1))) Here's another example of an implicit intersection. Enter the numbers 1,2,3,4,5 in A1:A5. (Normally) enter this formula in B6: =OFFSET(A1,,,5) The result will be an error. Now, drag the formula up to B5 and note the result, then drag up to B4, B3, B2, B1. Now, drag the formula back down to B5 then F2CSE. Note the result. These were 2 different formulas doing 2 totally different things so the results are different but the implicit intersection rule dictated those results. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Hi Group, Can anyone explain this behaviour? Often when I copy and paste an array formula from the newsgroups, the formula will work straight away even though it hasn't been committed with Ctrl+Shift+Enter and there are no curly braces around it. If I then do an F2+Enter the formula will fall down and requires a C+S+E to recommit it. Take this example from a recent Bob Phillips post. =MAX(IF(A1:A1000<"",ROW(A1:A1000))) If I paste it anywhere outside column A, and then start putting data in column A, the formula will correctly return last row that has a number in col A, even though it is an array formula and has not been committed with C+S+E and there are no curly braces around it. Any ideas why? Regards Martin |
Copy/Paste array formulae from the newsgroup
Can you duplicate that or have I got something
really screwy happening here? I *can not* duplicate that and I followed your steps exactly. I'm using Excel 2002 (all service packs installed) See inline comments -- Biff Microsoft Excel MVP "MartinW" wrote in message ... I'll try to explain it step by step what I am doing as it certainly is strange....to me anyway. I'm using XL2000 though that shouldn't matter (I think) In a fresh worksheet I copy and paste the formula separately into E4 F4 and G4. I then, Select E4, press F2 and commit with Enter Select F4, press F2 and commit with C+S+E Leave G4 as pasted. All three formulae are showing 0 I then put a 1 in A1 which shows E4 = 0 F4 = 1 G4 = 1 The results I get a E4 = 0 F4 = 1 G4 = 0 I continue to put 1 down col A, when I get to A4 the formulae are showing E4 = 1 F4 = 4 G4 = 4 The results I get a E4 = 1 F4 = 4 G4 = 1 I then skip to A37 and put a 1 (or any number), the formulae now show E4 = 1 F4 = 37 G4 = 37 The results I get a E4 = 1 F4 = 37 G4 = 1 Skip again to A739 and put a number in, shows E4 = 1 F4 = 739 G4 = 739 The results I get a E4 = 1 F4 = 739 G4 = 1 The pasted formula in G4 works in perfect unison with the CSE formula in F4 even though it has not been committed as an array. Can you duplicate that or have I got something really screwy happening here? Well, I can't duplicate that and I don't have an explanation as to why it's doing that for you. It shouldn't. Hopefully others will see this thread and try it then let us know how it worked for them. Regards Martin "T. Valko" wrote in message ... However if you copy the formula from my post I did! I can't reproduce what you're describing. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Thanks for the reply Biff, As you say when you enter the formula normally it will return 0 until you put data in A5 and it will then return 1. However if you copy the formula from my post and paste it into F4 and go through the same trial you will see it correctly returns the row number of the last value. If I then put a value in A37 the normally entered formula in E4 will show 1, but the pasted version in F4 shows 37 the same as it would if it was committed properly with CSE. I'll have a play with your implicit intersection examples and see if I can get a handle on that. Thanks Martin "T. Valko" wrote in message ... Are you sure it's working *exactly* as described? If I enter the formula (normally entered) in E4 and I then enter a random number in A1 the result of the formula is 0. If I then start entering random numbers in cell A2 and continue down the column, the formula continues to return 0 until there is an entry in cell A4. Then the formula result is 1 which is correct. This due to what's called the implicit intersection rule in regards to arrays. The formula and a piece of data are entered on the same row (could also be a column) and the data cell is within the referenced range of the formula. This is called an implicit intersection. Since the formula references an array but is not array entered it only evaluates the first element of the array. =MAX(IF(A1<"",ROW(A1))) Here's another example of an implicit intersection. Enter the numbers 1,2,3,4,5 in A1:A5. (Normally) enter this formula in B6: =OFFSET(A1,,,5) The result will be an error. Now, drag the formula up to B5 and note the result, then drag up to B4, B3, B2, B1. Now, drag the formula back down to B5 then F2CSE. Note the result. These were 2 different formulas doing 2 totally different things so the results are different but the implicit intersection rule dictated those results. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Hi Group, Can anyone explain this behaviour? Often when I copy and paste an array formula from the newsgroups, the formula will work straight away even though it hasn't been committed with Ctrl+Shift+Enter and there are no curly braces around it. If I then do an F2+Enter the formula will fall down and requires a C+S+E to recommit it. Take this example from a recent Bob Phillips post. =MAX(IF(A1:A1000<"",ROW(A1:A1000))) If I paste it anywhere outside column A, and then start putting data in column A, the formula will correctly return last row that has a number in col A, even though it is an array formula and has not been committed with C+S+E and there are no curly braces around it. Any ideas why? Regards Martin |
Copy/Paste array formulae from the newsgroup
Thanks for trying Biff. It's all very strange. Like you
say it shouldn't happen like that. Hopefully another 2000 user can confirm whether or not they get the same behaviour. Thanks again Martin "T. Valko" wrote in message ... Can you duplicate that or have I got something really screwy happening here? I *can not* duplicate that and I followed your steps exactly. I'm using Excel 2002 (all service packs installed) See inline comments -- Biff Microsoft Excel MVP "MartinW" wrote in message ... I'll try to explain it step by step what I am doing as it certainly is strange....to me anyway. I'm using XL2000 though that shouldn't matter (I think) In a fresh worksheet I copy and paste the formula separately into E4 F4 and G4. I then, Select E4, press F2 and commit with Enter Select F4, press F2 and commit with C+S+E Leave G4 as pasted. All three formulae are showing 0 I then put a 1 in A1 which shows E4 = 0 F4 = 1 G4 = 1 The results I get a E4 = 0 F4 = 1 G4 = 0 I continue to put 1 down col A, when I get to A4 the formulae are showing E4 = 1 F4 = 4 G4 = 4 The results I get a E4 = 1 F4 = 4 G4 = 1 I then skip to A37 and put a 1 (or any number), the formulae now show E4 = 1 F4 = 37 G4 = 37 The results I get a E4 = 1 F4 = 37 G4 = 1 Skip again to A739 and put a number in, shows E4 = 1 F4 = 739 G4 = 739 The results I get a E4 = 1 F4 = 739 G4 = 1 The pasted formula in G4 works in perfect unison with the CSE formula in F4 even though it has not been committed as an array. Can you duplicate that or have I got something really screwy happening here? Well, I can't duplicate that and I don't have an explanation as to why it's doing that for you. It shouldn't. Hopefully others will see this thread and try it then let us know how it worked for them. Regards Martin "T. Valko" wrote in message ... However if you copy the formula from my post I did! I can't reproduce what you're describing. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Thanks for the reply Biff, As you say when you enter the formula normally it will return 0 until you put data in A5 and it will then return 1. However if you copy the formula from my post and paste it into F4 and go through the same trial you will see it correctly returns the row number of the last value. If I then put a value in A37 the normally entered formula in E4 will show 1, but the pasted version in F4 shows 37 the same as it would if it was committed properly with CSE. I'll have a play with your implicit intersection examples and see if I can get a handle on that. Thanks Martin "T. Valko" wrote in message ... Are you sure it's working *exactly* as described? If I enter the formula (normally entered) in E4 and I then enter a random number in A1 the result of the formula is 0. If I then start entering random numbers in cell A2 and continue down the column, the formula continues to return 0 until there is an entry in cell A4. Then the formula result is 1 which is correct. This due to what's called the implicit intersection rule in regards to arrays. The formula and a piece of data are entered on the same row (could also be a column) and the data cell is within the referenced range of the formula. This is called an implicit intersection. Since the formula references an array but is not array entered it only evaluates the first element of the array. =MAX(IF(A1<"",ROW(A1))) Here's another example of an implicit intersection. Enter the numbers 1,2,3,4,5 in A1:A5. (Normally) enter this formula in B6: =OFFSET(A1,,,5) The result will be an error. Now, drag the formula up to B5 and note the result, then drag up to B4, B3, B2, B1. Now, drag the formula back down to B5 then F2CSE. Note the result. These were 2 different formulas doing 2 totally different things so the results are different but the implicit intersection rule dictated those results. -- Biff Microsoft Excel MVP "MartinW" wrote in message ... Hi Group, Can anyone explain this behaviour? Often when I copy and paste an array formula from the newsgroups, the formula will work straight away even though it hasn't been committed with Ctrl+Shift+Enter and there are no curly braces around it. If I then do an F2+Enter the formula will fall down and requires a C+S+E to recommit it. Take this example from a recent Bob Phillips post. =MAX(IF(A1:A1000<"",ROW(A1:A1000))) If I paste it anywhere outside column A, and then start putting data in column A, the formula will correctly return last row that has a number in col A, even though it is an array formula and has not been committed with C+S+E and there are no curly braces around it. Any ideas why? Regards Martin |
| All times are GMT +1. The time now is 11:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com