# WordPress.org

## TablePress[resolved] How to do formulas? (27 posts)

1. Clifford Paulick
Member
Posted 1 year 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.

Thank you.

2. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi Clifford,

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!

3. Clifford Paulick
Member
Posted 1 year ago #

Awesome. Thanks. I'll try it out.

4. TobiasBg
Member
Plugin Author

Posted 1 year 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!

5. Clifford Paulick
Member
Posted 1 year 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)

6. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi Clifford,

thanks for your feedback on the formulas feature!

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

7. Clifford Paulick
Member
Posted 1 year ago #

Thank you.

8. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

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

Best wishes,
Tobias

9. psn
Member
Posted 1 year 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

10. TobiasBg
Member
Plugin Author

Posted 1 year 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

11. psn
Member
Posted 1 year ago #

C2 content = 5650,95

Per

12. TobiasBg
Member
Plugin Author

Posted 1 year 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

13. psn
Member
Posted 1 year ago #

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

Per

14. psn
Member
Posted 1 year ago #

:-)

15. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

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

Best wishes,
Tobias

16. Hockeyguy1925
Member
Posted 1 year 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

17. TobiasBg
Member
Plugin Author

Posted 1 year 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

18. Hockeyguy1925
Member
Posted 1 year ago #

Thanks Tobias. All is working.

19. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

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

Best wishes,
Tobias

20. kpretlaw
Member
Posted 1 year 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?

21. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

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

22. Scharfheimlich
Member
Posted 1 year ago #

Hello Tobias,
are above mentioned math formulas available with table filter (e.g. with this shortcode [table id=1 filter="user1" /])? I am not able somehow to do sum of collum for "user1".

Sincerely S.

23. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

thanks for your question, and sorry for the late answer. As I was on vacation, I didn't have a chance to reply earlier.

With such a Shortcode, the filtering would be done first, and then the formulas would be evaluated. However, as the number of rows then is different, the formula might be using the wrong number.
Do you get any error messages when trying this? Can you show or more detailed example and table?

Regards,
Tobias

24. jasond5
Member
Posted 9 months ago #

Tobias,

I am trying to calculate time elapsed. The formula below is what Excel requires for it to work. Any suggestions?

Cell A1 = 10/1/13 8:30 AM
Cell B1 = 10/1/13 9:00 PM
Formula Cell = "TEXT(B1-A1,"h:mm")"

Rgrds,
J

25. TobiasBg
Member
Plugin Author

Posted 9 months ago #

Hi,

thanks for your question, and sorry for the trouble.

Unfortunately, I have to disappoint you here :-( The math functionality in TablePress does not support date or time formats at the moment. It's just not as sophisticated as Excel here :-( Sorry.

I can only suggest that you edit this table in Excel and then import the resulting table into TablePress.

Regards,
Tobias

26. jasond5
Member
Posted 9 months ago #

Sorry If you have answered this before.

Is there a way for one of the columns to have the formula automatically added as rows are adding?

I simply want to add two values in the same row everytime. It looks like I have to manually create the formula each time a row is added.

J

27. TobiasBg
Member
Plugin Author

Posted 9 months ago #

Hi,

thanks for your question, and sorry for the trouble.

Yes, you'll have to add the formulas manually or by copy/paste.
TablePress can not add data to a table automatically like this. Sorry.

Regards,
Tobias

This topic has been closed to new replies.