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$

ahbanen

9 Oct 10, 10:13AM

(14 replies)

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

Some suggestions/examples:

=ABS( - 1 )

$\left | -1 \right |$

(1)

=EXP(-1)

$e^{-1}$

(2)

=POWER(10;2)

$10^{2}$

(3)

=FACT(3)

$3!$

(4)

=SUM(C8:C26)

$\sum_{i=8}^{i=26}C_i$

(5)

=PRODUCT(C1:C10)

$\prod_{i=1}^{i=10}C_i$

(6)

The main idea is not to have to resort to LaTeX per se to quickly render a nice looking math expression; all these functions all are pretty straight forward related to typical text book math markup.

Note: I found the {http://codecogs.izyba.com/excel.latex?} URL is used to render the spreadsheet_formulas_to_LaTeX images. Is it allowed to use the same URL in other applications like Google Docs spreadsheets?

$CodeCogs\′s Photo$

CodeCogs

9 Oct 10, 4:33PM

Hi,

All brilliant ideas, I can implement some of these very quickly.

Regarding the Sum and Product examples, what would you suggest we do for:

=SUM(B10:C20)

As for Google Docs. Yes you can use the URL, however what you're linking to is on our test server, so once we finalise the Excel Addin (which should have been done ages ago, except for other work thats bogging us down!), this URL will officially change. The test url will stay, but I can never guarantee that it'll work always. Nevertheless you are free to use in in Google Docs etc; though we would off course appreciate (but don't insist on it) an acknowledgment in anything you publish.

$Deedee\′s Photo$

Deedee

11 Oct 10, 5:14AM

I think that the suggestions are quite good as well, and that the solution to the indexing would be to maintain the complete index as used by Excel. That is: keep the letter such that the sum goes from

$\sum _{I=B10}^{i=C20}$

(1)

. That would accurately describe what takes place in the formula.

$ahbanen\′s Photo$

ahbanen

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

$Deedee\′s Photo$

Deedee

11 Oct 10, 11:40AM

Well, I am not sure that I agree with you here.

I mostly use the formatting to ensure a readable documentation of complex formulae, and to document how and why the functions work in the context of the spreadsheet.

If I just want to produce good looking math I can still do that in the normal ways like you have done in your examples, and it could much better describe the subject with as few or as many abstractions as required by the author.

However, when I produce a nice looking representation of an Excel formula in a spreadsheet, I want it to reflect the formulae/calculation in the specific cell, not some further abstraction. The initial suggestion where you convert the Excel-functions to equivalent math-symbols is fine since it improve readability, but translating actual cell-references does IMHO not help.

$ahbanen\′s Photo$

ahbanen

11 Oct 10, 2:25PM

Deedee,

I can understand you want to see your spreadsheet formulas rendered in a way that represents what they would do in the spreadsheet. This should however not mean such formulas are rendered in a way that has no longer a relation with how in mathematics formulas are rendered.

E.g. the $A1$ reference system that is often used in spreadsheets reverses the order of row and column that is used in references to matrix elements. For that reason I also referred to the $RC$ reference system that still can be used in many (if not all) major spreadsheets. The latter reference system has a much more close relation to matrix references.

I hope you will agree that we can look at a spreadsheet/workbook sheet as a large matrix. This means that when we recognize the spreadsheet as such we also can decide to project the spreadsheet onto an equivalent matrix and still be talking about the same subject. As such one can recognize that a reference like $A2$ or $R2C1$ is fully equivalent to a reference like $S_{2,1}$ with $S$ as the sheet in our spreadsheet.

So if you say you want a nice looking representation of an Excel formula in a spreadsheet and you want to reflect the formula/calculation in a cell, is an equation like $=A1+B3*C5$ that different from $S_{1,1}+S_{3,1}*S_{5,3}$ ? With the latter formula you know it is a proper mathematical rendering of the same formula. While the former is just text in LaTeX as a markup language.

The real difference is visible when a formula like (I use one of the example from this CodeCogs section here):

=SUM(A1+B2)/SIN(x) is rendered as

$\frac{\sum {\color{blue} A1}+{\color{blue} B2}}{\sin(x)}$

(1)

In this rendering of the formula ${\color{blue} A1}+{\color{blue} B2}$ is not mathematically without a meaning but also to say at least ambiguous. And the entire formula is a pidgin rendering that neither is a spreadsheet formula nor a mathematical expression, and as such lowers the understanding of what the creator tries to express, instead of raising it. And - strictly personal - I think of this as a plain ugly rendering; I think CodeCogs consciously or unconsciously recognized this and for this reason rendered the part that was different in a different color from the rest...

We may disagree here, but I think that when one tries to render a spreadsheet formula as a mathematical expression one should make an effort to go all the way, or just not try. I feel there is much to be gained if the full effort is made, i.e. that this excellent add-in (pun intended) could stand out as an application instead of going half way and stop as something that was promising but simply wasn't pushed hard enough.

$CodeCogs\′s Photo$

CodeCogs

11 Oct 10, 7:14PM

Thanks guys, a really great discussion and some excellent ideas.

I particularly like the $S_{1,1}$ notation that Ahbanen has put forward. I am not a purist, but I can see the rational, though I can also see absolutely no reason why we shouldn't have it all.

so =SUM(B4:D56), could be rendered as:

$\sum \color{blue}B4: \color{blue}D56$

(1)

$\sum_{i=4,j=2}^{i=56,j=4} B_{i,j}$

(2)

$\sum \begin{pmatrix} S_{4,2} & \cdots & S_{56,2} \\ \vdots & \ddots & \vdots \\ S_{4,4} & \cdots & S_{56,4} \end{pmatrix}$

(3)

We'll add in an option that allows the user to select the method they want to use. In the scheme of things this is not hard to do.

However, I'm also not sure what's wrong with the Ugly format. Like Deedee, I personally trace errors using the graphical form. Having to convert $S_{3,4}$ to D3 would be painful, even if mathematically correct.

So is

$\sum_{i=4,j=B}^{i=56,j=D} S_{i,j}$

(4)

really that horrible, or perhaps a fourth option?

and what about this:

$\sum \begin{pmatrix} \color{blue}B4 & \cdots & \color{blue}D4 \\ \vdots & \ddots & \vdots \\ \color{blue}B56 & \cdots & \color{blue}D56 \end{pmatrix}$

(5)

or smaller

$\sum \left(\begin{smallmatrix} \color{blue}B4 & \cdots & \color{blue}D4 \\ \vdots & \ddots & \vdots \\ \color{blue}B56 & \cdots & \color{blue}D56 \end{smallmatrix}\right)$

(6)

Whats more difficult, is will people want the conversion style to be different in different parts of the sheet?

Incidentally, what addins are you both using? Have you tried our Excel 2007 plugin (on this page http://www.codecogs.com/pages/forums/pagegen.php?id=1850 ? Would you like to experiment with the new and improved 2003 plugin? We're looking for testers.

$Deedee\′s Photo$

Deedee

12 Oct 10, 9:01AM

Well, if it is possible to choose depending on personal taste and the particular circumstances then that would be brilliant.

I personally find:

$\sum _{i=B4}^{i=B56}i$

(1)

To be better looking (and less confusing) than

$\sum B4:B56$

(2)

and easier for the average Excel-user to grasp than

$\sum _{i=4,j=2}^{i=56,j=4}B_{i,j}$

(3)

One potentially confusing issue is the introduction of the additional variables such as $i,j$ and $B$ that may or may not figure as named cells in the sheet already.

I think the matrix abstraction $(S_{i,j})$ is nice, and that it may be useful for some situations, but for my audience I am afraid that it will require more in terms of explanations than I am willing to invest.

I see this tool as an elegant way to document (and troubleshoot) complex formulae in Excel and not as a tool for writing mathematical books or reports. For that purpose I prefer $\TeX$ or $\LaTeX$ over Excel anyway.

One of the very nice features with this Add-in is that it can render whatever you send it. If I need one nice display formula for use in Excel or perhaps Word, I can still write the formula with whatever formatting I need (if it is not too long) and copy it to wherever I want.

BTW, I often use named cells for given constants, and it is slightly annoying that grouping is not possible in the Excel name. This result in silly names like e.g. T_a_m_b to get $T_{amb}$ .

Would it be possible to allow the underscore to "switch" subscript 'on'/'off', or maybe just 'on' in the names, since subscripts usually come at the end anyway?

-BTW, I still use Excel 2003 in the office, and I would be willing to try out any improved versions as well.

$ahbanen\′s Photo$

ahbanen

12 Oct 10, 9:31AM

CodeCogs, I will try to post a better - perhaps a tiny bit formal - suggestion about how sheet references could be translated to matrix references, in a later post.

For now I would like to point out that not translating the cell references can lead to ambiguous math expressions. Take e.g. your (1) rendering: The sigma suggests a sum is to be taken, but of what? Some inner product of B5 and D56 ? (see the meaning a colon can take: http://en.wikipedia.org/wiki/Table_of_mathematical_symbols) And should B4 be interpreted as B times 4 and D56 as D times 56. Or should the cell values of these references be substituted? As the maker of the rendering you are aware you mean to indicate a spreadsheet cell range, but does any reader share the same insight.

One is of course totally free to think of the add-in as a means to personally doodle a bit with a mix of spreadsheet references and math symbols but in the end - for more serious stuff - one still needs to clarify what the renderings mean just to avoid ambiguity.

I'm not saying all this to pester you or Deedee - in fact I found Deedee's feedback much inspiring to try to formulate my suggestions better - but my main concern is with the usability of the add-in renderer. As I see it it could act as a kind of macro language for the input of LaTeX expressions, so spreadsheet formulas can be used to render valid math expressions. If you agree on this - even if not all and everything I propose can be implemented - that would be great. If however you see the add-in renderer as a toy that is intended to render math like expressions that are a hybrid between formulas and math expressions I would feel sad as I see this as a missed opportunity to create something very useful.

[If any math teachers read this I would value their feedback on this very much. Do you share the 'purist' view, or are you content with the 'hybrid' renderings?]

Addins? I don't use add-ins. I'm a Google Docs spreadsheet user and I found CodeCogs one day because the LaTeX math expression maker in the old Google Docs text editor proved to be too limited. I love the CodeCogs on-line editor since, although I hoped it would be easier to invoke the editor from the image URL (now both URLs are a bit too dissimilar)

$ahbanen\′s Photo$

ahbanen

12 Oct 10, 9:39AM

Deedee, On second thought I think I totally disagree about the

$\sum_{i=B4}^{i=B56}i$

(1)

rendering. Its meaning is totally depending on what meaning you as the creator project on it. That may be fine for personal documentation of troubleshooting but this has no longer to do with converting a spreadsheet formula in a mathematical notation format. In essence you create your own shorthand here, and while that is totally fine for personal use I feel it muddles the discussion a tiny bit.

$CodeCogs\′s Photo$

CodeCogs

12 Oct 10, 11:41AM

ahbanen. I'm a l'm a little confused about your use of URL from within Google Docs. Do you mean

http://codecogs.izyba.com/excel.latex?

is confusing relative to

http://www.codecogs.com/gif.latex?

for anything else?

We can fix that very quickly if so. In fact we can easily change the entire system.

Deedee, we can add groupings to the next release of the Excel Addin. We'll send you a copy of the new Plugin shortly.

$ahbanen\′s Photo$

ahbanen

12 Oct 10, 2:19PM

CodeCogs, Sorry about the confusion. When talking in the las paragraph of my post about URLs being dissimilar I was talking about the on-line editor and images rendered in it.

I.e. http://www.codecogs.com/latex/eqneditor.php?latex=CodeCogs%20is%20%20great for the on-line editor

versus http://latex.codecogs.com/gif.latex?CodeCogs%20is%20great for the rendered image.

When you compare both you will see that to change the URL of the image to the URL of the renderer virtually the entire URL has to be changed, which is too much to remember if you want to keep a shortcut to the editor (in Google Docs one would need to do so typically as a comment); to insert the image in Google Docs, the URL for the image would need to be copied but separately the URL for the editor would have to be extracted from the code rendered as HTML (Edit) in the CodeCogs on-line editor. It would be much easier to get from the image URL to the on-line editor if both URLs were more similar,

e.g. http://edit.codecogs.com/latex/eqneditor.php?type=gif&latex=CodeCogs%20is%20%20great for the editor

and http://image.codecogs.com/latex/eqneditor.php?type=gif&latex=CodeCogs%20is%20%20great for the image.

This way a user having the link to the image would only need to change in the URL image to edit to get to the on-line editor to make further changes. This may not be the most elegant way (and the base URL should perhaps be shortened even more to e.g. http://.codecogs.com/la/tex.php? to do away with superfluous clutter and because this would allow a longer LaTeX expression to be added; as some applications from which the URL would be invoked impose their own limit, e.g. hyperlinks invoked from Google Docs spreadsheets) but it certainly would make it much easier to edit an rendered image

$CodeCogs\′s Photo$

CodeCogs

12 Oct 10, 6:46PM

Ok, Now I understand your challenge. I'll personally play around with this later on the week and see whats possible. I think an alias from

http://edit.codecogs.com/gif.latex?sin(x)/x

to http://www.codecogs.com/latex/eqneditor.php?latex=sin(x)/x

should do.

$CodeCogs\′s Photo$

CodeCogs

25 Oct 10, 6:24PM

The url rewrite has been implemented. Such that if you use the 'edit' subdomain then you'll be moved onto the equation editor. As in

http://edit.codecogs.com/gif.latex?sin(x+y)

http://edit.codecogs.com/gif.latex?a^2+b^2=c^2

http://edit.codecogs.com/gif.latex?\sqrt(1+a)\euro .

$CodeCogs\′s Photo$

CodeCogs

24 May 11, 6:52PM

We finally have the first beta for Excel 2007/10 available.

Please try it out here: http://www.codecogs.com/products/excel_render/excel2010/install.php

Still some work to do on the instructions and a few other excel command. But I hope we've captured everything that was discussed 6 months ago in this thread.

Would welcome feedback if you have the latest version of Excel. Version for Excel 2003 in about 7 days.

Will.