Excel Formulae Befuddlement

  • Resolved nancygraz

    (@nancygraz)


    11 months, 2 weeks ago

    Hi everyone,

    I am a new user of Ninja tables so please forgive me if I am asking a question with an obvious solution. I have been able to figure out how to configure/design most of what I need, but I am stumped by the use of Excel Formulae.

    I am working on a project where front-end users need the ability to update some cell values on a row-by-row basis (primarily Y/N responses to a series of questions,) so Ninja tables is the obvious solution to provide this functionality. I have been able to implement this on our pages – YAY!!

    Next, using these values provided by our front-end users, we then use an Excel-style IF equation to assign a numeric value based on each of these Y/N responses. The values that are assigned to these Y/N responses vary with each row and are then ultimately summed to yield an overall “score.”

    I have been trying to figure out how to use Transform Value to assign these values for the Y/N responses and am stumped. In Excel this is simple and my equation for one row would be:

            =IF(B2="Y",20,0)

    Based on the Ninja table documentation as well as the list of supported Excel functions listed on the Transform Value page, I started my testing with the following equation:

        =IF(({{row.yn}}="Y”),20,0)

    This did nothing, LITERALLY. The contents of the cell on the front end is null. I then started trying numerous equation variations but cannot get anything to work or display. I have even tried entering these equation variations into the Data Transformer field (which would apply the same criteria/equation to the entire column). The best I can sometimes get on the front end is the literal text of the actual “equation” being displayed in the cell.

    I suspect I am missing something very simple and obvious, but I am at a loss. Can someone help?

    Also, is it possible to replace the row Reference Shortcode with a specific Row ID value to point to a specific cell in a table? For example, {{104.yn}} to point to that specific cell in the table?

    Thanks in advance!

    Nancy

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Support Syed Numan

    (@annuman)

    Hello @nancygraz,

    I presume that the table includes a dropdown column with the options Y/N, which users can select. Depending on the user’s selection, you wish to either add a certain number to another cell in the column or to the same cell in the column.

    The way you used the Excel formula will not function correctly. You should use the Excel formula provided below. However, remember to check the checkbox before applying the formula in Transform Value.

        =IF("{{row.yn}}"="Y","20","0")

    In response to your second query, there is a shortcode available for a specific cell ID. Please refer to the following URL.

    https://ninjatables.com/docs/advanced-shortcode/#table-cell-info

    Thank you

    Thread Starter nancygraz

    (@nancygraz)

    Hi @annuman

    Thank you for your assistance – I now have the formula working at the column level. Hooray! One small hurdle overcome.

    However, things are a bit more complicated. Each of the rows in our table assigns a different numeric value based on the respective Y/N response for that row. For example:

    • Row 1: Y = 20; N = 0
    • Row 2: Y = 15; N = 0
    • Row 3: Y = 5; N = -5

    Is there a way to use row_id metadata in a formula to determine which row a cell is on so that the appropriate row-specific value is then placed into the cell?

    Apologies in advance for the complicated scenario/application of formulae.

    Thread Starter nancygraz

    (@nancygraz)

    I actually was able to figure out how to achieve the above functionality/conditional logic without relying on row_id metadata values.

    I added a [hidden] column to my table that serves as an index to each row. I am now able to uniquely determine which row each Value cell is on, determine the corresponding value in the Y/N column, and then set the numeric value in my Value column appropriately. The resulting nested-IF formula for this logic is very ugly, but it works!

    Thank you for all of your assistance! I greatly appreciate it!

    Nancy

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Excel Formulae Befuddlement’ is closed to new replies.