Forum Home
Press F1
Thread ID: 47985	2004-08-11 04:50:00	Excel formula	bpt1 (419)	Press F1

Post ID	Timestamp	Content	User
260431	2004-08-12 20:14:00	> >An array entered value thats entered normally doesnt > always return an error (#Value etc) but instead > returns an incorrect value and so may not be picked > up immediately. Its just something to take into > consideration. > I don’t find what you say very clear. Errors can > occur in any attempt to enter any formula. What does > “entered normally” mean? Are you suggesting that > properly used array functions are more prone to error > than properly entered non-array functions, > alternatively that the errors are harder to pick up? > At best, that’s very disputable. Perhaps if you give > a specific example…. Hi Rugila, sorry if I wasnt clear on this. By normally entered I mean by just pressing the Enter key as opposed to Ctrl+Shift+Enter. Perhaps I should have called this the 'standard method'. What I mean is that if you take Andrews array entered formula ... =SUM(IF(B2:B30="T",IF(C2:C30<5,A2:A30))) And supposing the values are as follows:- A2:A8 = 1,2,3,4,5,6,7 B2:B8 = T,T,T,A,A,A,A C2:C8 = 1,3,5,7,9,11,13 Then Andrews formula with Ctrl+Shift+Enter returns 3 as rows 2&3 are true. However if you use the same formula but just press Enter the formula returns 28, being the total of all column A regardless of the other 2 conditions. So if whoever created the formula has other people using the workbook anbd someone need to say extend the range then if they dont press Ctrl+Shift+Enter the result changes. The cell is not in error and with a large range of data rather than just the 7 I gave above then this may not be noticed by anyone until much later. This is what Im meaning. However, just because people may make mistakes is no proper argument (to me anyway) to not use an array, but is something you may wish to consider if many people are using the book. Of course you may opt to protect sheet etc. All that I am attempting to do is make it clear for people reading this thread that you need to be careful when using arrays.	parry (27)
260432	2004-08-13 03:51:00	... "you need to be careful with using arrays" ... "But the numbers come out of a computer, so they must be right." In fact, you need to be careful when using computers. It's not arrays that are the problem. It's, as always, people. :D	Graham L (2)
260433	2004-08-13 04:13:00	You need to be very careful using spreadsheets. ]:) Just for fun I tried "spreadsheet+errors" to Google. This paper by Panko (panko.cba.hawaii.edu) should be compulsory (scary) reading. I like his comment that the increase (to 91%) in the percentage of spreadsheets found with significant errors in studies after 1997 comes from improvement in the auditing methods. ;-)	Graham L (2)
260434	2004-08-13 05:43:00	Interesting stats Graham. Does that mean the other 9% were empty spreadsheets? :-)	parry (27)
1 2 3