[TablePress] How to ignore empty cells in math formulas?

  • Resolved bdbeer

    (@bdbeer)


    9 years ago

    Hi,

    Can someone help me with the following:
    I’m trying to create a score table, where highest score, lowest score and average score are calculated by TablePress.

    A player has 12 attempts per year to score points, so the max number of scores is 12.
    If I use =max(I2:V2), all goes well. The higest value is displayed.
    If I use =min(I2:V2) and one of the cells in that range is empty, the result is 0. I want the min formula to ignore empty cells.
    If I use =average(I2:V2), then the numbers are added correctly, but they are always divided by 12, instead of the number of attempts (e.g. 2). I feel that this could also be solved by ignoring empty cells, like excel does.

    But how?

    Additionally, I’d like to create a formula that adds the highest 7 scores. e.g. a player has participated 10 times and it only takes the highest 7 scores.

    https://wordpress.org/plugins/tablepress/

Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Author TobiasBg

    (@tobiasbg)

    9 years ago

    Hi,

    thanks for your post, and sorry for the trouble.

    Sorry, this is not really possible 🙁 In formulas, empty cells are treated as “0” (which explains your results for min() and average().
    Unfortunately, I can’t think of a way to let the formulas ignore empty cells entirely 🙁

    Regards,
    Tobias

    Thread Starter bdbeer

    (@bdbeer)

    9 years ago

    Is it possible to add a function that only counts the value if x>0?

    Addif
    Countif

    That sort of thing?

    Plugin Author TobiasBg

    (@tobiasbg)

    9 years ago

    Hi,

    no, sorry, I’m not aware of a solution for that. The current mechanism for evaluating equations is only very basic.

    Regards,
    Tobias

    Thread Starter bdbeer

    (@bdbeer)

    8 years, 11 months ago

    Hi,

    Try this :’)

    =MIN(IF(I2=0,MAX(I2:V2),I2),IF(J2=0,MAX(I2:V2),J2),IF(K2=0,MAX(I2:V2),K2),IF(L2=0,MAX(I2:V2),L2),IF(M2=0,MAX(I2:V2),M2),IF(N2=0,MAX(I2:V2),N2),IF(O2=0,MAX(I2:V2),O2),IF(P2=0,MAX(I2:V2),P2),IF(Q2=0,MAX(I2:V2),Q2),IF(R2=0,MAX(I2:V2),R2),IF(S2=0,MAX(I2:V2),S2),IF(T2=0,MAX(I2:V2),T2),IF(U2=0,MAX(I2:V2),U2),IF(V2=0,MAX(I2:V2),V2))

    What it does is basically check if a cell value is 0 (empty) and if so, it returns the highest value of the same range. If not, it returns the cell value which is then inserted into a MIN function.

    It’s a bit of a dragon, but it works.

    Is this formula evaluated every time the table is requested on a page, or is it processed only when the table is saved?

    Plugin Author TobiasBg

    (@tobiasbg)

    8 years, 11 months ago

    Hi,

    wow, that is a nice “dragon” 🙂

    Formulas are indeed not evaluated on every page load (unless one has added the cache_table_output=false parameter to the Shortcode). Just like all other table content, tables are cached for 48 hours, before they are re-evaluated (and cached again).

    Regards,
    Tobias

    Thread Starter bdbeer

    (@bdbeer)

    8 years, 11 months ago

    Ok, great. Then it doesn’t place a heavy burden on my server hardware 🙂

    Perhaps this dragon is something you can use to code the MIN function that ignores empty cells?

    Plugin Author TobiasBg

    (@tobiasbg)

    8 years, 11 months ago

    Hi,

    hhm, I’m not sure, but I’ll definitely give it another look when I find better ways to extend the available formulas!

    Best wishes,
    Tobias

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘[TablePress] How to ignore empty cells in math formulas?’ is closed to new replies.