Posted: Tue Mar 22, 2005 9:43 am Post subject: Help with arrays in Calc
Many thanks to the NeoOffice/J community & especially Patrick & Ed for an excellent app. For the price of M$ Office, I bought a Mac Mini instead & haven't booted my Linux box for weeks.
In preparing to file taxes, I wanted to do a monthly summary of some records kept over the last year. I found the perfect solution in the help file:
Quote:
Enter the following formula as an array formula:
=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)
In order to enter this as a array formula, you must press the Shift Command + Enter key instead of simply pressing the Enter key to close the formula. The formula will then be shown in the Formula bar enclosed in braces.
I can't, though, seem to locate the requested key combination. Shift is easy. For command I usually hit the Windows key on my M$ Natural keyboard (though I've also tried Alt & Ctl). I've tried both regular and numeric enter keys.
The Command key is the key with the outline of an apple on it. Most Mac apps use the Command key instead of the Control key. The Control was only added to Macs many years ago to be compatible with PCs.
Joined: Sep 18, 2003 Posts: 434 Location: London, UK
Posted: Tue Mar 22, 2005 10:35 am Post subject:
Hmm, tricky one to answer as I and many others here will not have had any experience of using a non-Apple keyboard with their Mac. However, there is a workaround that may help - also from the NeoOffice Help:
Code:
Creating Array Formulas
If you create an array formula using the Autopilot: Functions, you must mark the Array check box each time so that the results are returned in an array. Otherwise, only the value in the upper-left cell of the array being calculated is returned.
If you enter the array formula directly into the cell, you must use the key combination Shift+Command+Enter instead of the Enter key. Only then does the formula become an array formula.
Array formulas appear in braces in NeoOffice/J Calc. You cannot create array formulas by manually entering the braces.
The cells in a results array are automatically protected against changes. However, you can edit or copy the array formula by selecting the entire array cell range.
Editing Array Formulas
1.Select the cell range or array containing the array formula. To select the whole array, position the cell cursor inside the array range, then press Ctrl+/, where / is the Division key on the numeric keypad.
2.Either press F2 or position the cursor in the input line. Both of these actions let you edit the formula.
3.After you have made changes, press Command+Shift+Enter.
You can format the separate parts of an array. For example, you can change the font color. Select a cell range and then change the attribute you want.
Copying Array Formulas
1.Select the cell range or array containing the array formula.
2.Either press F2 or position the cursor in the input line.
3.Copy the formula into the input line by pressing Command+C.
4.Select a range of cells where you want to insert the array formula and either press F2 or position the cursor in the input line.
5.Paste the formula by pressing Command+V in the selected space and confirm it by pressing CommandShift+Enter. The selected range now contains the array formula.
Adjusting an Array Range
If you want to edit the output array, do the following:
1.Select the cell range or array containing the array formula.
2.Below the selection, to the right, you will see a small icon with which you can zoom in or out on the range using your mouse.
When you adjust the array range, the array formula will not automatically be adjusted. You are only changing the range in which the result will appear.
By holding down the Ctrl key, you can create a copy of the array formula in the given range.
In other words, simply use the AutoPilot: functions button - the Æ’(x) next to the formula input box - to call up a dialogue for entering your formula and be sure to check the "Array" button in the bottom left corner:
Joined: May 31, 2003 Posts: 219 Location: French Alps
Posted: Wed Mar 23, 2005 1:17 pm Post subject: Re: Help with arrays in Calc
tef wrote:
I can't, though, seem to locate the requested key combination. Shift is easy. For command I usually hit the Windows key on my M$ Natural keyboard (though I've also tried Alt & Ctl). I've tried both regular and numeric enter keys.
I never used a PC keyboard on a Mac either.
Anyway, to help you identify keymaping, I suggest you open "System Preferences", then "International preference", then "Input Menu"; check the mark aside "Keyboard Viewer" (I also suggest "Character palette" for other purpose). Be sure "Show input menu in the menu bar" is checked.
Then from the input menu icon (a flag) you can open the "Keyboard Viewer" which displays depressed key(s). The command is the one with the "place of interest" symbol (a four leaf clover, sort of).
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You cannot download files in this forum
