18
Sep
11

QuickOffice Pro 7: QuickSheet – Supported Excel Functions

QO_PRO_512x512-96x96 Having spent the last few days testing QuickOffice Pro 7 for Symbian I noted that there is not much information on the supported Microsoft Excel functions within the QuickSheet sub-app of QuickOffice.

Function Entry

Functions can be entered within QuickSheet, but it is a somewhat long winded process as you have to:

  • tap on “Tap here to edit” or [Options] and [Edit Cell] which opens the half screen keyboard, and exit again by tapping on the green tick (bottom left corner).
  • tapping on [Options] brings up new menu options, including [Operators] and [Insert function].
  • Tap on [Insert function] and you will be presented with function categories as seen in Microsoft Excel function wizard.

Once you have selected a function, all function text must be entered in uppercase as lowercase letter usage presents you with a “Cannot accept the input” error message.

This Review forms part of a multi-part review:

Also when you change case during function entry the cursor is automatically moved after the closing bracket of the formula, requiring manual repositioning of the cursor to the correct text entry position.

The good news is that the first few operations can be ignored if you already know the function needed as functions can be entered manually.

Sadly there is no excel style guidance as to how, and in which order, functions should be constructed. To help I have listed all functions supported in QuickSheet and the construction of the function in the following section.

Available Functions

Below are a number of tables, aligned to the categories of functions available within Quicksheet, with hyperlinks to Microsoft detailed information on the functions..

Common

AVERAGE

Returns the average of its arguments

AVERAGE(number1,number2,…)

IF

Specifies a logical test to perform

IF(logical_test,value_if_true,
value_if_false)

IRR

Returns the internal rate of return for a series of cash flows

IRR(values,guess)

NOW

Returns the serial number of the current date and time

NOW()

NPV

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate

NPV(rate,value1,value2,..)

PMT

Returns the periodic payment for an annuity

PMT(rate,nper,pv,fv,type)

SUM

Adds its arguments

SUM(number1,number2,…)

STDEV

Estimates standard deviation based on a sample

STDEV(number1,number2,…)

Math and trigonometry functions

ABS

Returns the absolute value of a number

ABS(number)

ACOS

Returns the arccosine of a number

ACOS(number)

ACOSH

Returns the inverse hyperbolic cosine of a number

ACOSH(number)

ASIN

Returns the arcsine of a number

ASIN(number)

ASINH

Returns the inverse hyperbolic sine of a number

ASINH(number)

ATAN

Returns the arctangent of a number

ATAN(number)

ATAN2

Returns the arctangent from x- and y-coordinates

ATAN2(x_num,y_num)

ATANH

Returns the inverse hyperbolic tangent of a number

ATANH(number)

COS

Returns the cosine of a number

COS(number)

COSH

Returns the hyperbolic cosine of a number

COSH(number)

DEGREES

Converts radians to degrees

DEGREES(angle)

EXP

Returns e raised to the power of a given number

EXP(number)

FACT

Returns the factorial of a number

FACT(number)

INT

Rounds a number down to the nearest integer

INT(number)

LN

Returns the natural logarithm of a number

LN(number)

LOG

Returns the logarithm of a number to a specified base

LOG(number,base)

LOG10

Returns the base-10 logarithm of a number

LOG10(number)

MOD

Returns the remainder from division

MOD(number,divisor)

PI

Returns the value of pi

PI()

POWER

Returns the result of a number raised to a power

POWER(number,power)

PRODUCT

Multiplies its arguments

PRODUCT(number1,number2,…)

RADIANS

Converts degrees to radians

RADIANS(angle)

ROUND

Rounds a number to a specified number of digits

ROUND(number,num_digits)

ROUNDDOWN

Rounds a number down, toward zero

ROUNDDOWN(number,num_digits)

ROUNDUP

Rounds a number up, away from zero

ROUNDUP(number,num_digits)

SIN

Returns the sine of the given angle

SIN(number)

SINH

Returns the hyperbolic sine of a number

SINH(number)

SQRT

Returns a positive square root

SQRT(number)

SUM

Adds its arguments

SUM(number1,number2,…)

TAN

Returns the tangent of a number

TAN(number)

TANH

Returns the hyperbolic tangent of a number

TANH(number)

TRUNC

Truncates a number to an integer

