Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Formula from table with operation restrictions

  1. #1
    New Member DynV's Avatar
    Join Date
    Dec 2017
    Location
    Montreal, Canada
    Posts
    19

    Formula from table with operation restrictions

    I have a mostly automated profile/sheet/information that I'd like to automate even more as it allow custom formulas using limited data and operations. In the following table the column (col) 1 is entered and col 2 is automated and I'd like to do so for col 3 (reason in previous sentence). In the custom formulas the only information related to col 3 is col 1 & 2 and operations are arithmetic (only +, -, x, /) and rounding: round(), floor(), ceil(). There is no need to figure col 2, it's only there if it helps finding the restricted formula for col 3; although if there's a way to only get it from col 1, it would be a bit more robust.
    1 2 0
    2 2 0
    3 2 0
    4 2 0
    5 3 0
    6 3 0
    7 3 0
    8 3 0
    9 4 1
    10 4 1
    11 4 1
    12 4 1
    13 5 2
    14 5 2
    15 5 2
    16 5 2
    17 6 3
    18 6 3
    19 6 3
    20 6 3

    I'm not sure if this is the right forum for this request, moderators please change it if appropriate, or those that know one to send them a suggestion to do so.

    Thank you kindly for your help

    Update 1: Added rounding operations.
    Update 2: In the sentence starting with "There is no need to figure col 2", the semi-column and its rest.
    Last edited by DynV; 01-18-2018 at 10:41 AM.

  2. #2
    Elite Member stapel's Avatar
    Join Date
    Feb 2004
    Posts
    15,849

    Cool

    Quote Originally Posted by DynV View Post
    I have a mostly automated profile/sheet/information that I'd like to automate even more as it allow custom formulas using limited data and operations. In the following table the column (col) 1 is entered and col 2 is automated and I'd like to do so for col 3 (reason in previous sentence). In the custom formulas the only information related to col 3 is col 1 & 2 and operations are arithmetic (only +, -, x, /). There is no need to figure col 2, it's only there if it helps finding the restricted formula for col 3.
    1 2 0
    2 2 0
    3 2 0
    4 2 0
    5 3 0
    6 3 0
    7 3 0
    8 3 0
    9 4 1
    10 4 1
    11 4 1
    12 4 1
    13 5 2
    14 5 2
    15 5 2
    16 5 2
    17 6 3
    18 6 3
    19 6 3
    20 6 3
    You have a column which numbers the events (or whatever is being recorded here), a second column which may or may not receive additional data, and a third column which will be related to the first two by various arithmetical operations. And... what are you wanting to do, specifically? With what are you requesting assistance?

    Please be specific. When you reply, please include a clear statement of your thoughts and efforts so far, so we can see where things are getting bogged down. Thank you!

  3. #3
    Elite Member mmm4444bot's Avatar
    Join Date
    Oct 2005
    Location
    Seattle
    Posts
    9,347
    Are nested IF/ELSE/THEN statements available?
    "English is the most ambiguous language in the world." ~ Yours Truly, 1969

  4. #4
    Full Member
    Join Date
    Nov 2017
    Location
    Rochester, NY
    Posts
    994
    Quote Originally Posted by DynV View Post
    In the custom formulas the only information related to col 3 is col 1 & 2 and operations are arithmetic (only +, -, x, /).
    What does the division operation that is available to you produce, when the quotient is not a whole number? Does it give the exact value, or round up, or round down, or what? For example, would 3/4 be 0.75, or 1, or 0, or something else? If it is some form of integer division, then this should be possible, but probably not otherwise.

    Is it possible for you to tell us what system you are using (programming language, or whatever), and refer us to its manual so we can be sure of its limitations? That might save a lot of guessing.

  5. #5
    Elite Member
    Join Date
    Feb 2004
    Location
    Ottawa, Ontario
    Posts
    17,042
    Glad to see I'm not the only one confused!

    Problem looks familiar: did you post a similar one earlier?
    I'm just an imagination of your figment !

  6. #6
    New Member DynV's Avatar
    Join Date
    Dec 2017
    Location
    Montreal, Canada
    Posts
    19
    Quote Originally Posted by stapel View Post
    You have a column which numbers the events (or whatever is being recorded here), a second column which may or may not receive additional data, and a third column which will be related to the first two by various arithmetical operations. And... what are you wanting to do, specifically? With what are you requesting assistance?

    Please be specific.
    I thought the OP 2nd sentence (with reference to the 1st sentence for col 3) answered that question. I'll try to do this step by step. In the profile/sheet/information, a user goes row-by-row, hopefully going all the way to the end but might stop along the way, stopping would have nothing to do with the data. The use currently have to enter col 1 & 3 (as they go along, thus row-by-row), in the profile/sheet/information what's related to col 2 is automated and will always reflect what's in the table (whatever is in col 1, it will become col 2 of the corresponding row). As mentioned currently the data in profile related to col 3 has to be entered manually with the corresponding disadvantages, as it will function incorrectly if a user forget to modify it after changing the data corresponding to col 1, or do so incorrectly. I would like an algorithm to automatically modify the data related to col 3 so there's no chance of a user forgetting to change it after changing col 1; the only operations allowed are in the OP (about midway of the OP 3rd sentence).

    Quote Originally Posted by stapel View Post
    When you reply, please include a clear statement of your thoughts and efforts so far, so we can see where things are getting bogged down. Thank you!
    I have absolutely no idea how to do so, I have no skill that give me any approximation of the result.

    Quote Originally Posted by mmm4444bot View Post
    Are nested IF/ELSE/THEN statements available?
    Sadly no.

    Quote Originally Posted by Dr.Peterson View Post
    What does the division operation that is available to you produce, when the quotient is not a whole number? Does it give the exact value, or round up, or round down, or what? For example, would 3/4 be 0.75, or 1, or 0, or something else?
    It is, I forgot to mention it in the OP and made the corresponding changes to it as mentioned in its Update 1.

    Quote Originally Posted by Dr.Peterson View Post
    Is it possible for you to tell us what system you are using (programming language, or whatever), and refer us to its manual so we can be sure of its limitations? That might save a lot of guessing.
    5th Edition OGL by Roll20 - Roll20 Wiki but that's not necessary, as long as the operations are limited as I mentioned in the OP, I would make it work.

    Quote Originally Posted by Denis View Post
    Problem looks familiar: did you post a similar one earlier?
    Some weeks ago I made a similar OP but it was for a different 3rd column; the solution that was given to me works properly, so I'm hoping a different aspect (this new col 3) can be automated.

  7. #7
    Full Member
    Join Date
    Nov 2017
    Location
    Rochester, NY
    Posts
    994
    First, you can easily get column 2 (which I'll call y) from column 1 (x). It increases by 1 when x increases by 4, so the formula involves division by 4 and rounding (which is why I asked about integer division). Specifically, y = ceil(x/4) + 1.

    Apart from the first 4 rows, column 3 (z) is always 3 less than y, so it would be ceil(x/4) - 2. If you had if/else, or max or min, you could handle those first rows, e.g. as max(ceil(x/4) - 2, 0).

    So we need to find a way to simulate max using rounding. This can be done, only because (I assume) x is never greater than 20, and my w = ceil(x/4) - 2 is never more than 3. We want to take inputs of -1 through 3, and produce an output that is equal to w except that it is 0 when w is 0. Here is my idea: z = w - floor(w/4), since floor(w/4) is -1 when w = -1, but 0 otherwise.

    So here's the formula: z = (ceil(x/4) - 2) - floor(ceil(x/4) - 2), if you need to do it all at once and can't store w separately to use as input to z = w - floor(w/4).

    Sound good?

  8. #8
    New Member DynV's Avatar
    Join Date
    Dec 2017
    Location
    Montreal, Canada
    Posts
    19
    I currently don't have time to verify your formula then try it in the profile/sheet/information but should do so within 24 hrs. For now:
    Quote Originally Posted by Dr.Peterson View Post
    x is never greater than 20
    I hope your statement will become false but for it's been a while it has been correct and currently is.
    Last edited by DynV; 01-18-2018 at 03:38 PM.

  9. #9
    Full Member
    Join Date
    Nov 2017
    Location
    Rochester, NY
    Posts
    994
    Quote Originally Posted by DynV View Post
    I hope your statement will become false but for it's been a while it has been correct and currently is.
    We can only go by what you tell us. In the previous question, as I recall, you indicated that the table you showed contained all possibilities, and I supposed that is true here as well, because I needed some such assumption.

    I'm pretty sure we need some upper limit in order to solve your problem; if it is greater than 20, then we can just replace 4 with a bigger number in the calculation of z. (I actually chose a larger number than I needed as it is.)

  10. #10
    New Member DynV's Avatar
    Join Date
    Dec 2017
    Location
    Montreal, Canada
    Posts
    19
    Quote Originally Posted by Dr.Peterson View Post
    w = ceil(x/4) - 2[...]Here is my idea: z = w - floor(w/4), since floor(w/4) is -1 when w = -1, but 0 otherwise.
    So here's the formula: z = (ceil(x/4) - 2) - floor(ceil(x/4) - 2), if you need to do it all at once and can't store w separately to use as input to z = w - floor(w/4).?
    One cannot store a value using the profile/sheet/information, but one can make a "sub-formula", as in if something is repeated sufficiently enough to put part of it in another one that will basically do--exactly--the same thing but will just be prettier; ie: those will behave exactly the same, the formula has absolutely no sense, I made stuff up that contained a similar part multiple times

    1. formula1 = round(y*3+1) x round(y*3+1) - round(y*3+1) / 10 - round(y*3+1) / 4
    2. formula1a = round(y*3+1) ; formula1 = formula1a x formula1a - formula1a / 10 - formula1a / 4


    Unless it's pretty hard to do without a "sub-formula", I'd prefer it to be in a single formula. Perhaps it's me being bad (very) at math or I'm tired but wouldn't the single formula be z = (ceil(x/4)-2) - floor( (ceil(x/4)-2) /4) ? I took z = w - floor(w/4) then S&R (search & replace) w by ceil(x/4)-2 then changed the spacing.

    Quote Originally Posted by Dr.Peterson View Post
    In the previous question, as I recall, you indicated that the table you showed contained all possibilities, and I supposed that is true here as well, because I needed some such assumption.
    Highlight by me. In my previous reply (post #8) I was trying to convey it's not an--impossibility--but it's unlikely (very IMO), especially considering the update on dnd 5e - Are characters limited to 20th level in 5e? - Role-playing Games Stack Exchange. Sorry about the confusion.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •