Calculations
§1 Introduction
Besides the financial possibilities mentioned on the previous page (Accounting) the Zator's dBase have certain features that allow to use items to store numbers and dates. In these cases it is possible perform certain calculations on them, and get some basic results from the items of a group (window) or a hierarchy.
As in the previous case, this is a marginal utility, which does not prevent to be practical in some cases. Nor is it intended to replace the specific applications as spreadsheet, of which there are real marvels of sophistication. But to offer to the user who has very basic needs, a tool between the power of the traditional spreadsheet (perhaps excessive for its needs) and the traditional pen and paper, or tape calculator.
The calculations refer to two different sets of data: numbers and dates, and both can be obtained similar results including the sum; arithmetic average; geometric mean and standard deviation.
§2 Data entry
In a manner analogous to the accounting, the trick is to use items of a window (group) as data for the calculation. Here certain formal conditions are necessary too for the text in the line to be considered a numerical value or a date.
- The numerical quantities can be preceded by the symbols "+" or "-" (the first is considered implicit).
- For the separation of decimal fraction will be used the point "." (not the comma ",") and without separation between the digits in the integer part.
- The integer part can be missed if is is zero.
- Can be used scientific notation. In this case, after entering the amount in the title of this item, will be automatically converted to a decimal expression. If we do not want that to happen, the expression must be preceded or followed by a space.
- During the calculations, the spaces preceding amounts will be eliminated.
- If an item contains the # character at any position, or the first non-space character is different from a digit (0 .. 9) or a +/- sign, it is considered a commentary, and is not taken into account in calculations (*). Note that on the contrary, although not contain a valid numerical quantity, will be included in the calculations. If not contain a numerical quantity, its value will be considered zero, and influence the result. For example, in the arithmetic mean.
(*) We will see that this behavior can be modified, so that amounts may appear in any position within the title. In these cases we assume that the quantities occupy the column of n order within the title, assuming that certain characters -generally spaces- define the boundary of the hypothetical columns.
Examples:
1.10 // Ok.
-24 // Ok.
0.33 // Ok.
.33 // Ok. Equivalent to the previous
1,234.24 // NO.
1234.24 // Ok.
1234.24 This is the mean value // Ok. the text that follows will not affect the previous value
2.5E4 // Ok.
2.5e4 // Ok
2.5e4 // It will be transformed to a 25000 when entering the data
The dates should precede any other annotation in the item. The
years in four digits, and the months and days with two-digit preceded by 0 if
necessary. They are valid the following formats:
yyyy-mm-dd .........
yyyy/mm/dd .........
yyyy mm dd ........
yyyy,mm,dd ........
These calculations do not take into account items that include a hash (#) character at the beginning or do not include a valid date.
§3 Calculations
By clicking with the right mouse button on the auxiliary windows placed below the main windows, you get a contextual menu with the following choices:
- In this group:
- Numerical calculations
- Select column
- Sum
- Arithmetic mean
- Geometric mean
- Standard deviation
- Date calculations
- Select column
- Sum
- Arithmetic mean
- Geometric mean
- Standard deviation
- Accounting calculations
- Balance
- Include balance in items
- In this hierarchy:
- Numerical calculations
- Select column
- Sum
- Arithmetic mean
- Geometric mean
- Standard deviation
- Date calculations
- Select column
- Sum
- Arithmetic mean
- Geometric mean
- Standard deviation
- Accounting calculations
- Balance
- Copy
As you can see, the options that refer to numeric and date
calculations
have a similar
breakdown. The included in this group, relate to the main window above the auxiliary.
However, the included in In this hierarchy, cover all sub items dependent
(hanging) from the item in the window that has focus at that time whatever the level of
depth of this unit. Clearly providing properly the tree of items, can be obtained
the partial results that are needed, along with totals and grand totals [1].
The copy option serves to copy to the clipboard the content of the auxiliary
window (the one which contains the result).
The formulas used for the calculations, Sum; Arithmetic mean; Geometric mean and Standard
deviation can be found in any treaty on Mathematical Statistics. I.e, Wikipedia. The date calculations are
made on the amounts (integer) that result calculating the distance, in days, from
current (system) date and the indicated. I.e, if deals
with people birth dates, the average value divided by 365 gives the median age in
years of the people represented. Here are a few examples together with the results obtained.
Lines of the main window (as you can see, it is permissible to include any text after the data).
.33 a decimal
-10.30 a negative decimal
2.5e3 25oo in scientific notation (the space between the 3 and 2 is essential!)
This comment is not taken into account
120 one hundred and twenty
12o twelve (I confused zero -0- con the o letter)
Results in the auxiliary window using Numeric calculations > in this group
5 Res: 2622.030000 | Sum | |
5 Res: 524.406000 | Arithmetic mean | |
5 Res: 1#INF00 | Geometric mean | |
5 Res: 1#INF00 | Standard deviation |
Lines in the main window:
1895-09-21 Birth of Juan de la Cierva
1893-04-07 Birthday of Claudio Sanchez-Albornoz
2007-02-29 Impossible date
2007-04 Incorrect date
Results in the auxiliary window using Date calculations > In this group.
2 Res: 82759.00 # 226.7 years | Sum | |
2 Res: 41379.50 # 113.4 years | Arithmetic mean | |
2 Res: 41377.07 # 113.4 years | Geometric mean | |
2 Res: 448.50 # 1.2 years | Standard deviation |
In both cases, the first number indicates the number of items that have been involved in the calculation once excluded remarks.
§4 Select column
We have pointed out that the quantities, numerical or dates, must appear at
the beginning of the title so they can be identified as such by the calculation
routine. However, this behavior can be changed through this option.
The Select column option brings up a dialog in which you can select the
characters you consider as column separators (up to five different) and the
number of column where it is assumed that are the quantities involved in the
calculations. The characters should be indicated by their corresponding decimal
ASCII values comma separated "," [2], and
columns begins to count from zero (0 = first column).
Note that by default, have been selected as column delimiters the characters
tabulation "TAB" (9); space "
" (32); comma "," (44); semicolon ";"
(59) and vertical bar "|" (124), and that the selected column
is the first (0). So, unless otherwise specified, the calculations are done on
the first column, which corresponds to quantities that appear at the beginning
of the title.
As an real example, suppose a text file that contains statistics of access in a website. The file contains for each page of the site the following information (in the order indicated): Url-Viewed; Viewed; Average size; Entry; Exit. The appearance of a section of the file is as follows:
/Cpp/E4_9_8.htm 3671 21.12 KB 2710 2680
/Hardware/H2_5_3.htm 3518 47.39 KB 3050 3015
/Cpp/E4_4.htm 3121 11.93 KB 2215 1750
/Cpp/E1_2.htm 2974 18.60 KB 1487 1774
/Cpp/E2_2_1a.htm 2864 57.62 KB 2286 2336
/Hardware/H12_2.htm 2848 22.62 KB 2367 2245
/Hardware/H3_2.htm 2590 45.07 KB 1876 1835
/Cpp/E4_10.htm 2539 18.45 KB 1822 1485
/Cpp/E4_9_1.htm 2325 21.75 KB 1364 1458
/libreriasC.htm 2252 26.14 KB 1162 1368
/Hardware/H5_2.htm 2207 24.59 KB 1858 1854
/Cpp/E4_3_1.htm 2172 23.58 KB 1422 1293
/Hardware/H2_4.htm 2119 59.93 KB 1603 1581
/Cpp/E4_3.htm 2068 10.35 KB 1161 834
/Hardware/H12_4_2.htm 2048 48.35 KB 1671 1698
/Cpp/E4_13.htm 1868 9.34 KB 1554 1304
/Cpp/E0_1_1.htm 1791 33.69 KB 1493 1450
/Cpp/E4_9.htm 1751 18.71 KB 1115 661
/Hardware/H1_7_1.htm 1683 28.36 KB 1423 193
/Hardware/H10_1.htm 1658 38.07 KB 1468 1459
/Hardware/H4.htm 1649 10.73 KB 1078 981
/Hardware/H12_4.htm 1631 23.48 KB 1269 1279
/Hardware/H2.htm 1627 52.46 KB 1048 1069
/Cpp/E_Ce.htm 1613 31.53 KB 420 418
/Cpp/index.htm 1575 5.40 KB 474 320
/Cpp/E1_1.htm 1564 26.45 KB 1066 1119
/Cpp/E1_3.htm 1546 5.97 KB 582 692
This file does not provide statistical partial results, but it is possible, for example, calculate the page views for the Cpp Manual as follows:
- 1.- Create a new item to which we will call Visits.
- 2.- By selecting the option This group (items in this window) > Import titles from file, import the statistics file in the group that immediately depends of the item Visits that we have created (this group is initially empty). After the importation, it will appear in it as many items as there are lines in the statistics file. Each item contains the data related to a page.
- 3.- Then we click on the item entitled Visits and in the context menu, select the option Search in this hierarchy using: > Titles. Then we introduce the text /Cpp/ in the field of text searching, and start the search (the string "/Cpp/" is the path for pages of the Cpp Manual).
- 4.- When the search process ends, appear in the results window the items pertaining to visits to the manual Cpp. Then press the right button on the auxiliary window (under the result's window) and select the option Select column, then select column 1, the column containing the results that we are interested (the character space -32- is already selected by default as column delimiter).
- 5.- Then again select the context menu in the auxiliary window, and select the option: In this group> Numeric calculations > Addition.
- 6.- Then, in the auxiliary window appears the sum requested. In the case of the example is the result of the sum 3671 + 3121 + 2974 +...+ 1546
[1] The explanation about accounting calculations are included in the corresponding section of Accounting.
[2] You can get information on the ASCII values that correspond to different characters in http://www.zator.com/Cpp/E2_2_1a.htm