The Transform() function takes a range of information and performs calculations on that information. In its simplest form, Transform() can be used to reorder columns or output particular columns of the input range.

Syntax

---

Transform (Range, Formulas, Calculate Error Value)

Range	The range of cells in Excel used as input
Formulas	The calculations to apply to the input. At least one formula is required and up to 19 formulas can be specified. In the formulas, columns are represented as C1, C2, C3 etc. Formulas are provided in a comma delimited list. Each formula outputs its results to a column.
Calculate Error Value	The value to display when a formula fails

Examples

---

Example 1. Simple calculations

Example 2. Perform simple calculations, using an IF statement

Example 3. Use Transform to reorder columns

Example 4. Using Transform with a K Series

Example 5. Perform simple evaluations that return TRUE or FALSE

Example 6. Return close prices that are less than the ten-day rolling average (using Row Column syntax)

Example 1. Simple calculations

---

The Transform() function is used to perform calculations on the input columns: Column 1 + Column 2, Column 1 * Column 3, and Column 2 divided by Column 3. Text is provided to use in the case of calculation errors. In this example, dividing 100 by 0 causes an error to occur.

Range			Perform calculations
1	10	2	=Transform(A2:C6,”C1+C2,C1*C3,C2/C3″,”ERROR”)
2	20	2
3	30	2
4	40	2
5	50	0

Range			Output C1 + C2	Output C1 * C3	Output C2 / C3
1	10	2	11	2	5
2	20	2	22	4	10
3	30	2	33	6	15
4	40	2	44	8	20
5	50	0	55	10	ERROR

Example 2. Perform simple calculations, using an IF statement

---

The Transform() function is used to perform a calculation on the input columns. An IF statement is used in the calculation expression to test for values that are zero, to prevent the division by zero error occurring.

Range		Perform calculation
100	50	=Transform(A2:B6, “IF(C2 > 0, C1/C2, 0)”,”ERROR”)
50	2
88	4
400	20
700	0

Range		Output
100	50	2
50	2	25
88	4	22
400	20	20
700	0	0

Example 3. Use Transform to reorder columns

---

The Transform() function is used to reorder a set of columns

Range			Rearrange the columns
AAA	10	Jan	=Transform(A2:C6,”C2,C3,C1″,”ERROR”)
BBB	20	Feb
CCC	30	Mar
DDD	40	Apr
EEE	50	May

Range			Output
AAA	10	Jan	10	Jan	AAA
BBB	20	Feb	20	Feb	BBB
CCC	30	Mar	30	Mar	CCC
DDD	40	Apr	40	Apr	DDD
EEE	50	May	50	May	EEE

Example 4. Using Transform with a K Series

---

The Transform() function is used to perform a calculation on all columns in a range (in this example to divide all values by 10). This can be achieved by including a reference to every single column in the range. However, this would become problematic if there were hundreds of columns, or even an unknown number of columns (in cases where the range is output by another function).

Range				=Transform(A2:D5,”C1/10, C2/10, C3/10, C4/10″)
11	55	99	444	1.1	5.5	9.9	44.4
22	66	111	555	2.2	6.6	11.1	55.5
33	77	222	666	3.3	7.7	22.2	66.6
44	88	333	777	4.4	8.8	33.3	77.7

Using a K Series, it is possible to achieve the same result without specifying every column.

The word Columns means the total number of columns (in this example, 4 columns)

K = Series(1,Columns) denotes a series from 1 to the total number of columns (in this example 1,2,3,4)

CK/10 performs a division by 10 on all columns in the K Series.

Range				=Transform(A2:D5,”K=Series(1,Columns):CK/10″)
11	55	99	444	1.1	5.5	9.9	44.4
22	66	111	555	2.2	6.6	11.1	55.5
33	77	222	666	3.3	7.7	22.2	66.6
44	88	333	777	4.4	8.8	33.3	77.7

Example 5. Perform simple evaluations that return TRUE or FALSE

---

The Transform() function is used to perform various comparisons on the input columns in an iterative manner (row by row): whether the cost is greater than the average cost and whether cost is higher than the market value. These comparisons return boolean values.

Range					Perform comparisons
Sector	Asset	Unit Cost	Cost	Market Value
Basic Materials	CEB	100	5000	5015	=Transform(A3:E10,”C4 > Average(V4),C4 > C5″)
Basic Materials	MBD	101	600	588
Consumer Goods	QGW	99	70000	68000
Consumer Goods	QJU	96	3000	3000
Consumer Services	THT	123	4500	4515
Consumer Services	GTE	88	3600	3615
Financials	JWQ	145	24000	24000
Financials	QIF	350	50000	49988

Range					Output
Sector	Asset	Unit Cost	Cost	Market Value	Cost > Average Cost	Cost > Market Value
Basic Materials	CEB	100	5000	5015	FALSE	FALSE
Basic Materials	MBD	101	600	588	FALSE	TRUE
Consumer Goods	QGW	99	70000	68000	TRUE	TRUE
Consumer Goods	QJU	96	3000	3000	FALSE	FALSE
Consumer Services	THT	123	4500	4515	FALSE	FALSE
Consumer Services	GTE	88	3600	3615	FALSE	FALSE
Financials	JWQ	145	24000	24000	TRUE	FALSE
Financials	QIF	350	50000	49988	TRUE	TRUE

Example 6. Return close prices that are less than the ten-day rolling average

---

The FilterData() function is used to return prices that are less than the ten-day rolling average. This example uses Quintessence Row Column syntax to locate relative cell values for use in calculations on a line-by-line basis.

A TimeSeries() function is used to return a set of close prices. Another column is required that indicates whether a value is less than the ten-day rolling average price.

Return a list of close prices

=TimeSeries(“VRR”,”Close Price”,”1 Oct 2012″,”20 Oct 2012″,”p”,,,,,,”MarketDataProvider1″)

TimeSeries output
25604
25369
24684
24970
25402
25639
25626
25725
25540
26273
25457
26450
24312
26310

Use Transform, with the TimeSeries above as input, to compute the 10-day rolling averages

=Transform(TimeSeries(“VRR”,”Close Price”,”1 Oct 2012″,”20 Oct 2012″,”p”,,,,,,”MarketDataProvider1″),”AVERAGE(R(Line-9)C(1):R(Line)C(1))”)

TimeSeries output (close prices)	Transform output (10-day average)
25604
25369
24684
24970
25402
25639
25626
25725
25540	25454.8
26273	25521.7
25457	25530.5
26450	25707.1
24312	25641.3
26310	25732.1

Use FilterData, with the above TimeSeries as input, and the same expression as the Transform function above, to find all prices less than the 10-day rolling average

=FilterData(TimeSeries(“VRR”,”Close Price”,”1 Oct 2012″,”20 Oct 2012″,”p”,,,,,,”MarketDataProvider1″),”R(Line)C(1) < AVERAGE(R(Line-9)C(1):R(Line)C(1))”

TimeSeries output (close prices)	Transform output (10-day average)	FilterData output (< 10-day average)
25604		25457
25369		24312
24684
24970
25402
25639
25626
25725
25540	25454.8
26273	25521.7
25457	25530.5
26450	25707.1
24312	25641.3
26310	25732.1