WordPress.org

Ready to get started?Download WordPress

Forums

TablePress
[resolved] Formulas in tables (46 posts)

  1. danicasati
    Member
    Posted 1 year ago #

    Hi.
    Is it possible to put FORMULAS on tables?
    I can't find information about this feature.

    http://wordpress.org/extend/plugins/tablepress/

  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 #

    Thank you in advance.
    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,

    thanks for your question.

    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

    Instead it reads,

    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.

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic

Tags