How to do formulas?

Hi Clifford,

thanks for your question!

Yes, using formulas is not yet documented. I just didn’t have the time yet :-/

But it’s not really difficult, and it works very similar to Excel:
A formula starts with an = and then contains a mathematical expression, in which you can also reference values from other cells, by using their cell name. Here are two basic examples:

=A1+B3

will sum the cells A1 and B3.

=AVERAGE(C3:C10)

will calculate the average value of the cells from C3 through C10.

Other math functions that can be used are

sin, sinh, arcsin, asin, arcsinh, asinh, cos, cosh, arccos, acos, arccosh, acosh, tan, tanh, arctan, atan, arctanh, atanh, sqrt, abs, ln, log, exp, floor, ceil, average, max, min, mod, pi, power, round, sum, rand_int, rand_float

Does that help?

Regards,
Tobias

P.S.: And thanks for your donation, I really appreciate it!

Awesome. Thanks. I’ll try it out.

Hi,

sure, no problem! You are very welcome! ??

Best wishes,
Tobias

P.S.: In case you haven’t, please rate TablePress here in the plugin directory. Thanks!

Ok, I tested it and have some feedback.

1. I got this error ( https://screencast.com/t/g9wHVXCHMlZ ) because I used drag-and-drop. Is there a way for TablePress to auto-update the formulas, like Excel does (unless A!1! is specified or A!1 or A1!)?
2. Can there be an option per table settings for “pretty error messages” or “full error messages” so that, in this case, it would display “Error” instead of all that error text?
3. I figured out that cell references need to be capitalized. In other words, A1 works but a1 does not work in formula references. I’d be nice if it auto-capitalized them for you like Excel or if both upper- and lower-case cell references worked.
4. The main thing I wanted to use formulas for is to create an online calculator where the front-end user/visitor inputs the values. In Excel, you would lock all cells except the ones you want the user to edit/input and the formulas would do the rest. For both GDocs and TablePress, I can only get the final output, not a user-editable cell(s). Can that be done? If not, do you have any calculator-creator suggestions?
5. I saw Editor, but it’s not the UX I’m expecting people to go through (having to click, then click, then popup modal, then click again to submit).
6. Do you have a recommended “print this table” option? Will TableTools be turned into a plugin/extension? It’d be ideal if it could have options like auto-fitting to portrait, landscape, etc., like Excel printing (e.g. fit to X x Y pages)

I really appreciate your efforts and look forward to your insights.

Hi Clifford,

thanks for your feedback on the formulas feature!

I’ll answer your questions one by one:

1. TablePress does auto-update formulas. For example, if a cell references another cell C5, and C5 moves for some reason, then that is automatically updated in the first cell. If the first cell moves, but C5 remains in the same place, nothing changes of course. I’m not sure though how you got that error from the screenshot after a drag and drop. Cell A1 is the top left cell, which says “Header”, and that’s obviously no expression or number. Also, note that fixed cells like A!1! or A1! or A!1 are not supported. This would require a much more sophisticated logic which I’m not capable of implementing right now :-/
2. No, I’d rather not offer such an option. Usually, the person editing a table (and the formulas in it) is the person who checks a table (the “Preview” is very handy for that), so that such errors can be spotted and fixed immediately, before the changes to the table go live.
3. Yes, cell references need to be capitalized, as the column labels are also capital letters only. Adding support for lowercase references would probably be possible, but I’d much rather keep things consistent.
4. This is going to be tough, but unfortunately, I have to disappoint you: TablePress tables can not be edited by a visitor, but only on its “Edit” screen. That’s simply the purpose of the plugin (showing static data, but not really offering dynamic behavior). User-editable tables are not possible, sorry.
   A calculator should probably be possible in plain JavaScript, and there is likely code available for that in the web. It might just not yet be readily usable as a WordPress plugin (at least I don’t know any calculator plugins).
5. Yes, Editor for DataTables has a different purpose/use case and will not really help you here. Also, it can not be used in TablePress at this time.
6. For the “Print” option, I’d recommend TableTools actually. In fact, TableTools is available as a TablePress Extension. Unfortunately I had to take it offline for now, as it contains an issue, due to which it can not be used right now. (This announcement about TablePress’ predecessor WP-Table Reloaded, in which I also had to remove TableTools, gives you a clue.) I hope to be able to make it available again in the near future, and it will then be available on the TablePress Extensions page on the plugin website.

Regards,
Tobias

Thank you.

Hi,

no problem at all! Sorry though that I couldn’t help with that calculator idea…

Best wishes,
Tobias

Hi Tobias,

I have problem to get =SUM(C2:C14) I get this error then:
“!ERROR! C2 does not contain a number or expression”

C2 contain 5650,95 so I don’t get the error here!

Per

Hi Per,

well, what’s the content of cell C2? According to that error message, it is not a number or math formula, so you need to fix that.

Regards,
Tobias

C2 content = 5650,95

Per

Hi Per,

you’ll need to write that as 5650.95.
The formulas do not support commas as the decimal separator right now, as they require the English number format.
I’m thinking about a solution, but it’s not trivial…

Regards,
Tobias

Found the issue: It dont accept comma need to use dot like 5650.95 etc

Per

??

Hi,

yes, that’s what I meant. Great to hear that you found that, too!

Best wishes,
Tobias

Tobias,
I am trying to get “excel formulas” to complete the following.
=SUM(B2:B20) the numbers in these cells are over 100,000. I have tried to change these to a decemal ie 100.000 or showing number in cell as 100000

I get the following error of B20 “!ERROR! B20 does not contain a number or expression”

How do i convert that so that table press will recognize the 100,000
Thanks