Z4Soft PTab Spreadsheet v.3.0 ReadMe file

Web site: www.z4soft.com
Email: email@z4soft.com

Z4Soft PTab Spreadsheet v.3.0 runs on mobile devices with Windows CE 3.x and higher and on Windows NT/2000/XP.

Installation:

1. Connect your mobile device to your desktop PC
2. Run PTabSetup.exe.  

On-line help

The on-line help is available from the Start menu when PTab is running.

How to convert Microsoft Excel (.xls) files:

PTab can load/save MS Excel files (Excel v. 5.0/95/97/2000/XP/2003) directly.

What is converted from/to Microsoft Excel:

• Cell values: numbers (incl. date/time values), texts, Boolean values, error values
• Formulas containing: numbers, date/time values, texts, Boolean values, errors, +, -, *, /, ^, %, cell references, names, functions supported by PTab
• Formats: font (font style, size, color), background color, simple borders, alignment (left, right, center, center across selection; top, center, bottom), wrap text, merge cells, cell protection, basic number formats (number of decimals, thousand separator, currency symbol, text before and after the number), basic date/time formats
• Row height, column width, sheet names, titles freezing, zoom
• Group and outline, data validation

Z4Soft PTab Spreadsheet

Z4Soft PTab has the key features of common desktop spreadsheets:

• Load/Save Microsoft Excel (.xls, Excel v. 5.0/95/97/2000/XP/2003) files directly
• Load/Save Microsoft Pocket Excel (.pxl) files directly
• Import/Export TXT, CSV (Comma Separated Values), DBF (dBase)
• 256 sheets x 65536 rows x 256 columns
• Numeric, Text, Date/Time, Boolean values
• 249 built-in scientific, statistical, financial, date/time, logical, database, text and macro functions
• Multiple Undo/Redo
• Cut, Copy, Paste, Paste Special, Clear (All, Content, Formats), Fill (Right, Down, Series) cells, Insert/Delete Rows/Columns
• Set row height and column width, hide/unhide rows and columns, autofit row heights and column widths.
• Insert function, Quick sum, Pointing
• Format: Number format (General, Financial, Percentages, date/time, user defined),   Alignment (General, Left, Center, Right, Center across selection, Top, Center, Bottom,  Wrap Text, Merge Cells), Font, Size, Color, Background Color, Bold, Italic, Underline, Borders (Left, Right, Top, Bottom, Outline), Protection (Locked, Hidden) 
• Zoom (25% - 200%) and full screen mode 
• Freeze Titles (row and column freezing)
• Formula bar or in place editing
• Show/hide: Column and row titles, Gridlines, Zero values
• Sheets: New, Rename, Delete
• Sort (Ascending, Descending, up to 3 keys), Go to cell, Find/Replace
• Simple charts (Bar, Line, Pie, XY)
• Names (define and insert)
• Protection (Protect/Unprotect Sheet, Protect/Unprotect Workbook)
• Custom lists (e.g. Monday, Thursday, Wednesday...)
• Auto filter
• Automating Tasks - user defined tasks (simple macros)
• Insert sound or hyperlink (cell reference, Web page or E-mail address)  in a cell
• Password protection
• Group and outline
• Data validation
• Goal seek

What is new in PTab v.3.0 ?

• Data validation, dropdown boxes
• Group and outline
• External references (example: [BOOK.XLS]Sheet1!A5)
• Merge cells
• Goal seek
• Max. rows: 65536
• Wildcards (*,?) in search
• Auto save
• Load/save TXT, CSV, DBF (dBase) files
• New worksheet functions: Indirect, Concatenate, Fixed, Lookup, Offset, Mirr, Type, Trim, Clean, Rank, Median, Address, Sumproduct, Avedev, Betadist, Gammaln, Betainv, Binomdist, Chidist, Chiinv, Confidence, Critbinom, Expondist, FInv, FDist, Fisher, Fisherinv, Gammainv, Hypgeomdist, Gammadist, Lognormdist, Loginv, Negbinomdist, Normdist, Normsdist, Norminv, Normsinv, Standardize, Poisson, TDist, Weibull, Sumxmy2, Sumx2my2, Sumx2py2, Chitest, Correl, Covar, Forecast, Ftest, Intercept, Pearson, Rsq, Steyx, Slope, Prob, Devsq, Geomean, Harmean, Sumsq, Kurt, Skew, Ztest, Large, Small, Percentile, Percentrank, Mode, True, False, Trimmean, Tinv, MaxA, MinA, AverageA, StdevPA, VarPA, StdevA, Subtotal, VarA
• Automating tasks for…next cycle, comments
• New automating tasks functions: InsertSheet, DeleteSheet, GoalSeek, Save, Beep, ComboBox, ClearValidation, TickCount, CellRefR, WorkbookName

What was new in PTab v.2.1 ?

• More chart types and options (auto scale, gridlines, titles, save chart settings to a cell)
• Insert picture, sound or hyperlink in a cell
• Automating tasks improvements: Cell tasks - short task ("macros") stored in a cell, new functions (PlaySound(), ShowPicture(), FileBox(), RunApp(), IBrowser(), ListBox2(), Copy(), Paste(), CellTask(), ClearFormat()) and more.
• Recent files list
• Password protection
• Context-sensitive menu

Entering Data / Editing Cells

A cell can contain: number, text, date/time, Boolean value or formula. Tap on formula bar (or double-tap the cell if formula bar is off)  to edit cell's value/formula. If the number or the date begins with a single quote ', it is treated as a text.

