Functions

There are just over 100 @Functions in 1-2-3, the full list is available in the online help. Simply press F1 and navigate to @Function Index.

There are more detailed examples in Section 3 of the full printed manual, available online here.

Modern Functions

Modern spreadsheets have many convenient functions missing in 1-2-3, but often these are just wrappers over formulas that work just as well in 1-2-3.

This page contains notes on emulating some of the functions you may be missing.

Any corrections, improvements or suggestions are welcome! Please create an issue if you have any suggestions for this page.

@EOMONTH

The last day of the month n months before or after a specified date.

This can be calculated by subtracting 1 from the first day of the month after the target.

	A	B
1	Start Date:	01-Jan-2011
2	Month Offset:	8
3	Result:	30-Sep-2011

The formula in B3 is the following, use /Range Format Date to display it correctly:

@DATE(@YEAR(B1)+(@MONTH(B1)+B2)/12,@MOD(@MONTH(B1)+B2,12)+1,1)-1

If you also need to handle negative month offsets, this slightly more complicated formula is necessary:

@DATE(@YEAR(B1)+(@MONTH(B1)+B2)/12,@MOD(@YEAR(B1)*12+@MONTH(B1)+B2,12)+1,1)-1

Thanks to @rruhle for the addition, this was originally his formula, but generalized to handle any month offset.

@GEOMEAN

The geometric mean is the nth root of the product of n numbers.

Therefore, the geometric mean is simply @PRODUCT(A1..IV1)^(1/@COUNT(A1..IV1)).

Note: @PRODUCT() was implemented in ed6f51.

@OFFSET

There is no @OFFSET function in 1-2-3, but you can emulate it with @COORD and the indirection function @@ instead.

For example, to @SUM the last 12 occupied cells in A1..IV1, you could use this:

@SUM(@@(@COORD(1, @COUNT(A1..IV1) - 11, 1, 1) & "..IV1"))

@PRODUCT

There is no @PRODUCT function in 1-2-3, although we may add one in future.

@PRODUCT() was implemented in ed6f51. The following information is kept here for users of older versions.

If you arrange your worksheet like this, the formula in B2 can be copied and extended to calculate a running product.

	A	B	C	D	E
1	4	5	6	7	8
2	+A1	+B1*A2	+C1*B2	+D1*C2	+E1*D2

The product of A1..E1 is now available in E2.

@RANDBETWEEN

You can use the formula:

@INT(@RAND*(high-low+1)) + low

@TBILLEQ

You can use the formula:

(365*rate)/(360-(rate*@D360(settlement,maturity)))

	A	B
1	Discount:	9.14%
2	Maturity:	@DATE(2008,6,1)
3	Settlement:	@DATE(2008,3,31)
4	Result:	9.42%

For example, (365*9.14%)/(360-(9.14%*D360(@DATE(2008,3,31),@DATE(2008,6,1)))) returns 9.42%.

@WEEKDAY

Note: @WEEKDAY() was implemented in c5d0e55

You can use the formula @MOD(date, 7), which will give a number indicating the day (0 = Sat, 1 = Sun, 2 = Mon, and so on).

If you need the day as a string, you can use @CHOOSE(@MOD(@TODAY, 7), "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri").

Note: This will only return the correct weekday for dates after March 1st 1900, the reason is described here.