Harold, thank you: the ISNUMBER function works wonders. With one single command I have now filled the whole 2-dimensional array with calculated data, without burps on null entries.
I'll have to get experience with 'dragging' before it works, but since I now know better how to use the array function I may not even need it. Thank you again. Nino On Sun, 2006-12-03 at 22:43 +0000, Harold Fuchs wrote: > On Sunday, December 03, 2006 5:25 PM [GMT+1=CET], Nino Pereira > <[EMAIL PROTECTED]> wrote: > > >> Harold, > >> > >> thank you very much for looking at this problem in such a detailed > >> manner, and in the process teaching me various things I had long > >> wondered about, viz., the 'absolute' referencing. That will be > >> helpful for years to come. Of course I should have read the docs, > >> but you know how it is: if you need something right now, you don't > >> spend a lot > >> of time reading about all kinds of things that seem irrelevant for > >> the problem at hand. > >> > >> So, apparently, the problem is in the 'array formulas' that don't > >> do exactly what you might think they should. That looks indeed > >> like a bug (and not a feature: is this something the developers > >> should be made aware of? or, do they know because they may follow > >> our conversation?). > >> > >> One problem remains: I can't use your work-around they way you > >> describe it. When I click on a cell I don't get the little + sign, > >> and dragging doesn't do anything. What I do instead is copy the > >> cell contents to some kind of internal storage, a clipboard > >> (with ctrl c), move over to the next cell, and then copy > >> it there (with ctrl v). I have become very adept at this, alternating > >> (ctrl V) with the side or down arrow. Copying this way works also > >> for a whole array, so once you have done (in this example) the days, > >> you can repeat it for the various contracts. And, once you have > >> this, you can do it for the entire two-dimensional field. FYI, I have > >> attached the final result. > >> > >>> From my parochial perspective this solves my immediate problem. > >> Still, doing it this way is more time consuming than with the > >> array feature, which is exactly why I loved it when I found out > >> that it existed. Too bad it doesn't work as I thought it should. > >> > >> Thank you again. Please let me know if I should follow up on > >> this in any way. > >> > >> Nino > <snip> > Nino, > > 1. Someone else mentioned using the ISNUMBER function. I have confirmed > on my own system that this makes your problem go away even with array > formulae. So "=10*ISNUMBER(A1:A10)" works with nuls/text in A1:A10. > > 2. Perhaps I wasn't specific enough about how to do the copy/drag thing. > Enter a set of numbers into say A1:A10. In B1 put "=10*A1" (without the > quotes) and hit the Enter key. Now click in cell B1. You should get a > thickish black border round the cell and a tiny black square in the > bottom right corner of the cell. Put the cursor, carefully, over this > black square; the cursor should change to a plus sign. When it's a plus > sign, press and hold down the left mouse button and drag downwards to > B10. As you drag down, the cells you pass over will acquire red borders. > When you get to B10 release the mouse button. The cells B1:B10 will turn > black. Click the left button and the cells will acquire correct values > i.e 10 times the corresponding A cell with zeros for places where the A > cell is null/text. You do need a steady hand for this ;-) > > Regards, Harold > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
