Formula from table with operation restrictions

[DynV]

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.

 

[stapel]

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!

 

[mmm4444bot]

Are nested IF/ELSE/THEN statements available?

 

[Dr.Peterson]

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.

 

[DynV]

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).

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! :wink:

I have absolutely no idea how to do so, I have no skill that give me any approximation of the result.

Are nested IF/ELSE/THEN statements available?

Sadly no.

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.

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.

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.

 

[Dr.Peterson]

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?

 

[DynV]

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:

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.

 

[Dr.Peterson]

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.)

 

[DynV]

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.

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.

 

[Dr.Peterson]

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.

Yes, I probably typed too fast, or just pasted in the wrong place.

 

[DynV]

So I partly tested the formula in my last post (#10) and it doesn't work in a spreadsheet; if it can't work there, I don't see how it can work in the profile/sheet/information. I recreated only col 1 & 3 and it ends up being the same except col 3 is -1 for col 1 1-4 (so the first 4 rows different).

Attached if my test, it's a .zip and I'm not cheating as a .ods, an OpenOffice spreadsheet file actually is a zip file (of content specially made for the software). You can either rename it to the native format (.ods) or is you're using OpenOffice (I assume you could open it with other spreadsheet software), you can actually open a .zip (I didn't test that using other software).

Moderator Note: The attached .ZIP file has been uploaded to virustotal.com and has passed all security screens.

 

[Dr.Peterson]

So I partly tested the formula in my last post (#10) and it doesn't work in a spreadsheet; if it can't work there, I don't see how it can work in the profile/sheet/information. I recreated only col 1 & 3 and it ends up being the same except col 3 is -1 for col 1 1-4 (so the first 4 rows different).

I can't see the formula, just the values. But I had previously tested both the one- and two-step formulas in Excel and they worked; my version is

=(CEILING.MATH(A1/4)-2)-FLOOR.MATH((CEILING.MATH(A1/4)-2)/4)

It is possible that your ceiling function works differently; Excel's has two extra parameters that modify the function, but the defaults (as I used) give the correct behavior. The difference is in the handling of negative arguments, which would have just the effect you are showing.

I don't have time now to look into it more deeply, but I would check the definitions of the functions you used in Open Office. I'll do that when I get back.

 

[DynV]

First, I attached a .zip of multiple Excel version of a--very--slightly different version what was attached in my previous post (#12; for the conditional formatting blank check, I removed the cell absolutism (removed prepending dollar sign)) which I also included the native version. The formula is =ROUNDUP($A3/4)-2 - ROUNDDOWN( (ROUNDUP($A3/4)-2) /4) to =ROUNDUP($A22/4)-2 - ROUNDDOWN( (ROUNDUP($A22/4)-2) /4) which ROUNDDOWN() functioned correctly in my previous question (referred to in post #9), and if necessary for the other function see Documentation/How Tos/Calc: ROUNDUP function - Apache OpenOffice Wiki. I took your formula then S&R your function names and cell reference then changed spacing and it yielded =(ROUNDUP($A3/4)-2) - ROUNDDOWN( (ROUNDUP($A3/4)-2) /4), which seem to reflect the small change to your formula of removing the 1st set of parenthesis as it seemed unnecessary.

Open Office also have roundup/down functions that have extra parameters, CEILING() and FLOOR(), but not the functions I included in my formulas in this thread.

Moderator Note: The attached .ZIP file has been uploaded to virustotal.com and has passed all security screens.

 

[Dr.Peterson]

You are aware that ROUNDUP is not the same as CEILING? The difference is exactly what I was referring to: CEIL as defined mathematically rounds "toward positive infinity" -- that is, to the nearest integer greater than the given number. ROUNDUP rounds "away from zero" -- that is, it rounds positive number up, and negative numbers down. All this is stated in the documentation you referred to.

In order for the formula I gave to work, you must use the proper ceil function; since you said that function is available, I expected you to use it.

Have you tested with the real ceil (CEILING with only one parameter, in Excel or Open Office) yet? (I haven't opened your file to see what you have.)

 

[DynV]

First thing, it works. How I got to the confirmation: I made a spreadsheet and it had problems, FLOOR() seem to have a problem with either OpenOffice or the version I'm using, Apache OpenOffice, as mentioned in reply by Lupp on Jan 20 2018 7:08 am of Floor negative decimal fraction (View topic) • Apache OpenOffice Community Forum so I used INT() instead; there's no such function in the profile/sheet/information, it's only for the preliminary testing. After fixing the problem, using the fixed for the spreadsheet corrected version of Dr.Peterson (correction in post #10 of this thread), the result was as in the OP. Now I had to make sure it worked with the profile/sheet/information as otherwise I might have needed a workaround. To start implementation, the OP was the extra, the base is 1, so I added 1. I then used the corrected version of Dr.Peterson formula (post #10) then added 1 (changing -2 for -1) then changed X for the real variable, the resulting formula being ceil(@{base_level}/4)-1-floor((ceil(@{base_level}/4)-2)/4). I then made a test which preview is

and full-version should be at < link removed >

Moderator Note: The tinypic site has pop-up ads (designed to circumvent pop-up blockers) to lure visitors into clicking fake error notices, downloading who knows what. Additionally, their aggressive advertising opens background windows which play audio well after leaving the site.

and in the attached .zip; the .zip also contains the spreadsheet which contain the text version of the test result, the test was successful; to understand the test, its content is in the right sidebar, L is a diminutive for level which correspond to the OP table col(umn) 1, col 3 is (for each L) at the bottom-left section after the plus sign just above the end of Slashing. The test have things changed otherwise it would be difficult to show (it would be the # of die roll and I'd have to hover the result to show how many was rolled) and the frequency in which it's shown would be small (I'd have to trigger it on avg 20 times for each row of the OP, so I changed so each trigger so in essence each roll, is a critical hit distinguishable by the green color in the top section (of each trigger)).

Although with that test I was convinced the implementation was successful, I actually did it for a real occurrence and it works properly.

Thank you again for your help Dr.Peterson

Update 1: s/ then changed X for the real variable./then changed X for the real variable, the resulting formula being ceil(@{base_level}/4)-1-floor((ceil(@{base_level}/4)-2)/4)./

Moderator Note: The attached .ZIP file has been uploaded to virustotal.com, and it has passed all security screens.