Index » Products » Formula Render (fxRender) »

How about supporting functions like ABS as EXP in a better way than just as a function name.

$ahbanen\′s Photo$

11 Oct 10, 7:37AM

Deedee, CodeCogs,

One could say that if these would indicate a consecutive order of the cells B10 to C20 that would be a usable suggestion. But in fact B10:C20 is a matrix, so the summation should reflect that. Also I don't think it is necessary that the rendered math expression should directly reflect cell references. On could say that the summation takes place of a section of a larger matrix or as an independent matrix which would require changing the range reference B10:C20 to a relative one like R[0]C[0]:R[10]C[1] and use the indices of this:

E.g. (as section of the larger sheet matrix) SUM( B10:C20 )

$\sum_{i=10,j=2}^{i=20,j=3} S_{i,j}$

(1)

or (as an independent relative matrix) SUM( R[0]C[0]:R[10]C[1] )

$\sum_{i=0,j=0}^{i=10,j=1} R_{i,j}}$

(2)

What I would however like to propose is to use the first column indicator of the range as the identifier of the matrix, i.e. when the range A1:C10 would be used an A, when B10:C20 would be used a B and so on, to allow the user some easy versatility to set the matrix id.

I.e. SUM( B10:C20 )

$\sum_{i=10,j=2}^{i=20,j=3} B_{i,j}$$

(3)

After all IMHO we should think of this feature as a simplified way to enter math expressions using a spreadsheet formula syntax and not as a way to rewrite spreadsheet formulas as if they were math expressions.

@CodeCogs,

While taking the weekend to think this over - which allowed me to avoid ugly suggestions like ${\color{red} \sum_{i=10,j=B}^{i=20,j=C} A_{i,j}}$ or an abomination like (brrr!) ${\color{red} \sum_{i=10,j=B}^{i=20,j=C} J_{i}}$ - this also gave me time to consider the fact that a SUM or PRODUCT is in fact a special function and that in essence an range like B10:C20 should be treated like a (independent relative) matrix:

$\begin{bmatrix} R_{0,0} & \cdots & R_{0,1} \\ \vdots & \ddots & \vdots \\ R_{10,0} & \cdots & R_{10,1} \end{bmatrix}$

(4)

So SUM( B10:C20 ) would in essence be equivalent to (this time as sub-matrix of the larger sheet matrix):

$\sum \begin{pmatrix} S_{10,2} & \cdots & S_{10,3} \\ \vdots & \ddots & \vdots \\ S_{20,2} & \cdots & S_{20,3} \end{pmatrix}$

(5)

the latter which the can be abbreviated to the earlier proposed

$\sum_{i=10,j=2}^{i=20,j=3} S_{i,j}$

(6)

By allowing range to be depicted as matrices it would of course also become possible to think about renderings for MDETERM, MINVERSE and MMULT.

Last but not least: there should of course also be renderings for functions like AND, OR and NOT. And perhaps for ROUND, FLOOR and CEILING. And even perhaps for the concatenate operator (&). And like that there are even more (AVERAGE, MEAN, etc., etc.). (If you think it would help to make some suggestions for these please say so.)

And - with the chance you will end rolling on the floor laughing because it is such a wild idea - perhaps the automated rendering of specialized financial and statistical functions should be considered. Which would IMHO lead to better understanding of such functions but also to a cheer of those who are using these functions often and would like to document reports with the more impressive renderings