WordPress.org

TablePress[resolved] Formulas in tables (46 posts)

1. danicasati
Member
Posted 1 year ago #

Hi.
Is it possible to put FORMULAS on tables?

2. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

yes, as briefly mentioned on the plugin website, you can have formulas, similar to those in Excel, in your table. I haven't yet had the time to thoroughly document this, but it's pretty much straight forward:
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.

Regards,
Tobias

3. danicasati
Member
Posted 1 year ago #

Thank you.
Any sheet with all available formulas on your website?

4. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

no, there's no such sheet yet, as I just didn't have the time yet :-( Sorry. But I like the idea, and I will try to create a list for the documentation!

However, here's a list of the available math functions, that I directly extracted from the plugin's source code. Maybe that is helpful for now.

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

Best wishes,
Tobias

5. danicasati
Member
Posted 1 year ago #

I love this plugin, I gave you 5 stars!
Keep good work!

6. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

thank you, that's very nice! I really appreciate it!

Best wishes,
Tobias

7. vhmetals
Member
Posted 1 year ago #

Tobias,

Thank you for providing the above examples. The examples are very helpful. I have tablepress on my silver website. I need to insert a more complicated mathematical formula. Based on the information you provided, it will look something like this:

=C2 x .925 x .90 x (silver spot price)

I have two issues:

1. The table does not perform multiplication. The following error is displayed:

"!ERROR! expecting operator, implicit multiplication not allowed."

2. I need the formula to calculate the current price of silver. I cannot enter a set amount (for example, 28.89). The price of silver changes constantly.

Is there a way to input the price of silver from the following code:

<object width="336" height="180">
<param name="movie" value="http://www.monex.com/ichart/inter_chart.swf" />
<embed src="http://www.monex.com/ichart/inter_chart.swf" width="336" height="180">
</embed>
</object>

The above code is from Monex.com. It provides the current price for silver, gold, platinum, and palladium.

8. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

First: No, importing the price from an external source is not possible. I suggest that you designate a special cell in the table that holds the price, which you manually update (I will assume you choose cell A4).

With that, the formula in the cell would become

``=C2*0.925*0.90*A4``

Regards,
Tobias

9. vhmetals
Member
Posted 1 year ago #

One other issue. The chart only recognizes whole numbers when computing mathematical equations. I need it to recognize decimals as well.

10. vhmetals
Member
Posted 1 year ago #

Thanks for your response and suggestion about a separate column for spot price. This may be the best temporary solution.

11. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

it will recognize decimals, but only if you use the dot `.` as the decimal separator, instead of a comma `,`. If you need the `,` for visual purposes, I suggest to create a second (hidden) column with the same data, but different decimal separators.

Regards,
Tobias

12. vhmetals
Member
Posted 1 year ago #

I am not using any commas. I only use dots. But it still show up as an error.

13. vhmetals
Member
Posted 1 year ago #

I am using the following formula to calculate silver melt value:

=C2*0.925*0.90*E2

14. vhmetals
Member
Posted 1 year ago #

!ERROR! C2 does not contain a number or expression 28.84

15. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

it looks like there's a space after the 82.11. Could you check that?
(Could catch by the way, I'll see if I can make the formula parser ignore such spaces. The best way is to not have them though.)

Regards,
Tobias

16. vhmetals
Member
Posted 1 year ago #

It works! Thank you so much. I deleted the space. Now I have two other issues. Is there a way to add a dollar sign in column D and E without throwing off the code. Also, is there a way to only display two digits after the decimal point instead of 6 digits. For example,
the final product in column D should look like this:

\$1971.40

17. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

no, adding a \$ sign is not possible, currently. You could either move the currency symbol to the table head row, or maybe create some JavaScript code, that adds the \$ sign.

Having just two digits after the decimal point can be achieved by using the `round()` function, that is mentioned above:

``=round(C2*0.925*0.90*E2,2)``

The 2 indicates that you want to digits there.

Regards,
Tobias

18. vhmetals
Member
Posted 1 year ago #

Thanks Tobias. I will try that.

19. vhmetals
Member
Posted 1 year ago #

That solved the issue. When it rounds, though, it leaves off the zero. It should read:

1971.40

1971.4

Also, is there a way to add a comma so that it reads,

1,971.40

Is there a shortcut to remove the phrase "ozt" from column C in bulk. I have 2800 entries so it would take a long time to manually remove that phrase.

20. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

ah, then try

``=round(C2*0.925*0.90*E2,3)``

It might be possible that the number indicates which digit shall be rounded.

About the comma: No, sorry :-( Same reason as for the \$ sign.

And the "ozt". There's nothing the plugin to do this, but a quick way would be to export the table to CSV, open that in a text editor, do a search&replace there, and re-import the file, replacing the existing table.

Regards,
Tobias

21. vhmetals
Member
Posted 1 year ago #

Thanks I will work on that. Appreciate all the help!

22. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

sure, no problem! You are very welcome!

Best wishes,
Tobias

23. vhmetals
Member
Posted 1 year ago #

Where can I leave a review?

24. vhmetals
Member
Posted 1 year ago #

One other question if you don't mind. Is there a way to input the formula in a bulk fashion? For example,

=round(C2*0.925*0.90*E2,0)
=round(C3*0.925*0.90*E2,0)
=round(C4*0.925*0.90*E2,0)
=round(C5*0.925*0.90*E2,0)

...All the way until C2800

I ended up using 0 so that the value would round to the nearest dollar. Looks more professional.

25. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

reviews are greatly appreciated, please see http://wordpress.org/support/view/plugin-reviews/tablepress

For the bulk adding of formulas:
Very good question... Where did you get your table from, initially? Excel? Can this maybe be automatized there, e.g. via a macro? I don't really know another easy solution right now, but I'll think about it. Maybe I can come up with something.

Regards,
Tobias

26. vhmetals
Member
Posted 1 year ago #

Thanks for the link. I will leave a review soon. My table data originated on TablePress. Should I be saving a hard copy or is my data secure on Table Press?

27. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi,

thanks, that's nice!

So, you added the data for 2800 rows in TablePress? Wow, you must have spent a lot of time with that. Good job! :-)

Yes, your data is secure, but of course it is always the better idea to have a backup! In TablePress, you should export your table to CSV and JSON format. The CSV can be opened in Excel, for example, while the JSON format will store everything in a format that TablePress understands (in case you want to move the table to another server, for example). Additionally, it is always a good idea to have a full backup of the WordPress mySQL database.

Regards,
Tobias

28. psn
Member
Posted 1 year ago #

Hi Tobias, is it possible for you to add in more functions, like TODAY(), INT? I want to calculate users age.

Per

29. TobiasBg
Member
Plugin Author

Posted 1 year ago #

Hi Per,

no, I'm sorry. The evaluation of the formulas is done by an external third-party PHP library called EvalMath. Unfortunately, I don't have the knowledge to extend that, as it uses some sophisticated parsing of math expressions, that I don't have experience with. Sorry.

Regards,
Tobias

30. Northwave
Member
Posted 1 year ago #

Hi,
is there a way to set the rounded decimals to always be 2, despite the formula outcome?

Like `=round(A1/B1,2)`, this works when there are more than two decimals in the result. It then rounds the value nicely to i.e. 1.97. But if the result is exactly 3, then it shows only 3 and not 3.00. Which would be nice.

This topic has been closed to new replies.