Fireworkz now uses the global clipboard for copy and paste, and to a more limited extent, cut. Fireworkz documents can accept clipboard data in Fireworkz native format and any other that it can import. Fireworkz documents can supply clipboard data in Fireworkz native format and any other that it can export.
There is now an Effects sub-menu on the style menu giving menu access to several style effects you may wish to apply; it also designates control key shortcuts for underline, superscript and subscript. This is defined by the new Config file.
There is now an option 'Recalc on load' in the Choices dialogue box which allows Fireworkz to force recalculation of spreadsheets on loading: you may wish to set this option if you regularly modify spreadsheets on which other documents depend without having those dependent documents loaded. The default is off, which allows Fireworkz to simply use the recalculated values stored in the document when it was last saved. Certain spreadsheet functions always force recalculation of themselves (and their dependent cells) on reload in any case (the GRAND(), INPUT(), NOW(), PAGE(), PAGES(), RAND(), RANDBETWEEN(), TODAY() and VERSION() spreadsheet functions). Charts always recalculate in any case as the picture is not stored with the document.
Several toolbar buttons have been added since the documentation was printed; simply point at them and the status line will tell you what they can do.
The toolbar in Fireworkz is actually configurable.
The file Choice18
controls the order in which tools appear on each toolbar - it may be found in the directory
RISC OS: <Fireworkz$Dir>.AppData.UK.Choices
Windows: %FireworkzDir%\AppData\UK\Choices\
.
Examples:
{Tool-t:0;VIEW}
puts the View Control button on toolbar 0 (default){Tool-t:1;FILL_DOWN}
is on toolbar 1 (spreadsheet){Tool-t:1}
puts a small separator before the next button on the given toolbar.You can reorder the commands within each toolbar group. We recommend not moving buttons between groups unless you are very careful. We also strongly recommend that you make a backup of this file before modifying it.
There are several other buttons defined that you can add that may be useful for use in primary schools: PLUS, MINUS, TIMES, DIVIDE.
These now appear in the Choice18
file commented out thus
{ToolPossible-t:0;PLUS}
.
Simply delete the word Possible to make the button work.
You can similarly comment out existing buttons.
The Tab well has been removed from the default toolbar by commenting it out using the ToolPossible commands (so it's easy to restore if you want it).
HKEY_CURRENT_USER\SOFTWARE\Colton Software\Fireworkz
Fireworkz uses entries in this section of the Registry for user-specific configuration.
ButtonStyle
: This controls the appearance and behaviour of the toolbar buttons in Fireworkz when themes are not being used.
ButtonStyle
=97 - Office 97 style buttons (default)ButtonStyle
=95 - Windows 95 style buttonsReportEnable
: Flag to allow some build-selective reporting by Fireworkz.
UserPath
: Location of Fireworkz' user-writable files such as Choices and Dicts.
2.00: InstalledDefaultTemplate
: <version>;.<name> - version and name of installed default template file in the user's profile.
2.00: InstalledStandardTemplates
: <version>;.<name> - list of versions and names of installed standard template files in the user's profile.
HKEY_LOCAL_MACHINE\SOFTWARE\Colton Software\Fireworkz
Fireworkz uses entries in this section of the Registry for machine-wide configuration.
ButtonStyle
: This controls the appearance and behaviour of the toolbar buttons in Fireworkz if not specified for the current user.
Directory
: Installation directory of Fireworkz.
SystemPath
: Locations of Fireworkz' data files (analagous to RISC OS Fireworkz$Path).
Usr1
: User name
Usr2
: Organisation name (this may be left blank or omitted entirely).
2.00: DefaultTemplate
: <version>;.<name> - version and name of default template file to be installed in each user's profile.
2.00: StandardTemplates
: <version>;.<name> - list of versions and names of standard template files to be installed in each user's profile.
2.20: ResourcesPath
: Locations of Fireworkz' Windows-specific data files (analagous to RISC OS FireworkzRes$Path).
Redundant: DialogStyle
: Previously controlled the appearance of the dialogue boxes in Fireworkz.
Redundant: RegistrationNumber
: Encoded version of the registration number.
Only present on old installations.
Starting with 1.35, all Arithmetic functions are reclassified as Mathematical functions.
Starting with 2.00, all Complex number functions are reclassified as Engineering functions.
Starting with 2.00, date/time values may have fractional values added and subtracted from them.
Starting with 2.00, evaluator coercion of real numbers to (internal) spreadsheet functions which
require integer parameters now applies the same rounding as ODF.INT() to improve accuracy.
This is similar rounding (around the 14th significant place) as applied to INT() in 1.30/05
such that formulae which are affected by the imprecise floating point representation of
fractional numbers e.g. INT((0.06-0.02)/2E-6) yield the correct integer value.
Note that all such extra rounding in the evaluator, including that applied by INT(),
may be completely suppressed by deselecting the option in the Choices dialogue box.
Starting with 2.10, spreadsheet functions that take date parameters have been extended to accept numeric input. If a number is supplied where a date is required, it is treated as an OpenDocument / Microsoft Excel-compatible serial number and converted to a date value. This allows data sets containing such numeric values to be more easily processed in Fireworkz.
Starting with 2.24, this function will accept a parameter to set which day to base the calculation on.
See DAYNAME.
Starting with 2.00, this function will accept a date as well as a time. Previously this would have given an error.
See TIMEVALUE.
Starting with 2.24, this function will accept a parameter to set which day to base the calculation on.
See WEEKDAY.
Starting with 2.00, complex number engineering functions will also accept Open Document / Microsoft Excel-style
complex number strings
(e.g. "2+1i", "1", "-j") as well as 2x1 arrays of numbers.
The acceptable imaginary numbers suffix is either lower-case i or j,
and where a function takes a pair of complex numbers and an imaginary number suffix is specified on both numbers,
both numbers need not use the same suffix (Microsoft Excel would require both to be the same) and 'i' will dominate.
Starting with 2.24, this function may take a single parameter as the real part of the resulting complex number with imaginary part zero.
Starting with 2.00, this function will accept an extra optional parameter, factor.
Starting with 2.24, this function will accept a non-integer period.
See DDB.
Starting with 1.34/10, this function will always use the faster
binary chop algorithm to help speed up searching large ranges.
It will also accept a fourth optional parameter lookup_type:number
with meanings the same as that for MATCH, i.e.:
See HLOOKUP.
Changes as per HLOOKUP.
See VLOOKUP.
Starting with 2.00, this function will return an OpenDocument / Microsoft Excel-compatible serial number when passed a date parameter. Previously this would have given an error.
The conversion is performed such that all dates starting from 01-Mar-1900 yield the same number as Microsoft Excel.
Earlier dates in 1900 do not match in Microsoft Excel as that incorrectly believes 1900 to be a leap year.
Unlike Microsoft Excel, Fireworkz can also yield serial numbers for dates before 1900.
See INT.
Starting with 2.00, this function will accept an extra optional parameter, b, which then
returns the logarithm of x to base b.
If b is omitted, the logarithm of x to base 10 is returned as before.
See LOG.
Starting with 1.34/10, this function will allow the inverse of a matrix to be calculated for n > 3.
See M_INVERSE.
Starting with 2.00, this function will accept zero for the column and/or row number.
If the column number is zero, the whole of the specified row(s) is returned.
If the row number is zero, the whole of the specified column(s) is returned.
Starting with 2.00, this function will accept zero for the optional column and/or row size.
If the column size is zero, the rest of the specified row(s) beginning at the given column number is returned.
If the row size is zero, the whole of the specified column(s) beginning at the given row number is returned.
See INDEX.
Starting with 2.00, all the supplied arbitrary dimensioned array or range of data is binned.
Prior to that only the first column of this data would have been binned.
See BIN.
Starting with 2.00, this function will now recalculate automatically on reload.
See GRAND.
Starting with 2.00, this function will accept a list of parameters (like AVG etc.)
See MEDIAN.
Starting with 2.20, this function will return zero if supplied with an empty string (compatible with LibreOffice Calc behaviour).
Starting with 2.00, this function will no longer allow Fireworkz' internal inline escape character (ASCII 7) to be inserted into a string (would subsequently crash the text formatter).
See CHAR.
Starting with 2.00, this function takes notice of the Alternate formula style choice.
If set, this choice outputs a string with an initial equal sign, upper-case function names and cell references and cell references in ranges are given with a colon separator.
See FORMULA_TEXT.
Starting with 2.00, this function will accept a length of zero. If the length parameter is omitted, length of one is assumed.
See LEFT.
Clarified parameter usage such that length == zero is not a fault but simply allows insertion of new_text at start_n.
See REPLACE.
Starting with 2.00, this function will accept a repeat count of zero, which returns an empty string.
See REPT.
Starting with 2.00, this function will accept a length of zero. If the length parameter is omitted, length of one is assumed.
See RIGHT.
Starting with 2.24, this function can accept text strings longer than 200 characters as input.
See TRIM.
Starting with 2.24, this function can accept text strings longer than 200 characters as input.
See VALUE.
As Fireworkz C_COMPLEX() but always produces an OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.COMPLEX.
As Fireworkz C_RADIUS() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMABS.
As Fireworkz C_IMAGINARY() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMAGINARY.
As Fireworkz C_THETA() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMARGUMENT.
As Fireworkz C_CONJUGATE() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMCONJUGATE.
As Fireworkz C_COS() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMCOS.
As Fireworkz C_DIV() but requires OpenDocument / Microsoft Excel-style complex number strings.
This function was added in 2.00.
See ODF.IMDIV.
As Fireworkz C_EXP() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMEXP.
As Fireworkz C_LN() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMLN.
Returns the logarithm to base ten of complex number.
Requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMLOG10.
Returns the logarithm to base two of complex number.
Requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMLOG2.
As Fireworkz C_POWER() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMPOWER.
As Fireworkz C_MUL() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMPRODUCT.
As Fireworkz C_REAL() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMREAL.
As Fireworkz C_SIN() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMSIN.
As Fireworkz C_SQRT() but requires OpenDocument / Microsoft Excel-style complex number string.
This function was added in 2.00.
See ODF.IMSQRT.
As Fireworkz C_SUB() but requires OpenDocument / Microsoft Excel-style complex number strings.
This function was added in 2.00.
See ODF.IMSUB.
As Fireworkz C_ADD() but requires OpenDocument / Microsoft Excel-style complex number strings.
This function was added in 2.00.
See ODF.IMSUM.
As Fireworkz FV() but with different parameter list order and sign of result.
This function was added in 2.00.
See ODF.FV.
As Fireworkz IRR() but with different parameter list order and optional guess parameter.
This function was added in 2.00.
See ODF.IRR.
As Fireworkz PMT() but with different parameter list order and sign of result.
This function was added in 2.00.
See ODF.PMT.
Returns an OpenDocument / Microsoft Excel-compatible number corresponding to the type of parameter.
This function was added in 2.00.
See ODF.TYPE.
Returns the value of the probability density function (PDF) or the cumulative distribution function (CDF) for the beta distribution with given alpha and beta shape parameters.
If lower_limit is omitted the standard beta distribution lower limit of zero is used.
If upper_limit is omitted the standard beta distribution upper limit of one is used.
cumulative selects CDF (TRUE
, default) or PDF (FALSE
).
As BETA.DIST() but with different parameter list order.
This function was added in 2.00.
See ODF.BETADIST.
Returns the product of all those values in the range for which the condition is satisfied.
This function was added in 2.00.
See DPRODUCT.
This function returns the number of days between the given dates.
This function was added in 2.24.
See DAYS.
This function returns the number of days between the given dates according to the desired 360-day calendar method.
See http://en.wikipedia.org/wiki/360-day_calendar.
This function was added in 2.00.
See DAYS_360.
This function returns the week number corresponding to the date as specified by ISO 8601.
This function was added in 2.24.
See ISOWEEKNUM.
Returns the complex number array formed from the given real part and optional imaginary part. An optional imaginary number suffix may be supplied, in which case a complex number string is returned.
This function was added in 2.00.
Starting with 2.24, this function may take a single parameter as the real part of the resulting complex number with imaginary part zero.
See C_COMPLEX.
Returns the complex conjugate of complex_number.
This function was added in 2.00.
See C_CONJUGATE.
Returns the imaginary part of complex_number as a real number.
This function was added in 2.00.
See C_IMAGINARY.
Returns the real part of complex_number as a real number.
This function was added in 2.00.
See C_REAL.
Separately rounds the components of complex_number to digits decimal places.
digits must be an integer, either positive or negative (which rounds to the left of the decimal point).
This function was added in 2.00.
See C_ROUND.
Returns the square root of complex_number.
This function was added in 2.00.
See C_SQRT.
Returns the n-th order modified Bessel function of the first kind. In(x).
This function was added in 2.00.
See BESSELI.
Returns the n-th order Bessel function of the first kind. Jn(x).
This function was added in 2.00.
See BESSELJ.
Returns the n-th order modified Bessel function of the second kind. Kn(x).
This function was added in 2.00.
See BESSELK.
Returns the n-th order Bessel function of the second kind. Yn(x).
This function was added in 2.00.
See BESSELY.
Converts the binary number to its decimal value.
number may be a string, or a number, of up to ten binary digits.
This function was added in 2.24.
See BIN2DEC.
Converts the binary number to a hexadecimal representation with places digits.
number may be a string, or a number, of up to ten binary digits.
If specified, places must be in the range 1..10. If places is omitted, the minimum appropriate number of digits is returned.
This function was added in 2.24.
See BIN2HEX.
Converts the binary number to an octal representation with places digits.
number may be a string, or a number, of up to ten binary digits.
If specified, places must be in the range 1..10. If places is omitted, the minimum appropriate number of digits is returned.
This function was added in 2.24.
See BIN2OCT.
Returns the bit-wise AND of number_1 and number_2.
This function was added in 2.24.
See BITAND.
Returns number shifted left bit-wise by shift bits.
If shift is negative, a right shift is performed.
This function was added in 2.24.
See BITLSHIFT.
Returns the bit-wise OR of number_1 and number_2.
This function was added in 2.24.
See BITOR.
Returns number shifted right bit-wise by shift bits.
If shift is negative, a left shift is performed.
This function was added in 2.24.
See BITRSHIFT.
Returns the bit-wise exclusive-OR of number_1 and number_2.
This function was added in 2.24.
See BITXOR.
Converts the decimal number to a binary representation with places digits.
number may be a positive number or a string of decimal digits.
If specified, places must be in the range 1..10. If places is omitted, the minimum appropriate number of digits is returned.
This function was added in 2.24.
See BIN2HEX.
Converts the decimal number to a hexadecimal representation with places digits.
number may be a positive number or a string of decimal digits.
If specified, places must be in the range 1..10. If places is omitted, the minimum appropriate number of digits is returned.
This function was added in 2.24.
See BIN2HEX.
Converts the decimal number to an octal representation with places digits.
number may be a positive number or a string of decimal digits.
If specified, places must be in the range 1..10. If places is omitted, the minimum appropriate number of digits is returned.
This function was added in 2.24.
See DEC2OCT.
Returns the value 1 if number_1 == number_2, else 0.
If number_2 is omitted, 0 is used.
This function was added in 2.00.
See DELTA.
Returns the error function integrated between zero and a limit value, or between a lower limit and an optional upper limit.
This function was added in 2.00.
See ERF.
Returns the complementary error function integrated between a lower limit and +infinity.
This function was added in 2.00.
See ERFC.
Returns the value 1 if a number is greater than, or equal to, the step value, else 0.
If step is omitted, 0 is used.
This function was added in 2.00.
See GESTEP.
Converts the hexadecimal number to a binary representation with places digits.
number may be a string of up to ten hexadecimal digits or a number of up to ten digits.
If specified, places must be in the range 1..10. If places is omitted, the minimum appropriate number of digits is returned.
This function was added in 2.24.
See HEX2BIN.
Converts the hexadecimal number to its decimal value.
number may be a string of up to ten hexadecimal digits or a number of up to ten digits.
This function was added in 2.24.
See HEX2DEC.
Converts the hexadecimal number to an octal representation with places digits.
number may be a string of up to ten hexadecimal digits or a number of up to ten digits.
If specified, places must be in the range 1..10. If places is omitted, the minimum appropriate number of digits is returned.
This function was added in 2.24.
See HEX2OCT.
Converts the octal number to a binary representation with places digits.
number may be a string, or a number, of up to ten octal digits.
If specified, places must be in the range 1..10. If places is omitted, the minimum appropriate number of digits is returned.
This function was added in 2.24.
See OCT2BIN.
Converts the octal number to its decimal value.
number may be a string, or a number, of up to ten octal digits.
This function was added in 2.24.
See OCT2DEC.
Converts the octal number to a hexadecimal representation with places digits.
number may be a string, or a number, of up to ten octal digits.
If specified, places must be in the range 1..10. If places is omitted, the minimum appropriate number of digits is returned.
This function was added in 2.24.
See OCT2HEX.
Calculates the future value of principal given the series of compounded interest rates.
This function was added in 2.00.
See FVSCHEDULE.
Microsoft Excel compatible function. As Fireworkz TERM() but with different parameter list order and sign of result.
This function was added in 2.00.
See NPER.
Returns the logical AND of all the Logical values in list.
Unlike the corresponding AND logical operator, &, all parameters of the AND() function are always evaluated.
This function was added in 2.00.
See AND.
Returns the Logical constant FALSE (numeric value zero).
This function was added in 2.00.
See FALSE.
Returns the logical negation of value as per the corresponding ! operator.
This function was added in 2.00.
See NOT.
Returns the logical OR of all the Logical values in list.
Unlike the corresponding OR logical operator, |, all parameters of the OR() function are always evaluated.
This function was added in 2.00.
See OR.
Returns the Logical constant TRUE (numeric value one).
This function was added in 2.00.
See TRUE.
Returns the logical exclusive-OR of all the Logical values in list.
This function was added in 2.00.
See XOR.
Converts number using the given radix.
If radix is greater than ten, letters in the range A to Z are used to represent ten upwards.
If minimum_length is specified, zeros are used to pad the conversion at the left.
Revisions:
This function was added in 2.24.
See BASE.
Rounds number to the given multiple towards +infinity (or away from zero, if both are negative).
If multiple is omitted, 1 is used.
This function was added in 2.00.
See CEILING.
Converts a number in a given base to its corresponding decimal value.
This function was added in 2.24.
See DECIMAL.
Rounds number up to the next even integer, away from zero.
This function was added in 2.00.
See EVEN.
This function yields the factorial of n (most commonly shortened to n!).
n must be an integer greater than or equal to zero.
See FACT.
Rounds number to the given multiple towards -infinity (or towards zero, if both are negative).
If multiple is omitted, 1 is used.
This function was added in 2.00.
See FLOOR.
Returns the greatest common denominator of all the values in the list.
This function was added in 2.24.
See GCD.
Returns the least common multiple of all the values in the list.
This function was added in 2.24.
See LCM.
Rounds number to the given multiple.
This function was added in 2.00.
See MROUND.
Returns the multinomial of all the numeric values in list.
This function was added in 2.00.
See MULTINOMIAL.
Rounds number up to the next odd integer, away from zero.
This function was added in 2.00.
See ODD.
Returns the number a raised to the power of b.
This function was added in 2.00.
See POWER.
Returns the product of all the values in the list, which is usually a range of cells.
This function was added in 2.00.
See PRODUCT.
Truncates the result of numerator ÷ denominator to an integer, always towards zero.
This function was added in 2.00.
See QUOTIENT.
Returns a random integer in the interval [lower,upper].
This function was added in 2.00.
See RANDBETWEEN.
Rounds number down to digits decimal places, towards zero.
digits must be an integer, either positive or negative (which rounds to the left of the decimal point).
This function was added in 2.00.
See ROUNDDOWN.
Rounds number up to digits decimal places, away from zero.
digits must be an integer, either positive or negative (which rounds to the left of the decimal point).
This function was added in 2.00.
See ROUNDUP.
Returns the sum of the given power series for x.
n is the initial power to which x is raised,
this being multiplied by the first coefficient in the array.
For each subsequent coefficient, m is added to the power.
This function was added in 2.00.
See SERIESSUM.
Returns the sum of the products of all the corresponding values in each of the given array parameters.
This function was added in 2.00.
See SUMPRODUCT.
Returns the sum of the squares of all the values in the list.
This function was added in 2.00.
See SUMSQ.
Sum of the difference of the squares of the corresponding values from the x-data array and y-data array.
This function was added in 2.00.
See SUM_X2MY2.
Sum of the sum of the squares of the corresponding values from the x-data array and y-data array.
This function was added in 2.00.
See SUM_X2PY2.
Sum of the squares of the difference of the corresponding values from the x-data array and y-data array.
This function was added in 2.00.
See SUM_XMY2.
Truncates number to digits decimal places, always towards zero.
If supplied, digits must be an integer, either positive or negative (which applies truncation to the left of the decimal point).
If digits is omitted, zero is used.
This function was added in 2.00.
See TRUNC.
Returns the Logical value TRUE
if number is an even integer, otherwise returns FALSE
.
Non-integral values are first truncated.
This function was added in 2.00.
See ISEVEN.
Returns the Logical value TRUE
if number is an odd integer, otherwise returns FALSE
.
Non-integral values are first truncated.
This function was added in 2.00.
See ISODD.
Provided to allow some Microsoft Excel and Lotus 1-2-3 spreadsheets to load.
This function was added in 2.00.
See N.
Returns the average deviation of the values in list from their arithmetic mean.
This function was added in 2.00.
See AVEDEV.
See BETA.
Returns the value of the probability density function (PDF) or the cumulative distribution function (CDF) for the beta distribution with given alpha and beta shape parameters.
cumulative selects CDF (TRUE
, default) or PDF (FALSE
).
If lower_limit is omitted the standard beta distribution lower limit of zero is used.
If upper_limit is omitted the standard beta distribution upper limit of one is used.
See BETA.DIST.
This function was added in 2.00.
nCk = n! / ((n-k)! k!)
This function yields the number of combinations of n unlike things taken k at a time.
For very large n this is an approximation achieved using logarithms.
See COMBIN.
Returns the number of blank cells (or empty text strings) in range.
This function was added in 2.00.
See COUNTBLANK.
Returns the population covariance of the two arrays.
This function was added in 2.00.
See COVARIANCE.P.
Returns the sample covariance of the two arrays.
This function was added in 2.00.
See COVARIANCE.S.
Returns the sum of the squares of the deviations of the values in the list from their arithmetic mean.
This function was added in 2.00.
See DEVSQ.
Returns the value of the probability density function (PDF) or the cumulative distribution function (CDF) for the exponential distribution with parameter lambda.
cumulative: selects CDF (TRUE
, default) or PDF (FALSE
).
This function was added in 2.00.
See EXPON.DIST.
Returns the predicted y-value corresponding to the given x-value using the linear regression line given by the y-data and x-data arrays.
This function was added in 2.00.
See FORECAST.
Returns the geometric mean of the values in the list.
All values in the list must be positive, non-zero numbers.
This function was added in 2.00.
See GEOMEAN.
This special function yields the value of the gamma function of x, Γ(x).
This function was added in 2.00.
See GAMMA.
Returns the value of the probability density function (PDF) or the cumulative distribution function (CDF) for the gamma distribution with given shape and scale parameters.
cumulative selects CDF (TRUE
, default) or PDF (FALSE
).
This function was added in 2.00.
See GAMMA.DIST.
This special function yields the natural logarithm of the absolute value of the gamma function of x.
See GAMMALN.
Returns the harmonic mean of the values in the list.
All values in the list must be non-zero numbers.
The harmonic mean is the reciprocal of the arithmetic mean of the reciprocals of the values.
This function was added in 2.00.
See HARMEAN.
Returns the value of the probability density function (PDF) or the cumulative distribution function (CDF) for the hypergeometric distribution for a sample with sample_successes, number_sample and a population with population_successes, number_population.
cumulative selects CDF (TRUE
) or PDF (FALSE
, default).
This function was added in 2.00.
See HYPGEOM.DIST.
Returns the intercept of the linear regression line given by the y-data array and x-data array.
This function was added in 2.00.
See INTERCEPT.
Returns the k-th largest value in array.
This function was added in 2.00.
See LARGE.
Returns the value of the probability density function (PDF) or the cumulative distribution function (CDF) for the log-normal distribution with given mean and standard deviation.
cumulative: selects CDF (TRUE
, default) or PDF (FALSE
).
This function was added in 2.00.
See LOGNORM.DIST.
Returns the mode of array.
The mode is the most frequently occuring value in a data set.
This function was added in 2.00.
See MODE.SNGL.
Returns the k-th (exclusive) percentile value in array where k is in (0,1).
This function was added in 2.00.
See PERCENTILE.EXC.
Returns the k-th (inclusive) percentile value in array where k is in [0,1].
This function was added in 2.00.
See PERCENTILE.INC.
nPk = n! / (n-k)!
This function yields the number of permutations of n unlike things taken k at a time.
For very large n this is an approximation achieved using logarithms.
See PERMUT.
Returns the value of the probability density function (PDF) or the cumulative distribution function (CDF) for the normal distribution with given mean and standard deviation.
cumulative selects CDF (TRUE
, default) or PDF (FALSE
).
This function was added in 2.00.
See NORM.DIST.
Returns the value of the probability density function (PDF) or the cumulative distribution function (CDF) for the standard normal distribution (i.e. the normal distribution with mean value of zero and standard deviation of one).
cumulative selects CDF (TRUE
, default) or PDF (FALSE
).
This function was added in 2.00.
See NORM.S.DIST.
Returns the value of the probability density function for the standard normal distribution (i.e. the normal distribution with mean value of zero and standard deviation of one).
This function was added in 2.00.
See PHI.
Returns the value of the probability density function (PDF) or the cumulative distribution function (CDF) for the Poisson distribution with parameter lambda.
cumulative selects CDF (TRUE
, default) or PDF (FALSE
).
This function was added in 2.00.
See POISSON.DIST.
Returns the q-th quartile value in array where q is one of:
q=1 25% Q1
q=2 50% Q2 (MEDIAN)
q=3 75% Q3
This function was added in 2.00.
See QUARTILE.EXC.
Returns the q-th quartile value in array where q is one of:
q=0 0% (MIN)
q=1 25% Q1
q=2 50% Q2 (MEDIAN)
q=3 75% Q3
q=4 100% (MAX)
This function was added in 2.00.
See QUARTILE.INC.
Returns the rank of number in the given array.
If order is zero or omitted, array is treated as being sorted in descending order.
If order is non-zero, array is treated as being sorted in ascending order.
This function was added in 2.00.
See RANK.EQ.
Returns the slope of the linear regression line given by the y-data array and x-data array.
This function was added in 2.00.
See SLOPE.
Returns the k-th smallest value in array.
This function was added in 2.00.
See SMALL.
Normalises x to the normal distribution given by mean and standard deviation.
This function was added in 2.00.
See STANDARDIZE.
Returns the standard error of the linear regression line given by the y-data and x-data arrays.
This function was added in 2.00.
See STEYX.
Returns the arithmetic mean value of the interior of the data in array (or range of cells) when the lower and upper percent range of values are excluded from the calculation.
percent should be in the range 0.0..0.5
(i.e. 0 to 50%).
This function was added in 2.00.
See TRIMMEAN.
Returns the value of the probability density function (PDF) or the cumulative distribution function (CDF) for the Weibull distribution with given shape and scale parameters.
cumulative selects CDF (TRUE
) or PDF (FALSE
).
This function was added in 2.00.
See WEIBULL.DIST.
Removes all unprintable characters from text.
This function was added in 2.00.
See CLEAN.
Converts number into a string with a currency symbol, thousands commas, digits decimal places and with negative numbers in parentheses.
Note that any rounding is carried out according to the same formula as that used by the ROUND
function.
This function is like STRING
but with the addition of a currency symbol, thousands commas and negative parentheses.
This function was added in 2.00.
See DOLLAR.
Converts number into a string with digits decimal places.
Note that any rounding is carried out according to the same formula as that used by the ROUND
function.
This function is like STRING
when no_commas is FALSE
, otherwise STRING()-like but with the addition of thousands commas.
This function was added in 2.00.
See FIXED.
Replaces occurences of the old text string with the new text string in text.
If instance is omitted, all occurences of the old text string are subtituted.
If instance is supplied, only the instance-th occurence of the old text string is subtituted.
This function was added in 2.00.
See SUBSTITUTE.
Provided to allow some Microsoft Excel and Lotus 1-2-3 spreadsheets to load.
This function was added in 2.00.
See T.
The printed Spreadsheet Guide p32 para2 refers to using the TRANSPOSE()
spreadsheet function in order to process columns of data into rows for sorting.
This should say: 'If you want to sort data that is organised along rows,
not down columns, you will need to use the TRANSPOSE() spreadsheet function
to swap the data around'.
Please note that the automatic detection of number styles on numeric entry only operates when the Edit in cell option is switched on.
The printed Spreadsheet Guide omits the ability of the DAYNAME() and MONTHNAME() spreadsheet functions to convert from a date parameter. See DAYNAME and MONTHNAME.