• Number, example: 123.45, -1.2e55
• Text
• Date/Time format is taken from the PPC regional settings (menu Start/Settings/Regional Settings, tab Number, List separators combo box). You must specify a year in the date. Examples: 8/22/99 in the U.S.A. vs. 22.8.99 in Germany, 10:44, 12/30/1999 14:25:30
• Boolean: true or false
• Formula:  Formulas begin with an equal sign =, PTab evaluates its value. Example: =A5+2, =Sum(B2:C7). It can contain:
• Number, text , date/time, Boolean values
• Cell references, example: A4, $B7, $C$3, Sheet1!A3, '2ndSheet'!$C$7, [BOOK.XLS]Sheet1!A5
• Cell ranges, example: A1:B4, 'Year 1999'!C5:D7
• Arithmetic operators: +, -, /, *, %, ^ , example: A1*B5%, C7*100-5
• Comparative operators: =, <>, <, <=, >, >=, example: if(A1>B1, A1, B1)
• Text joining operators: &, example: A1 & " years"
• Functions: see below.

File Menu

• New - creates a new workbook.
• Save - saves the current workbook. If you have not saved the workbook yet, Save works as Save As.
• Save As - saves the current workbook with a specified name.
• Recent - list of recently opened files.
• Password - set or change password.
• About - Z4Soft PTab spreadsheet: copyright, version and contact info.

Edit Menu

• Undo - undoes the last action.
• Redo - redoes the last action.
• Cut - cuts the selected cells to the clipboard.
• Copy - copies the selected cells to the clipboard. If you paste the copied contents of the clipboard into another application, cell texts separated by tabs are pasted.
• Paste - pastes the clipboard contents to the sheet. You can paste texts separated by tabs from another application.
• Paste Special - pastes the clipboard contents to the sheet using special options (paste: contents, formats, validation, only the results of formulas, skip empty cells).
• Clear All/Contents/Format/Validation - clears contents+format/contents/format of selected cells.
• Fill Right/Down/Series - fills the selected cells by the leftmost/topmost cell contents or fill Series: linear, growth, date (date unit: day, weekday, month, year) or autofill (e.g. January, February, March...).
• Delete Cells - remove the selected rows/columns.
• Insert Cells - inserts the same number of rows/columns as selected.
• Insert Function - inserts a function to the current cell.

Format menu

• Cells - changes cell(s) numeric/date&time format (you can add your own numeric/date&time format), alignment (horizontal, vertical, wrap text, merge cells), font, borders, protection (locked or hidden cells).
• Row > Height/AutoFit/Hide/Unhide - sets row height. 
• Column > Width/AutoFit/Hide/Unhide - sets column width.
• Sheets - adds/renames/deletes sheets.
• Zoom - change the magnification of the sheet. You can specify 25 - 200 percent.
• Full Screen - full screen mode on/off.
• Freeze Titles - allow row and/or column titles to stay visible as you scroll the sheet. To freeze the vertical titles, select the column to the right of where you want the split to appear. To freeze the horizontal titles, select the row below where you want the split to appear. To freeze both titles, select the cell below and to the right of where you want the split to appear.

Tools Menu

• Go to - selects specified cell or range.
• Find - finds a cell in the current sheet containing the specified text. You can search by rows or columns, look in values or formulas, search case sensitive/insensitive.
• Replace - finds and replaces a cell containing the specified text. Search options: search by rows or columns, search entire cells only, search case sensitive/insensitive.
• Sort - sorts a selected range of cells: ascending or descending, case sensitive/insensitive, sorts rows or columns, exclude header row from sort.
• Chart - creates a simple chart (Bar, Line, Pie, XY) from selected cells that contain the data and labels you want in the chart.
• Filter - auto filter on/off.
• Name > Insert/Define - defines and inserts a name in a formula.
• Protection > Protect/Unprotect Sheet/Workbook - use Format/Cells/Protection to lock/unlock and hide/unhide cells and than use Protect Sheet or Protect Workbook to activate these settings.
• Outline > Group/Ungroup/Clear/Auto – use to display only the rows or columns with headings and summaries or to display detail data adjacent to a summary row or column.
• Data Validation - use data validation to restrict cell entries within specified limits or to the data from a list.
• Goal Seek – use when you know the result of a formula but not the input value.
• Options - View options: enables/disables formula bar, showing of column and row titles, gridlines and zero values. General options: move after enter (none/right/down), full screen zoom, auto save. Custom lists: import from selection, delete list. 

Task Menu

• User Defined Tasks - user defined tasks can be added to this menu.
• Workbook Task - creates and runs automating tasks (see Automating tasks).
• Cell Task - define or modify automating tasks stored in a cell.
• Insert in Cell - inserts Picture reference, Sound reference or Hyperlink (cell reference, Web page or E-mail address)  in a cell.

Spreadsheet Functions Reference

Math & Trig Functions

Abs(number) Returns the absolute value of a number. Example: Abs(-3) equals 3
Acos(number) Returns the arccosine of a number. The number must be from -1 to 1. Example: Acos(-0.5) equals 2.094395
Acosh(number) Returns the inverse hyperbolic cosine of a number. The number must be greater or equal to 1. Example: Acosh(1) equals 0.04613
Asin(number) Returns the arcsine of a number. The number must be from -1 to 1. Example: Asin(-0.5) equals -0.5236
Asinh(number) Returns the inverse hyperbolic sine of a number. Example: Asinh(8) equals 2.77647
Atan(number) Returns the arctangent of a number. Example: Atan(0.5) equals 0.46365
Atan2(x,y) Returns the arctangent of the specified x and y coordinates. Example: Atan2(1, 1) equals 0.785398
Atanh(number) Returns the inverse hyperbolic tangent of a number. The number must be between -1 and 1 (excluding -1 and 1). Example: Atanh(-0.1) equals -0.10034
Ceiling(number, significance) Returns the number rounded up to the nearest multiple of the significance. Example: Ceiling(10.43, 0.1) equals 10.5
Combin(number, numberChosen) Returns the number of combinations. Number is the number of items, numberChosen is the number of items in each combination. Example: Combin(10, 2) equals 45
Cos(number) Returns the cosine of the given angle. Example: Cos(0.5) equals 0.87758
Cosh(number) Returns the hyperbolic cosine of a number. Example: Cosh(5) equals 74.20995
Degrees(number) Converts radians into degrees. Example: Degrees(Pi()/2) equals 90
Even(number) Returns the number rounded up to the nearest even integer. Example: Even(1.3) equals 2
Exp(number) Returns e raised to the power of the number. Example: Exp(Ln(2)) equals 2
Fact(number) Returns the factorial of a number. The number must be nonnegative number. Example: Fact(5) equals 120
Floor(number, significance) Rounds the number down, toward zero, to the nearest multiple of the significance. Example: Floor(3.5, 1) equals 3
Int(number) Rounds a number down to the nearest integer. Example: Int(1.7) equals 1
Ln(number) Returns the natural logarithm of a number. Example: Ln(Exp(2)) equals 2
Log(number, base) Returns the logarithm of a number to the base you specify. If the base is omitted, it is assumed to be 10. Example: Log(100, 10) equals 2
Log10(number) Returns the logarithm (base 10) of a number. Example: Log10(100) equals 2
Mod(number, divisor) Returns the remainder after the number is divided by the divisor. Example: Mod(5, 2) equals 1
Odd(number) Returns the number rounded up to the nearest odd integer. Example: Odd(2) equals 3
Pi() Returns the number 3.14159265358979, the mathematical constant PI.
Power(number, power) It raises the number to the power. You can also use the "^" operator (e.g. 4^2). Example: Power(4,2) equals 16
Product(number, number, ...) Multiplies all the numbers given as arguments and returns the product. Example: A1 contains 2, Product(A1, 5) equals 10
Radians(number) Converts degrees to radians. Example: Radians(180) equals 3.14159
Rand() Returns a random number greater than or equal to 0 and less than 1. A new number is returned every time the workbook is recalculated.
Round(number) Rounds a number to the specified number of digits. Example: Round(5.46, 1) equals 5.5
RoundDown(number, numDigits) Rounds the number down, toward zero, to the specified number of digits. Example: RoundDown(40.8, 0) equals 40
RoundUp(number, numDigits) Rounds the number up, away from zero, to the specified number of digits. Example: RoundUp(39.1, 0) equals 40
Sign(number) Returns 1 if the number is positive, 0 if the number is 0, and -1 if the number is negative. Example: Sign(-5) equals -1
Sin(number) Returns the sine of the given angle. Example: Sin(Pi()/2) equals 1
Sinh(number) Returns the hyperbolic sine of the number. Example: Sinh(3) equals 10.01787
Sqrt(number) Returns the square root. Example: Sqrt(4) equals 2
Subtotal(functionNumber, ref1, ref2, …) Returns a subtotal. Nested subtotals and filtered hidden rows are ignored. FunctionNumber specifies which function to use in calculation subtotals (1-Average, 2-Count, 3-Counta, 4-Max, 5-Min, 6-Product, 7-Stdev, 8-Stdevp, 9-Sum, 10-Var, 11-Varp). Example: Subtotal(9,A1:A3) will calculate a subtotal of the cells A1:A3 using the Sum function.
Sum(number, number, ...) Return the sum of all the numbers in the list of arguments. Example: Sum(10, 20) equals 30
SumIf(range, criteria, sumRange) It sums up the values of only those cells from the sumRange for which the corresponding cells in the range satisfy the criteria. Example: Sumif(A1:A5,">100000",B1:B5)
Sumproduct(reference1, reference2, ...) Multiplies corresponding numeric components in given ranges and returns the sum of those products.
Sumsq(number, number, ...) Returns the sum of squares of the arguments.
Sumx2my2(reference1, reference2) Calculates the difference between the squares of corresponding numbers in two ranges and then returns the sum of squares of the differences.
Sumx2py2(reference1, reference2) Calculates the sum of the squares of corresponding numbers in two ranges and then returns the sum total of the sums.
Sumxmy2(reference1, reference2) Calculates the differences of corresponding values in two ranges and returns the sum of squares of the differences.
Tan(number) Returns the tangent of the given angle. Example: Tan(5) equals -3.38051
Tanh(number) Returns the hyperbolic tangent of the number. Example: Tanh(1) equals 0.76159
Trunc(number, numDigits) Truncates the number to an integer. The argument numDigits is the precision of the truncation. The default value for the numDigits is 0. Example: Trunc(-7.8) equals -7

Statistical Functions

Avedev(number, number, ...) Returns the average of the absolute deviations of data points from their mean.
Average(number, number, ...) Returns the average of the arguments. Example: Average(1, 2, 3) equals 2
AverageA(number, number, ...) Returns the average of all values in a set of values (TRUE is 1, FALSE and texts are 0).
Betadist(x, alfa, beta, A, B) Returns the cumulative beta probability density function.
Beatainv(probability, alfa, beta, A, B) Returns the inverse of the cumulative beta probability density function.
Binomdist(numOfSuccess, trials, probability, cumulative) Returns the individual term binomial distribution probability.
Confidence(alfa, standardDeviation, size) Returns the confidence interval for a population mean.
Correl(reference1, reference2) Returns the correlation coefficient between two data sets.
Count(number, number, ...) Counts how many numbers are in the list of arguments. Example: B1 contains 5, B2 contains 3, Count(B1:B2) equals 2
CountA(number, number, ...) Counts the number of nonblank values in the list of arguments. Example: A1 contains 2, A2 is blank, CountA(A1:A2) equals 1
CountBlank(range) Counts empty cells in the specified range of cells.
CountIf(range, criteria) Counts the number of cells within the range that meets the given criteria. Example: A1 contains 6, A2 contains3, CountIf(A1:A2,">5") equals 1
Covar(reference1, reference2) Returns covariance.
Critbinom(trials, probability, alfa) Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
Devsq(number, number, ...) Returns the sum of squares of deviations of data points from their sample mean.
Expondist(x, lambda, cumulative) Returns the exponential distribution value.
Fdist(x, degOfFreedom1, degOfFreedom2) Returns the F probability distribution for two data sets.
Finv(probability, degOfFreedom1, degOfFreedom2) Returns the inverse of the F probability distribution.
Fisher(number) Returns the Fisher transformation value.
Fisherinv(number) Returns the inverse of the Fisher transformation.
Forecast(x, seriesY, seriesX) Predicts a future value along a linear trend.
Ftest(reference1, reference2) Returns the result of a F-test.
Gammadist(x, alfa, beta, cumulative) Returns the gamma distribution value.
Gammainv(probability, alfa, beta) Returns the inverse of the gamma cumulative distribution.
Gammaln(number) Returns the natural logarithm of the gamma function.
Geomean(number, number, ...) Returns the geometric mean of a range of positie numeric data.
Harmean(number, number, ...) Returns the harmonic mean of a data set of positive numbers.
Hypgeomdist(numSuccInSample, size, numSuccInPop, popSize) Returns the hypergeometric distribution.
Chidist(x, degOfFreedom) Returns the one-tailed probability of the chi-squared distribution.
Chiinv(x, degOfFreedom) Returns the inverse of the one-tailed probability of the chi-squared distribution.
Chitest(reference1, reference2) Returns the test for independence (the value from the chi-squared distribution for the statistic and the appropriate degrees of freedom).
Intercept(seriesY, seriesX) Calculates the point at which a line will intersect the y-axis by using a best-fit regression line plotted through the known x-values and y-values.
Kurt(number, number, ...) Returns the kurtosis of a data set.
Large(reference, n) Returns the n-th largest value in a data set.
Loginv(probability, mean, standardDeviation) Returns the inverse of the lognormal cumulative distribution function of x.
Lognormdist(x, mean, standardDeviation) Returns the cumulative lognormal distribution of x.
Max(number, number, ...) Returns the maximum value in the list of arguments. Example: Max(1, 2, 3) equals 3
MaxA(number, number, ...) Returns the largest value in a set of values (TRUE is 1, FALSE and texts are 0).
Median(number, number, ...) Returns the median, or the number in the middle of the set of given numbers.
Min(number, number, ...) Returns the minimum value in the list of arguments. Example: Min(1, 2, 3) equals 1
MinA(number, number, ...) Returns the smallest value in a set of values (TRUE is 1, FALSE and texts are 0).
Mode(number, number, ...) Returns the most frequently occurring, or repetitive, value in a range of data.
Negbinomdist(numOfSuccess, numOfFailure, probability) Returns the negative binomial distribution.
Normdist(x, mean, standardDeviation, cumulative) Returns the normal cumulative distribution for the specified mean and standard deviation.
Norminv(probability, mean, standardDeviation) Returns the inverse of the normal cumulative distribution function (Nomdist).
Normsdist(z) Returns the standard normal cumulative distribution (has a mean of zero and standard deviation of one).       
Normsinv(probability) Returns the inverse of the standard normal cumulative distribution function (Normsdist).
Pearson(reference1, reference2) Returns the Pearson product moment correlation coefficient r.
Percentile(reference, n) Returns the n-th percentile.
Percentrank(reference, x, decimal) Returns the rank of a value in a data set as a percentage of the data set.
Permut(number, numberChosen) Returns the number of permutations. Number is the number of objects, numberChosen is the number of objects in each permutation. Example: Permut(10, 2) equals 90
Poisson(x, mean, cumulative) Returns the Poisson distribution.
Prob(values, probabilities, lowLimit, upLimit) Returns the probability that values in a range are between two limits [lowerLimit..upperLimit].
Rank(number, reference, order) Returns the rank of a number in a list of numbers. Its size relative to other values in the list.
Rsq(seriesY, seriesX) Returns the square of the Pearson product moment correlation coefficient through the given data points.
Skew(number, number, ...) Returns the skewness of a distribution.
Slope(seriesY, seriesX) Returns the slope of the linear regression line through the given data points.
Small(reference, n) Returns the n-th smallest value in a data set.
Standardize(x, mean, standardDeviation) Returns a normalized value from a distribution characterized by mean and standard deviation.
Stdev(number, number, ...) Estimates the standard deviation based on a sample.
StdevA(number, number, ...) Estimates standard deviation based on a sample (TRUE is 1, FALSE and texts are 0).
StdevP(number, number, ...) Calculates the standard deviation of the entire population.
StdevpA(number, number, ...) Calculates standard deviation based on an entire population (TRUE is 1, FALSE and texts are 0).
Steyx(seriesY, seriesX) Returns the standard error of the predicted y-value for each x in a regression.
Tdist(x, degOfFreedom, type) Returns the Student’s t-distribution.
Tinv(probability, degOfFreedom) Returns the inverse of the Student’s t-distribution.
Trimmean(reference, percentage) Returns the mean of the interior portion of a set of data values.
Var(number, number, ...) Estimates the variance based on a sample.
VarA(number, number, ...) Estimates variance based on a sample (TRUE is 1, FALSE and texts are 0).
VarP(number, number, ...) Calculates variance of the entire population.
VarpA(number, number, ...) Calculates variance based on an entire population (TRUE is 1, FALSE and texts are 0).
Weibull(x, alfa, beta, cumulative) Returns the Weibull distribution.
Ztest(reference, x, standardDeviation) Returns a value of a z-test.

 

Financial Functions

Ddb(cost, salvage, life, period, factor) Returns the depreciation of an asset for a specified period using the double-declining balance method (you can specify other method). Cost is the initial cost of the asset. Salvage is the value at the end of the depreciation. Life is the number of periods over which the asset is being depreciated. Period is the period for which you want to calculate the depreciation. Period must use the same units as life. Factor is the rate at which the balance declines. If factor is omitted, it is assumed to be 2. All the arguments must be positive numbers. Example: Ddb(30000, 500, 3650, 10) equals 16.357
Fv(rate, nper, pmt, pv, type) Returns the future value of an investment. Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period. Pv is the present value. Type can be 0 or 1 (0 - payments at the end of the period, 1 - at the beginning). If type is omitted, it is assumed to be 0. Example: Fv(0.01, 12, -500) equals 6341.25
IPmt(rate, per, nper, pv, fv, type) Returns the interest payment for an investment. Per is the period for which you want to find the interest (must be 1 <= per <= nper). For more information see Pmt. Example: Ipmt(0.01, 1, 24, 5000) equals -50
Irr(values, guess) Returns the internal rate of return. Values is a reference to cells that contain the numbers for which you want to calculate the internal rate of return. Guess is your guess of the result. If guess is omitted, it is assumed to be 0.1. Example: A1:A6 contain the following values: $-100,000, $10,000, $16,000, $20,000, $22,000 and $24,000 Irr(A1:A6) equals -2.43%.
Mirr(reference, interestRate, interestRateReinvested) Returns the internal rate of return for a series of periodic cash flows.
Nper(rate, pmt, pv, fv, type) Returns the number of periods for an investment. For more information see Pmt. Example: Nper(0.01, -100, -1000, 20000) equals 101
Npv(rate, value1, value2...) Returns the net present value of an investment. Rate is the rate of discount over the length of one period. Value1, value2, are arguments representing the payments (negative values) and income (positive values). Example: Npv(10%, -10000, 4000, 5000, 6000) equals 2069.53
Pmt(rate, nper, pv, fv, type) Returns the periodical payment for an annuity. Rate is the interest rate per period, nper is the number of payment periods in an annuity, pv is the present value, fv is the future value, type see Fv. Example: Pmt(0.01, 10, 10000) equals -1055.82
PPmt(rate, per, nper, pv, fv, type) Returns the payment on the principa for a given period of an investment. For more information see Pmt. Example: Ppmt(0.01, 1, 36, 1000) equals -23.21
Pv(rate, nper, pmt, fv, type) Returns the present value of an investment. For more information see Pmt. Example: Pv(0.01, 5*12, 1000, 0) equals -44955.04
Rate(nper, pmt, pv, fv, type, guess) Returns the interest rate per period of an annuity. For more information see Pmt. Guess is your guess for what the rate will be. Example: Rate(36, -300, 10000, 0) equals 0.42%
Sln(cost, salvage, life) Returns the straight-line depreciation of an asset for one period. Cost is the initial cost of the asset. Salvage is the value at the end of the depreciation. Life is the number of periods. Example: Sln(40000, 5000, 8) equals 4375.
Syd(cost, salvage, life, period) Returns the sum-of-years' digits depreciation of an asset for a specified period. Cost is the initial cost of the asset. Salvage is the value at the end of the depreciation. Life is the number of periods. Period is the period. Example: Syd(40000, 5000, 8, 8) equals 972.22.

 

Date & Time Functions

Date(year, month, day)Returns a particular date. Example: Year(Date(1999, 1, 1)) equals 1999
DateValue(dateText)Converts a date in the form of text to a serial number. Example: DateValue("1/1/99") equals 36161
Day(date) Returns the day of a date. Example: Day(1/1/99) equals 1
Hour(time) Returns the hour of a time. Example: Hour(2:45) equals 2
Minute(time) Returns the minutes of a time. Example: Minute(2:45) equals 45
Month(date) Returns the month of a date. Example: Month(1/1/99) equals 1
Now() Returns the current date and time.
Second(time) Returns the seconds of a time. Example: Second(Time(1, 45, 20)) equals 20
Time(hour, min, sec) Returns a particalar time. Time(1,45,0) equals 1:45
TimeValue(timeText) Converts a time in the form of text to a serial number. TimeValue("1:45") equals 0.072917
Today() Returns the current date.
Weekday(date, type) Returns the day of the week corresponding to a date. If type = 1 or omitted returns: 1 (Sunday) through 7 (Saturday). Example: Weekday(Date(1999,8,15)) equals 1
Year(date) Returns the year of a date. Example: Year(Date(1999, 1, 1)) equals 1999

Logical Functions

And(logical1, logical2, ...) Returns TRUE if all its arguments are TRUE, otherwise returns FALSE. Example: And(FALSE, TRUE) equals FALSE
False() Returns the logical value FALSE.
If(condition, trueValue, falseValue) Returns trueValue if condition evaluates to TRUE or falseValue if condition evaluates to FALSE. Example: If(A1>A2, "OK", "Cancel")
Not(logical) Reverses logical value. Example: Not(TRUE) equals FALSE
Or(logical1, logical2, ...) Returns TRUE if any argument is TRUE, otherwise return FALSE. Example: Or(FALSE, TRUE) equals TRUE
True() Returns the logical value TRUE.
 

Text Functions
Char(number) Returns the character specified by a number. Example: Char(66) equals B
Clean(text) Removes all nonprintable characters from a text string.
Code(text) Returns a numeric code for the first character in a text string. Example: Code("ABC") equals 65
Concatenate(text1, text2, ...) Joins several text strings into one text string.
Exact(text1, text2) Returns TRUE if two text strings are identical (case sensitive). Example: Exact("palm", "PALM") equals FALSE
Find(findText, withinText, start) Finds string (findText) within another text string (withinText), and returns its starting position. Example: Find("A", "CBA", 1) equals 3
Fixed(number, numDecPlaces, unDelimiter1000) Rounds a number to the specified number of decimals and returns the result as text with or without commas.
Left(text, num_chars) Returns the leftmost characters from a text string. Example: Left("Palm PC", 2) equals "Pa"
Len(text) Returns the number of characters in a text string. Example: Len("Palm PC") equals 7
Lower(text) Converts text to lowercase. Example: Lower("Palm PC") equals "palm pc"
Mid(text, start, count) Returns a specific number of characters from a text string, starting at the position start. Example: Mid("Palm PC", 3, 2) equals "lm"
Proper(text) Capitalizes the first letter of all words. Converts all the other letters to lowercase. Example: Proper("this CHAPTER") equals "This Chapter"
Replace(oldText, start, count, newText) Replaces part of a text string oldText, based on the number of characters you specify (start, count), with a newText. Example: Replace("Palm PC", 5, 1, "-Size") equals "Palm_SizePC"
Rept(text, nTimes) Repeats text the given number of times. Example: Rept("-", 5) equals "-----"
Right(text, num_chars) Returns the rightmost characters from a text string. Example: Right("Palm PC", 2) equals "PC"
Substitute(text, oldText, newText, n) Substitutes newText for oldText in a text string. N specifies which occurrence of the oldText you want to replace. Example: Substitute("1/1/1999", "1", "2", 2) equals "1/2/1999"
T(value) Returns the text referred to by value. Example: A1 contains "AAA" then T(A1) equals "AAA"
Trim(text) Removes all spaces from a text string except for single spaces between words.
Upper(text) Converts text to uppercase. Example: Upper("Palm PC") equals PALM PC
Value(text) Converts a text that represents a number to the number. Example: Value("1000") equals 1000

Information & Lookup Functions

Address(row, col, refType, A1, sheetName) Creates a cell reference from specified row and column numbers.
Column(reference) Returns the column number of the given reference.
Columns(range) Returns the number of columns in an array or reference. Example: Columns(A1:D1) equals 4
HLookup(lookupValue, data, row) Searches for a value in the top row of a data table and then returns a value in the same column from a row you specify. LookupValue is the value to be found in the first row of the data table. Data is the table of data. Row is the row number in the data table from which the matching value is returned.
Index(range, rowNum, colNum) Returns a reference to a specified cell within range.
Indirect(text) Returns the reference specified by a text.
IsBlank(value) Returns TRUE if the value is blank.
IsErr(value) Returns TRUE if the value is any error value except !!NA.
IsError(value) Returns TRUE if the value is any error value.
IsLogical(value) Returns TRUE if the value is a logical value.
IsNA(value) Returns TRUE if the value is error value !!NA.
IsNonText(value) Returns TRUE if the value is any value that is not text.
IsNumber(value) Returns TRUE if the value is a number.
IsRef(value) Returns TRUE if the value refers to a reference.
IsText(value) Returns TRUE if the value is text.
Lookup(value, vector1, vector2) Finds the value in a row or column (vector1) and returns the corresponding value from a row or column (vector2).
Match(lookupValue, lookupRange, matchType) Returns the position of an item in a range that matches a specified value in a specified order. LookupValue is the value you want to match in the range.
N(value) Returns a value converted to a number.
NA() Returns the error value !!NA (no value is available).
Offset(reference, rows, cols, height, width) Returns a reference to a range shifted by specified number of rows and columns.
Row(reference) Returns the row number of the given reference.
Rows(range) Returns the number of rows in a reference or array. Example: Rows(A1:A5) equals 5
Type(value) Returns a number indicating the data type of a value (number = 1, text = 2, logical value = 4, error valu = 16).
VLookup(lookupValue, data, column) Searches for a value in the leftmost column of a data table, and then returns a value in the same row from a column you specify. LookupValue is the value to be found in the first column of the data table. Data is the table of data. Column is the column number in the data table from which the matching value is returned.

Database Functions

Daverage(database, field, criteria) Averages the values in the specified database column matching the conditions.
Dcount(database, field, criteria) Counts the cells containing numbers in the specified database column matching the conditions.
Dcounta(database, field, criteria) Counts nonblank cells in the specified database column matching the conditions.
Dget(database, field, criteria) Extracts a single value from the specified database column matching the conditions.
Dmax(database, field, criteria) Returns the largest number in the specified database column matching the conditions.
Dmin(database, field, criteria) Returns the smallest number in the specified database column matching the conditions.
Dproduct(database, field, criteria) Multiplies the values in the specified database column matching the conditions.
Dstdev(database, field, criteria) Estimates the standard deviation based on a sample, using the numbers i in the specified database column matching the conditions.
Dstdevp(database, field, criteria) Calculates the standard deviation of a population based on the entire population, using the numbers in the specified database column matching the conditions.
Dsum(database, field, criteria) Adds the numbers in the specified database column matching the conditions.
Dvar(database, field, criteria) Estimates variance based on a sample, using the numbers in the specified database column matching the conditions.
Dvarp(database, field, criteria) Calculates the variance of a population based on the entire population, using the numbers in the specified database column matching the conditions.

Automating Tasks

What are the Automating tasks?

Using automating tasks you can create simple spreadsheet applications (macros) to automate your tasks.

Your first task:

Tap Task > Workbook Task to open Workbook Task dialog. Tap New... button to open Define Task dialog. Name your first task Hello (write this name instead of "Task Name" to the edit line at the top of this dialog). Tap on button fn() and select function MsgBox from the list. Now write text "Hello World" between the parenthesis:

MsgBox("Hello World")

Tap OK to close Define Task dialog. Select Run on "Menu Item" and tap Close button. 
Now tap Task > Hello to run your first task. Message box "Hello World" will be displayed.

Workbook Automating Tasks

Use Workbook Task menu item (Task > Workbook Task) to define, modify and run your automating tasks.

• Tap Run button to run task.
• Tap New... button to define a new task.
• Tap Edit... button to edit previously defined task.
• Tap Delete button to permanently delete selected task.

Run on - select an event to start task:

• (no event) - select this option to run task only from this dialog using Run button.
• Menu Item - select this option to add a new item to menu Task. Fill the following edit line with the text that labels the menu item.
• Tap on Cell - select this option if you want to execute this task by tapping on a cell or cell range. Fill the following edit line with a cell or cell range reference. 
  Example: Task body: Zoom(80), select Tap on Cell: A1. Now tapping on A1 you can zoom your workbook to 80%.
• New Value in - select this option if you want to execute this task every time value in a cell is changed. Fill the following edit line with a cell or cell range reference.

Global - check this check box if you want to use this task in any workbook. Normal tasks are stored in workbook file. Global tasks are stored in Windows registry.

Cell Automating Tasks

Use Cell Task menu item (Task > Cell Task) to define or modify automating tasks stored in a cell.

• Cell text - text to display in a cell.
• Task Icon - choose an icon you want to display in a cell. You can run the task by tapping on this icon. Choose (no icon) if you want to execute this task by tapping on a cell.
• Task Definition - task source. For example: ShowPicture("myBMW.jpg"). 
• fn() - tap on this button to open Insert function dialog box. 

PTab uses cell tasks to insert pictures, sounds or hyperlinks into cells (see Insert in Cell > Picture... / Sound... / Hyperlink...) or to save charts (see Chart / Options). You can use cell tasks for short "macros" that you can execute by tapping on a cell or cell icon. 

Variables

In tasks you can use variables. 

Example:

ok = YesNoBox("Finish task?")
if ok then
  Return()
end

There are no global variables. Use workbook to store global data.

How to set and get cell values

Use Set(reference, value) function to set cell value.

Example:

Set(A1,1000)

If you need to create a cell reference using row and column numbers (column A has number 1) use function CellRef(col, row [, sheetIndex]). Parameter sheetIndex is optional.

Example:

i = 1
while i < 100
  Set(CellRef(1,i), i)
  i = i+1
end 

If command

if condition1 then
  commands
elsif condition2 then
  commands
else
  commands
end

Examples:

if a1<>0 then Set(c1, b1/a1) end

if a1=0 then
  Set(c1, "Sorry...")
else
  Set(c1, b1/a1)
end

While statement

while condition
  commands
end

Executes commands as long as a condition is True.

Example:

while a1>0
  Set(CellRef(2, a1), a1*100)
  Set(a1, a1-1)
end

For statement

for counterVariable = startValue to endValue [step stepValue]
  commands
end

Repeats commands a specified number of times.

Example:

for i = 1 to 10
  Set(CellRef(1, i), i)
end

for j = 1 to 100 step 10
  Set(a1, a1*j)
end

Calling a procedure 

You can structure your code into more simple tasks and then you can call previously defined task from within a task using its name. Example:

Set(a1,100)
while a1>0
  Hello
  Set(a1, a1-1)
end

Comments

Set(a1,100)  ‘Note: set cell A1 to value 100

Automating Tasks Functions Reference

Parameters in [ ] are optional.

ActiveCell()

Returns the active (highlighted) cell. Example:

Set(ActiveCell(), "Hello World!")

ActiveSheet([ index])

Returns the number of the active sheet (1-based) and selects the new one if index is specified. Returns "!!Val"  if index is less than 1 or greater than number of sheets. Example:

ActiveSheet(2)

Beep([soundType ])

Plays a waveform sound. SoundType specifies the system sound type (64 – asterisk, 48 – exclamation, 32 – question, 16 – hand, no parameter – default sound). Example:

Beep()
Beep(32)

CellRef(col , row [, sheetIndex ])

Returns the cell reference. Example:

Set(CellRef(a1,2), 123)

CellRefR(dcol , drow, [, sheetIndex ])

Returns the cell reference relative to the current cell. Example:

Sel(a1)
Sel(CellRefR(1,0))
MsgBox(“the current cell is now B1”)
Sel(CellRefR(0,5))
MsgBox(“the current cell is now B6”)

CellTask(text, task [, icon])

Creates cell task in the current cell. Text is cell label visible in sheet, task is task source and icon is number of icon (see Cell Task dialog for more info). If icon = 0 (or if this parameter is omitted) cell task is executed by tapping on cell. If icon > 0 then cell task is executed by tapping on icon. Example:

CellTask("Picture","ShowPicture(""pic.jpg"")",3)

Chart(["Type:Bar / Line / LineMarkers / Pie / XY / XYLines / XYLinesMarkers",
            "Title: ChartTitle",
            "XTitle:XaxisTitle",
            "YTitle:YaxisTitle",
            "Series:Rows" or "Series:Columns",
            "Legend" or "Legend:No", 
            "Xaxis" or "Xaxis:No", 
            "Yaxis" or "Yaxis:No",
            "Xgrid" or "Xgrid:No",
            "Ygrid" or "Ygrid:No",
            "AutoScale" or "AutoScale:No"])

Shows the chart of the selection. All parameters are optional and their order is not important. 
Default values of parameters: Chart("Type:Bar", "Series:Rows", "Legend", "Xaxis", "Yaxis", "Xgrid:No", "Ygrid:Yes", "AutoScale").

Example:

Sel(a1:c20)
Chart("Type:XY", "Title:Sin(x)", "Xgrid", "Ygrid")

Sel(b2:f5)
Chart("Series:Columns", "AutoScale:No")

Clear()

Clears contents in the current selection. Example:

Clear()

ClearFormat()

Clears format in the current selection. Example:

ClearFormat()

ClearValidation()

Clears validation settings in the current selection. Example:

ClearValidation()

ColHide([hideUnhide])

Returns whether the first column of current selection is hidden and hides (true) or unhides (false) selected columns if hideUnhide is specified. Example:

Sel(B1:B1)
ColHide(true)

ColWidth([width])

Returns column width of  the first column of current selection and sets width of selected columns if width is specified. Example:

Sel(B1:C1)
ColWidth(100)

ComboBox(“list”)

Creates combo box in the current cell. See also Tools / Data Validation menu item for more info. Use function ClearValidation() to clear combo boxes in the current selection. Use Run on “New value in” workbook task event to run a task every time value in a cell is changed. Example:

Sel(A1)
ComboBox(“BMW,Jaguar,Volvo”)

Copy()

Copies the current selection to the clipboard. Example:

Sel(A1:A5)
Copy()
Sel(B1)
Paste()

DeleteCells(colsRows)

Deletes cells (true - columns, false - rows). Example:

DeleteCells(true)

DeleteSheet(worksheet)

Removes worksheet. Example:

DeleteSheet(“Sheet2”)

FileBox(title [, filter])

Opens a file dialog box with the title and the file type filter. Example:

picture = FileBox("Pictures", "*.jpg,*.gif,*.bmp,*.2bp,*.xbm")
ShowPicture(picture)

Filter(onOff)

Sets auto filter (true = autofilter on, false = autofilter off). Auto filter range is the current selection. Example:

Filter(true)

FindDialog()

Shows Find dialog box. Example:

FindDialog()

FormatPainter(cellRef)

Formats the current selection using the format from the specified cell (cellRef). Example:

FormatPainter(f2)

FreezeTitles( onOff)

Freeze/unfreeze titles. Example:

Sel(a2)
FreezeTitles(true)

FullScreen(onOff)

Returns the current screen mode and sets it if onOff is specified (true = full screen, false = normal). Example:

FullScreen(true)

GoalSeek(setCell, toValue, chngCell)

Runs a goal seek. When goal seeking PTab varies the value in chngCell until a formula in setCell returns the result toValue. See also Tools > Goal Seek menu item. Example:

GoalSeek(A1, 1000.0, C1)

IBrowser([webpage])

Runs Internet browser. Example:

IBrowser("www.z4soft.com")

InputBox(title)

Shows input dialog box with the title. Example:

age = InputBox("Enter your age")

InsertCells(colsRows)

Inserts cells (true - columns, false - rows). Example:

InsertCells(false)

InsertSheet( worksheet)

Inserts new worksheet. Example:

InsertSheet(“Cars”)

LastCol([rowNumber] )

Returns the last nonblank cell in the whole sheet (if rowNumber is not specified) or  in the specified row. Example:

last = LastCol()

last = LastCol(15) 

LastRow([columnNumber])

Returns the last nonblank cell in the whole sheet (if columnNumber is not specified) or in the specified column. Example:

last = LastRow()

last = LastRow(15)

ListBox(title, range [, retValue] )

Shows list box dialog with items from range and with the title and returns the index of the selected item or its value (if retValue is TRUE). Example:

med = ListBox("Choose medicine", Medicines!a1:a50, TRUE)

ListBox2(title, item1, item2, item3 ... item29)

Shows list box dialog with items: item1, item2, item3... and with the title and returns text of the selected item. Example:

car = ListBox("Choose car", "BMW", "Jaguar", "Ferrari")

Modified([onOff])

Returns true if the Modified flag is true (workbook was modified) and sets the Modified flag to onOff (true or false). Example:

Modified(false)

MsgBox(text)

Shows the Message Box with the text. Example:

MsgBox("Hello")

Paste([contents, format, validation, skipBlanks, onlyResults])

Pastes the clipboard to the current selection. Example:

Sel(A1:A5)
Copy()
Sel(B1)
Paste()

All arguments are optional. Paste() function without arguments works as Edit / Paste menu command. Paste() function with arguments works as Edit / Paste Special menu command. Default values for arguments are Paste(true, true, true, false, false). Example (paste contents, preserve previous format):

Paste(true, false)

PlaySound([filename])

Plays a sound (file of type *.wav). If you use this function without parameter, any currently playing sound is stopped. Example:

PlaySound("Song1.wav")

RangeRef(col1 , row1 , col2 , row2 [, sheetIndex ])

Returns the range reference. Example:

MsgBox(Sum(RangeRef(a1,2,20,2)))

Return()

Stops task execution. Example:

if a1=0
  MsgBox("A1 must not be 0")  
  Return()
end

RowHeight([height])

Returns row height of  the first row of current selection and sets height of selected rows if height is specified. Example:

Sel(A2:A7)
RowHeight(10)

RowHide([hideUnhide])

Returns whether the first row of current selection  is hidden and hides (true) or unhides (false) selected rows if hideUnhide is specified. Example:

Sel(A2:A5)
RowHide(true)

RunApp(application [, parameters])

Runs an application (program) with an optional parameters. Example:

RunApp("iexplore.exe", "http://z4soft.com")

Save(fileName )

Saves the current workbook. Example:

Save(“data.xls”)

Sel([range ])

Returns the current selection and select the new one if range is specified. Example:

Sel(a2:c12)

Set(reference , value )

Sets cell specified by reference to the value. Example:

Set(a1, 12.34)

SheetName(sheetNum)

Returns name of the specified sheet. Example:

name = SheetName(1)

SheetsCount()

Returns a  number of sheets in workbook. Example:

MsgBox("Sheets count is "&SheetsCount())

ShowPicture(pictureFile [, time])

Shows a picture (file of types: *.jpg, *.gif, *.bmp, *.2bp, *.xbm). Use the second (optional) parameter to close a picture window after time seconds. Without specifying the time parameter a picture can be closed by tapping on it. Example:

ShowPicture("car.bmp")

Sort(firstBy[, ascending, secondBy, ascending, thirdBy, ascending, headerRow, caseSensitive, sortRows] )

Sorts the current selection. FirstBy, secondBy, thirdBy - column numbers (or row numbers if sortRows is true). sorts ascending (ascending = true) or descending (ascending = false). For more info see Sort dialog in Tools menu. Example:

Sel(a1:c20)
Sort(1)

TickCount()

Returns the number of seconds that have elapsed since the system was started. This function has microsecond precision. Example:

tm = TickCount()

TopLeftCell(reference)

Returns the top left cell on the screen and sets it if reference is specified. Example:

TopLeftCell(A1)

WorkbookName()

Returns name of the opened workbook. Example:

Save(WorkbookName())

YesNoBox(text)

Shows the box giving the choice yes or no and returns true if user selects Yes. Example:

if YesNoBox("Stop") then
  Return()
end

Zoom([magnification ])

Returns current value of zoom magnification and sets it to magnification if specified. Example:

Zoom(65)

Zoom(Zoom()-10)

Automating Tasks Examples

Selecting cells from a2 to the last cell
Sel(RangeRef(1, 2, LastCol(), LastRow())

Selecting column by tapping the cell in the first row (onTap: $A$1:$A$255)
curCol = Column(ActiveCell())
Sel(RangeRef(curCol, 1, curCol, LastRow())

Entering a product name to the tapped cell in column B from product table in Sheet2 (onTap:$B$1:$B$16384)
choice = ListBox("Select a product", Sheet2!a1:a16384)
Set(ActiveCell(), CellRef(1, choice, 2))

Get current selection coordinates:
c  = Column(Sel())
r = Row(Sel())
numCols = Cols(Sel())
numRows = Rows(Sel())

Keyboard shortcuts