WordPress › Support » How to do formulas?

Clifford Paulick
Member
Posted 3 months ago #

The plugin description says "Tables can contain any type of data, even formulas that will be evaluated."

I didn't see any mention of formulas in FAQ, Documentation, or within the plugin settings pages.

Please help with that. I'm thinking this might be a good way to make a calculator.

Thank you.

http://wordpress.org/extend/plugins/tablepress/
TobiasBg
Member
Plugin Author
Posted 3 months ago #
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!
Clifford Paulick
Member
Posted 3 months ago #

Awesome. Thanks. I'll try it out.
TobiasBg
Member
Plugin Author
Posted 3 months ago #

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!
Clifford Paulick
Member
Posted 3 months ago #
Ok, I tested it and have some feedback.
1. I got this error ( http://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.
TobiasBg
Member
Plugin Author
Posted 3 months ago #
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
Clifford Paulick
Member
Posted 3 months ago #

Thank you.
TobiasBg
Member
Plugin Author
Posted 3 months ago #

Hi,

no problem at all! Sorry though that I couldn't help with that calculator idea...

Best wishes,
Tobias
psn
Member
Posted 3 months ago #

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
TobiasBg
Member
Plugin Author
Posted 3 months ago #

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
psn
Member
Posted 3 months ago #

C2 content = 5650,95

Per
TobiasBg
Member
Plugin Author
Posted 3 months ago #

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
psn
Member
Posted 3 months ago #

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

Per
psn
Member
Posted 3 months ago #

:-)
TobiasBg
Member
Plugin Author
Posted 3 months ago #

Hi,

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

Best wishes,
Tobias
Hockeyguy1925
Member
Posted 2 months ago #

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
TobiasBg
Member
Plugin Author
Posted 2 months ago #

Hi,

all numbers that shall be used in formulas must be written as "100000", so without any thousand separators, like the comma.

Regards,
Tobias
Hockeyguy1925
Member
Posted 2 months ago #

Thanks Tobias. All is working.
TobiasBg
Member
Plugin Author
Posted 2 months ago #

Hi,

great! That's good to hear! :-)

Best wishes,
Tobias
kpretlaw
Member
Posted 1 month ago #

Tobias,

I am just getting started and evaluating if TablePress will work for me. I have found your function capability very helpful. Can I reference data in another table in my formula?

As an example:

=[TableA A1] + [TableB A1]

Do you have any ability to look across tables when calculating a field's value?
TobiasBg
Member
Plugin Author
Posted 1 month ago #

Hi,

thanks for your post!

Referencing data from other tables is tricky, and there's only rough support for this with an extra Extension. One user played around with this a little bit. For details, please see http://wordpress.org/support/topic/calling-other-table-cell-values-from-another-table

The better approach for such cases should however be to maintain the tables in Excel and export the (calculated) tables to CSV and import those into TablePress.

Regards,
Tobias