TRUNC(number,num_digits)

Statistical functions

AVEDEV

Returns the average of the absolute deviations of data points from their mean

AVEDEV(number1,number2,…)

AVERAGE

Returns the average of its arguments

AVERAGE(number1,number2,…)

COUNT

Counts how many numbers are in the list of arguments

COUNT(value1,value2,…)

COUNTA

Counts how many values are in the list of arguments

COUNTA(value1,value2,…)

COUNTBLANK

Counts the number of blank cells within a range

COUNTBLANK(range)

KURT

Returns the kurtosis of a data set

KURT(number1,number2,…)

LARGE

Returns the k-th largest value in a data set

LARGE(array,k)

MAX

Returns the maximum value in a list of arguments

MAX(number1,number2,…)

MIN

Returns the minimum value in a list of arguments

MIN(number1,number2,…)

RAND

Returns a random number between 0 and 1

RAND()

SKEW

Returns the skewness of a distribution

SKEW(number1,number2,…)

SMALL

Returns the k-th smallest value in a data set

SMALL(array,k)

STDEV

Estimates standard deviation based on a sample

STDEV(number1,number2,…)

STDEVA

Estimates standard deviation based on a sample, including numbers, text, and logical values

STDEVA(value1,value2,…)

STDEVP

Calculates standard deviation based on the entire population

STDEVP(number1,number2,…)

STDEVPA

Calculates standard deviation based on the entire population, including numbers, text, and logical values

STDEVPA(value1,value2,…)

VAR

Estimates variance based on a sample

VAR(number1,number2,…)

VARA

Estimates variance based on a sample, including numbers, text, and logical values

VARA(value1,value2,…)

VARP

Calculates variance based on the entire population

VARP(number1,number2,…)

VARPA

Calculates variance based on the entire population, including numbers, text, and logical values

VARPA(value1,value2,…)

Logical functions

AND

Returns TRUE if all of its arguments are TRUE

AND(logical1,logical2,..)

IF

Specifies a logical test to perform

IF(logical_test,value_if_true, value_if_false)

NOT

Reverses the logic of its argument

NOT(logical)

OR

Returns TRUE if any argument is TRUE

OR(logical1,logical2,..)

FALSE

Returns the logical value FALSE

FALSE()

TRUE

Returns the logical value TRUE

TRUE()

Text functions

CHAR

Returns the character specified by the code number

CHAR(number)

CODE

Returns a numeric code for the first character in a text string

CODE(text)

CONCATENATE

Joins several text items into one text item

CONCATENATE(text1,text2,…)

EXACT

Checks to see if two text values are identical

EXACT(text1,text2,…)

FIND

Finds one text value within another (case-sensitive)

FIND(find_text,within_text, start_num)

LEFT

Returns the leftmost characters from a text value

LEFT(text,num_chars)

LEN

Returns the number of characters in a text string

LEN(text)

LOWER

Converts text to lowercase

LOWER(text)

MID

Returns a specific number of characters from a text string starting at the position you specify

MID(text,start_num,num_chars)

PROPER

Capitalizes the first letter in each word of a text value

PROPER(text)

REPLACE

Replaces characters within text

REPLACE(old_text,start_num, num_chars,new_text)

REPT

Repeats text a given number of times

REPT(text,number_times)

RIGHT

Returns the rightmost characters from a text value

RIGHT(text,num_chars)

T

Converts its arguments to text

T(value)

TRIM

Removes spaces from text

TRIM(text)

UPPER

Converts text to uppercase

UPPER(text)

VALUE

Converts a text argument to a number

VALUE(text)

Date and time functions

DATE

Returns the serial number of a particular date

DATE(year,month,day)

DATEVALUE

Converts a date in the form of text to a serial number

DATEVALUE(date_text)

DAY

Converts a serial number to a day of the month

DAY(serial_number)

DAYS360

Calculates the number of days between two dates based on a 360-day year

DAYS360(start_date,end_date, method)

HOUR

Converts a serial number to an hour

HOUR(serial_number)

MINUTE

Converts a serial number to a minute

MINUTE(serial_number)

MONTH

Converts a serial number to a month

MONTH(serial_number)

NOW

Returns the serial number of the current date and time

NOW()

SECOND

Converts a serial number to a second

SECOND(serial_number)

TIME

