![]() Cell D17 holds the largest of the 16 items, SMALL(… 16). In the cell below, SMALL sorts the same values again and picks the second smallest, SMALL(… 2). Otherwise OFFSET selects the 16 used items, then SMALL sorts them and picks the smallest for the first value in D. The formula in column D checks if the item in column A is empty. Column C is an ascending series which numbers the items. Cell B2 uses COUNTA to determine how many of the 20 cells in the first column are in use. ![]() Here's how the formulas in the first sheet work. On current hardware you won't notice a delay for 20 items, but you probably will for 20,000 items, which implies 20,000 sorts of 20,000 items. Thus for 20 rows, 20 individual sorts are performed. But for each row the formula sorts all 20 items, and then discards 19 of them, as explained below. For large amounts of data, the formulas will be slow because they are inefficient.You must make a copy of the data because formulas cannot sort "in place", so the size of your spreadsheet will grow.The formulas are complicated to build, and doing so may take more of your time than simply using Data, Sort even if you have to do that dozens of times. ![]() There are some disadvantages to this technique, however: See OOo Help if you don't know about the fill handle. The capacity of the sheet can be expanded beyond 20 by adjusting the text $21, which occurs in some of the formulas, to a larger number using the Edit, Find & Replace dialog and then filling the formulas down to unused rows with the fill handle. It provides for sorting up to 20 numbers, of which 16 are currently in use. The first sheet in the attached file shows how to do that. It is possible to construct formulas which will maintain a copy of the data in sorted order. ![]() However this does not keep the data sorted if changes are made to it after sorting. The easiest way to sort data in Calc is to use Data, Sort from the menu. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |