You can write Excel formulas with SQL 2 Excel. There are 4 special substitution variables that you can use in your formulas:

{ROWID}    : Substituted with current row ID
{COLID}    : Substituted with current column ID
{MINROWID} : Substituted with the minimum row ID, e.g. first data row of the main query   (added in version 1.1.5)
{MAXROWID} : Substituted with the maximum row ID, e.g. last data row of the main query    (added in version 1.1.5)

For SQL 2 Excel Pro, these 4 variables are also available:

{LMAXROWID}  : Substituted with the last maximum row ID.  Used in extra cells   (added in version 2.7.7)
{LMINROWID}  : Substituted with the last minimum row ID.  Used in extra cells   (added in version 2.7.7)
{OAMAXROWID} : Substituted with the overall maximum row ID, up to this point.  
               Used in extra cells  (added in version 2.7.7)
{OAMINROWID} : Substituted with the overall minimum row ID, up to this point.  
               Used in extra cells  (added in version 2.7.7)

You can use simple expressions, like below, to reference rows or columns relative to the current.

{ROWID-1} : Will be substituted with current row ID - 1
{ROWID+1} : Will be substituted with current row ID + 1.

Here's an example illustrating how to write the SQL query:

SELECT ID, '=A{ROWID}*2' AS FORMULA
FROM jos_content

You need to specify which column(s) that does have formulas in them in the Worksheet configuration. In this case it's column 2 which will have a formula. We specify that in the Worksheet configuration, like this:

If more then one column has formulas in them, specify the columns with a comma delimted list.

The {ROWID} will be substituted with the current row ID when the cell formula is inserted into the Worksheet. The resulting spreadsheet will contain formulas, like this:

Comments (5)

Subscribe to this comment's feed

mhpbkk said:

	... hi, is it possible to insert a sum of a column into one specific cell? eg. in the example above put the sum of column 2 into cell B12...?
	report abuse vote down vote up

March 21, 2010
Votes: +0

Mats @ Joomla-R-Us said:

	... @mhpbkk : With SQL 2 Excel Pro you can add "Extra Cells" with data, formulas, text, results from SQL queries etc etc, at any place(s) in the spreadsheet, as long as the cell hasn't been written to before. You can't do it with the free version though. -Mats
	report abuse vote down vote up

March 22, 2010
Votes: -1

jeffreyd65 said:

	... I would like to do a simple calculation at the end of a column. What would the formula be for an extra cell? I tried something similar to the above : SELECT AmountUS, '=Sum{colid}' AS FORMULA FROM projdb_proj_data But that didn't work. Also tried the standarde excel formula =Sum(), but that didn't work either? Do you have a document with sample formulas other than this page? Let me know, Jeff
	report abuse vote down vote up

August 22, 2010
Votes: +0

fredhause said:

	... Help Please Formulas do not seem to work for us... We have an UNION of several SQL statements that works fine. UNTIL we put in a formula. SELECT "","zzz" AS StaffName ,"82" AS Job,"","TOTALS","","","=SUM(H2:H4" AS FORMULA,"" simply puts the plain text "=SUM(H2:H4" into the cell and not the formula.
	report abuse vote down vote up

March 05, 2011
Votes: +0

admin said:

	... @fredhouse: See above how to specify which columns contains formulas! -Mats
	report abuse vote down vote up

March 05, 2011
Votes: +0