Function reference
Every example on these pages is executed against the same Rust engine that ships in the app — not transcribed from another vendor's documentation. Where VisiGrid differs from Excel, the page says so.
Math
- ABSReturn a number without its sign, so -150 and 150 both become 150.
- CEILINGRound a number up to the nearest multiple of a given significance — the next 0.05, the next 5, the next full crate.
- EXPReturn e (about 2.71828) raised to a given power. The inverse of LN.
- FLOORRound a number down to the nearest multiple of a given significance — the 0.05 below, the 5 below, the last full crate.
- INTRound a number down to the nearest integer. Negative numbers go further from zero, not towards it.
- LNReturn the natural logarithm (base e) of a positive number. The inverse of EXP.
- LOGReturn the logarithm of a number to a given base, or to base 10 when no base is given.
- LOG10Return the base-10 logarithm of a positive number — its order of magnitude.
- MODReturn the remainder after dividing one number by another. The result has the sign of the divisor, as in Excel.
- PIReturn π (3.14159265358979) to full floating-point precision.
- POWERRaise a number to an exponent. Works for whole, negative and fractional exponents, and is the same as the ^ operator.
- PRODUCTMultiply every number in a range or list.
- RANDReturn a random number from 0 up to (but not including) 1. A new value every time the sheet recalculates.
- RANDBETWEENReturn a random integer between two bounds, inclusive. A new value every time the sheet recalculates.
- ROUNDRound a number to a given number of decimal places, or to tens, hundreds and thousands with a negative count. Halves round away from zero.
- ROUNDDOWNRound a number down, towards zero, to a given number of digits — whatever the next digit is.
- ROUNDUPRound a number up, away from zero, to a given number of digits — whatever the next digit is.
- SQRTReturn the positive square root of a number. Negative numbers return #NUM!.
- SUMAdd up numbers, cell ranges, or a mix of both.
- SUMIFAdd up the cells that meet a single condition.
- SUMIFSAdd up the numbers in a range that meet one or more conditions.
- SUMPRODUCTMultiply corresponding values in two or more ranges and sum the results — and, with a condition inside, count or total the rows that meet it.
- TRUNCRemove the fractional part of a number, or keep a set number of decimals, without rounding.
Text
- CONCATCombine several pieces of text into one.
- CONCATENATECombine several pieces of text into one. Superseded by CONCAT, TEXTJOIN and the ampersand.
- EXACTReturn TRUE only when two pieces of text match exactly, capitalisation and all.
- FINDReturn the position where one piece of text appears inside another.
- LEFTReturn the first n characters of a piece of text.
- LENReturn how many characters a piece of text contains.
- LOWERReturn text with every letter in lower case.
- MIDReturn a run of characters starting at a given position.
- PROPERConvert text to title case.
- REGEXEXTRACTExtract the part of a string that matches a regular expression — the first match, every match, or the capture groups.
- REGEXMATCHReturn TRUE if text matches a regular expression. The same function as REGEXTEST, under the name Google Sheets uses.
- REGEXREPLACEReplace the parts of a string that match a regular expression — every match, or just one — with capture groups available as $1, $2.
- REGEXTESTReturn TRUE if text matches a regular expression — a valid email, an order number, a postcode — and FALSE if not.
- REPLACEReplace a run of characters at a known position with different text.
- REPTBuild a string by repeating another one.
- RIGHTReturn the last n characters of a piece of text.
- SEARCHReturn the position of one piece of text inside another, without caring about capitalisation.
- SUBSTITUTESwap every occurrence of one piece of text for another, or just one of them.
- TEXTTurn a number or date into text using an Excel format code — "yyyy-mm-dd", "#,##0.00", "0%", "000" — for labels, file names and joined strings.
- TEXTAFTERSplit a string and keep the part after a separator.
- TEXTBEFORESplit a string and keep the part before a separator, without counting characters.
- TEXTJOINCombine values with a delimiter between them, optionally skipping empty ones.
- TRIMStrip leading and trailing spaces, and collapse runs of spaces inside text, so values compare as equal.
- UPPERReturn text with every letter capitalised.
- VALUETurn a number that is stored as text into an actual number, so arithmetic and lookups work on it.
Lookup and reference
- CHOOSEReturn one of up to 254 values, picked by an index number — the second value for 2, the fifth for 5.
- COLUMNReturn the column number of a reference (A is 1, B is 2), or of the cell the formula is in.
- COLUMNSReturn the number of columns in a range or array.
- HLOOKUPSearch the first row of a range and return a value from further down the same column.
- INDEXReturn the value at a given row (and optionally column) position within a range.
- INDIRECTTurn text such as "B3" or "B2:B4" into a live reference, so the address a formula reads can be calculated.
- MATCHReturn the position of a value within a range, rather than the value itself.
- OFFSETReturn a reference a given number of rows and columns away from a starting cell, optionally resized — the basis of ranges that grow with the data.
- ROWReturn the row number of a reference, or of the cell the formula is in.
- ROWSReturn the number of rows in a range or array.
- VLOOKUPFind a value in the first column of a range and return something from the same row.
- XLOOKUPLook up a value in one range and return the matching value from another.
- XMATCHReturn where a value sits in a range, with exact matching by default and the option to search backwards.
Logical
- ANDCombine several conditions so all of them must hold.
- IFChoose between two results depending on whether a condition is true.
- IFERRORReturn your own value when a formula produces any error, instead of showing #N/A or #REF!.
- IFNACatch #N/A and nothing else, so a genuine problem still surfaces instead of being hidden.
- IFSReturn the result for the first condition that is true, without nesting.
- ISERRORReturn TRUE when a formula produced any error.
- ISNAReturn TRUE for #N/A specifically, and FALSE for every other error.
- ISNUMBERReturn TRUE when a value is numeric rather than text.
- ISTEXTReturn TRUE when a value is text rather than a number.
- NOTTurn TRUE into FALSE and FALSE into TRUE.
- ORCombine several conditions so that any one of them suffices.
- SWITCHCompare one expression to several values and return the matching result.
Statistical
- AVERAGEAdd up the numbers and divide by how many there were.
- AVERAGEIFAverage only the values whose row meets a condition.
- AVERAGEIFSAverage the values whose row satisfies every one of a set of criteria.
- AVGReturn the mean of a range. A VisiGrid alias, not an Excel function.
- COUNTCount how many cells hold numbers, ignoring text and blanks.
- COUNTACount cells that hold anything at all, including text.
- COUNTBLANKCount how many cells in a range hold nothing.
- COUNTIFCount how many cells in a range satisfy one condition.
- COUNTIFSCount rows that satisfy every one of a set of criteria.
- MAXReturn the biggest numeric value, ignoring text and blanks.
- MEDIANReturn the value in the middle of a sorted set of numbers.
- MINReturn the smallest numeric value, ignoring text and blanks.
- NORM.S.DISTReturn the probability that a standard normal value falls at or below z, or the height of the bell curve at z.
- NORMSDISTThe pre-2010 name for the cumulative form of NORM.S.DIST — the probability that a standard normal value falls at or below z.
- STDEVThe pre-2010 name for STDEV.S. Same calculation, same result; kept so older workbooks open unchanged.
- STDEV.PCalculate the standard deviation when the data covers every member of the group, not a sample of it.
- STDEV.SEstimate the standard deviation of a population from a sample of it. The usual choice for real data.
- STDEVPThe pre-2010 name for STDEV.P. Same calculation, same result; kept so older workbooks open unchanged.
- VARThe pre-2010 name for VAR.S. Same calculation, same result; kept so older workbooks open unchanged.
- VAR.PCalculate the variance when the data covers the whole population — the average squared distance from the mean.
- VAR.SEstimate the variance of a population from a sample — the square of the sample standard deviation.
- VARPThe pre-2010 name for VAR.P. Same calculation, same result; kept so older workbooks open unchanged.
Financial
- CUMIPMTAdd up the interest portion of a loan's payments between two periods, such as one year of mortgage interest.
- CUMPRINCAdd up the principal portion of a loan's payments between two periods — how much the balance fell.
- FVCalculate what regular payments and a starting balance grow to at a fixed interest rate.
- IPMTCalculate how much of a given loan payment is interest, for any period of the loan.
- IRRFind the discount rate at which a series of cash flows has a net present value of zero — the return an investment earns.
- NPERWork out the number of periods needed to pay off a loan, or to reach a savings target, at a fixed rate and payment.
- NPVDiscount a series of future cash flows to today's value at a given rate. The initial investment belongs outside the function.
- PMTCalculate the fixed periodic payment for a loan or annuity from the rate, the number of periods and the amount borrowed.
- PPMTCalculate how much of a given loan payment reduces the balance, for any period of the loan.
- PVCalculate what a series of equal future payments is worth today — how much you can borrow for a given payment, or what an annuity costs.
- RATEWork out the interest rate per period from the number of payments, the payment and the amount — the rate a loan is really charging.
- XIRRFind the annual return of cash flows that arrive on irregular dates — the rate at which their XNPV is zero.
- XNPVDiscount cash flows that arrive on irregular dates to today's value, each by its own number of days — NPV for real-world timing.
Date and time
- DATETurn three numbers into a date the spreadsheet can do arithmetic with.
- DATEDIFCount complete days, months or years from one date to another.
- DATEVALUETurn a date written as text into a date serial number.
- DAYReturn the day number, 1 to 31, from a date.
- DAYSCount the calendar days from one date to another.
- EDATEMove a date forward or backward a number of months, clamping to the month's end.
- EOMONTHReturn the month end, a number of months before or after a date.
- HOURReturn the hour, 0 to 23, from a time value.
- MINUTEReturn the minutes, 0 to 59, from a time value.
- MONTHReturn the month, 1 to 12, from a date.
- NETWORKDAYSCount the days between two dates, skipping weekends.
- NOWReturn the current moment as a date with a time fraction attached.
- SECONDReturn the seconds, 0 to 59, from a time value.
- TIMETurn three numbers into a time value you can compare and do arithmetic on.
- TODAYReturn today's date, with no time attached, updating whenever the sheet recalculates.
- WEEKDAYReturn which day of the week a date falls on, as a number.
- WORKDAYAdd or subtract working days from a date, skipping weekends.
- YEARReturn the four-digit year from a date.
Trigonometry
- ACOSReturn the angle, in radians, whose cosine is the given number. Input must be between -1 and 1.
- ASINReturn the angle, in radians, whose sine is the given number. Input must be between -1 and 1.
- ATANReturn the angle, in radians, whose tangent is the given number.
- ATAN2Return the angle, in radians, from the origin to a point, taking the x and y coordinates separately so the quadrant is correct.
- COSReturn the cosine of an angle given in radians.
- DEGREESConvert an angle from radians, which the trigonometric functions use, into degrees.
- RADIANSConvert an angle in degrees into radians, the unit SIN, COS and TAN expect.
- SINReturn the sine of an angle. The angle is in radians; convert degrees with RADIANS first.
- TANReturn the tangent of an angle given in radians — rise over run.
Array
- FILTERReturn the rows of a range where a condition is TRUE — sales over a threshold, one region's orders — as a live result that spills and updates.
- SEQUENCEFill a range with a sequence of numbers — 1 to 10, every 5 from 100, a grid of row by column — from a single formula that spills.
- SORTReturn a range sorted by one of its columns, ascending or descending. The result spills and updates when the source changes.
- SPARKLINEDraw a range of numbers as a row of block characters in a single cell — a trend you can read at a glance beside the data.
- TRANSPOSETurn a row into a column, or a table on its side, with a formula that stays linked to the source.
- UNIQUEReturn each distinct value or row from a range once, in the order first seen. Spills into as many cells as there are distinct values.