Returns the serial number of a particular time

TIME(hour, minute,second)

TIMEVALUE

Converts a time in the form of text to a serial number

TIMEVALUE(time_text)

TODAY

Returns the serial number of today’s date

TODAY()

WEEKDAY

Converts a serial number to a day of the week

WEEKDAY(serial_number, return_type)

YEAR

Converts a serial number to a year

YEAR(serial_number)

Financial functions

DDB

Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify

DDB(cost,salvage,life,period,factor)

FV

Returns the future value of an investment

FV(rate,nper,pmt,pv,type)

IPMT

Returns the interest payment for an investment for a given period

IPMT(rate,per,nper,pv,fv,type)

IRR

Returns the internal rate of return for a series of cash flows

IRR(values,guess)

NPER

Returns the number of periods for an investment

NPER(rate,pmt,pv,fv,type)

NPV

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate

NPV(rate,value1,value2,..)

PMT

Returns the periodic payment for an annuity

PMT(rate,nper,pv,fv,type)

PPMT

Returns the payment on the principal for an investment for a given period

PPMT(rate,per,nper,pv,fv,type)

PV

Returns the present value of an investment

PV(rate,nper,pmt,fv,type)

RATE

Returns the interest rate per period of an annuity

RATE(mper,pmt,pv,fv,type,guess)

SLN

Returns the straight-line depreciation of an asset for one period

SLN(cost,salvage,life)

SYD

Returns the sum-of-years’ digits depreciation of an asset for a specified period

SYD(cost,salvage,life,per)

Information functions

ERROR.TYPE

Returns a number corresponding to an error type

ERROR.TYPE(error_val)

ISBLANK

Returns TRUE if the value is blank

ISBLANK(value)

ISERR

Returns TRUE if the value is any error value except #N/A

ISERR(value)

ISERROR

Returns TRUE if the value is any error value

ISERROR(value)

ISLOGICAL

Returns TRUE if the value is a logical value

ISLOGICAL(value)

ISNA

Returns TRUE if the value is the #N/A error value

ISNA(value)

ISNONTEXT

Returns TRUE if the value is not text

ISNONTEXT(value)

ISNUMBER

Returns TRUE if the value is a number

ISNUMBER(value)

ISREF

Returns TRUE if the value is a reference

ISREF(value)

ISTEXT

Returns TRUE if the value is text

ISTEXT(value)

N

Returns a value converted to a number

N(value)

TYPE

Returns a number indicating the data type of a value

TYPE(value)

Lookup and reference functions

ADDRESS

Returns a reference as text to a single cell in a worksheet

ADDRESS(row_num,column_num, abs_num,a1,sheet_text))

CHOOSE

Chooses a value from a list of values

CHOOSE(index_num,value1, value2,…)

COLUMNS

Returns the number of columns in a reference

COLUMNS(array)

HLOOKUP

Looks in the top row of an array and returns the value of the indicated cell

HLOOKUP(lookup_value, table_array,row_index_num, range_lookup)

INDEX

Uses an index to choose a value from a reference or array

INDEX(…)

ROWS

Returns the number of rows in a reference

ROWS(array)

VLOOKUP

Looks in the first column of an array and moves across the row to return the value of a cell

VLOOKUP(lookup_value, table_array,column_index_num, range_lookup)

Advertisements

9 Responses to “QuickOffice Pro 7: QuickSheet – Supported Excel Functions”


  1. 1 CLTSchwarz
    September 19, 2011 at 12:19 am

    Nice work. I’ve been thinking of upgrading to the Pro to get the direct cloud storage (I use DropML heaps now), is it much better than standard? Is Adobe faster at scrolling docs?

    Thanks

  2. 4 Alberto
    January 6, 2012 at 3:38 pm

    I got it for my kindle fire… what I wanted was to be able to do Data validation lists… I have spreadsheets that use lists created with the data validation function. How can I enable this in Quick office pro… It is the only reason I bought. Otherwise, please recommend a solution. Last time I checked it could not be done in google spreadsheets either

  3. February 11, 2014 at 7:10 pm

    I do not even know how I ended up here, but I
    thought this post was good. I don’t know who you are but definitely you’re
    going to a famous blogger if you aren’t already 😉 Cheers!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


mbrett

Twitter Updates


%d bloggers like this: