Hello,
it is possible to create a question with limitied values in combination of text and numbers.
For example a yuestion where it is only possible to enter numbers from 0, 5, 10 ,15.....100 (in 5 steps) and/or "+"?
Thanks regards
Hi @Maggi,
You can use the mod operator to check the remainder of the number entered. For the example you gave, you may use mod 5 to check that it equates to zero.
Use
. mod 5 = 0
under constraint.
1 Like
Thank you for your prompt answer. But I donnot understand how to integrate(paste it into the form, If I do this there will be a error. So I attached the question and what I want is to limit the answers to 0,5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100, and also +
So I tried it before with range widget and as paramter start=0;end=100;step=5.
This worls also, but I can only select the number values not also a text like "+". So I search a solution that I can combinate the numbers in 5 steps and the text symbol "+" as alternativ entry and at the end the values of the fields with numbers shoul calculate as it is in my example.
Or would it a solution to create a extra text question for every rnage question as alternativ values?
Thanks and regards
Maggi
mst_project-example.xlsx (12.3 KB)
Hi @Maggi
Using the suggestion of @Yakub you need to only put
. mod 5 = 0
in the constraint.
I've changed the second variable to a select_one, so you can select 0 to 100 or "+", but if you select "+" you will have problem with the sum at the end, so you will need to add a check for that.
mst_project-example.xlsx (14.8 KB)
@nmambre: THX for the help.
This works good. The problem is for me, that I cannot code a "Check" at the calculate sum at the end, thats to much for me...... Maybe someone has a solution to me......
Thanks
@Maggi what does work? The mod or select_one?
If you use the select_one instead of integer for all the variables, what do you expect to sum if the user selects "+".
Give a clear example of the inputs and the expected output and maybe someone can help.
Maybe add hint in english or a note in english in the example file with what you need. I didn't touch the other calculates because I don't understand what you're doing there.
Hinmambre,
Sorry so I change now all from integer to select one and the calculation works. It shows at the end the result (sum) of all values that entered in the different fields. Also the calculation if the sum is <100 or >100 works correct. Only if I select "+" (not a number iin one of the fields) the result of the calculation (sum and the hint that the values are <100 or >100) would not displayed anymore than. The calculation for the hint if the sum is greater than or lower than 100 is at line 17 and 18. The calculation of the sum is at Cell I17 with the addition that no entry is a"0" and would also accepted with no entry.
Thanks
mst_project-example.xlsx (15.4 KB)
OK, @Maggi so if i understand, it works with numbers, but fail with "+"?
So my question is still, what is the expected value?
If you have the below values as an example, what do you expect the result of cell I14 to be? and cell E17 and E18?
megalithal_stufen = 5
makrolithal_stufen = "+"
mesolithal_stufen = 0
mikrolithal_stufen = 0
akal_stufen = 0
psammopelal_stufen = 0
argyllal_stufen = 0
technolithal_stufen = 0
technolithal2_stufen = 0
summe_substrate_min = 0
Hi nmambre: yes if also a + is in one of the group of fields the calculation does not display the text and the sum of all fields. All fields at the end should have a sum of 100 for the sum of all. If the user select more than 100 in all fields or less than 100 in line 17 annd 18 it will displayed with a text. If the sum of all fields is 100 not. the actual calculation, if a user select the questions and fill it out, will dispolayed in cell I17 so the user can see, what sum is actual. In addition it should also be possible to enter a + instead of a value.
At my post here: Repeat Function map and Picture and calculate - #3 by Maggi
I got the hint because the sum was only displayed after all fields was filled out, this is why i integrate the cell I14 it will displayed direct after a value was entered.
The sum of row 14 will displayed in C 16. and if it is less than 100 than the text of C 18 will displayed and if it higher than 100 the text of C 17 will displayed.
Tanks
Sorry, but I don't understand. Your explanation may seem obvious to you, but I don't know what you want with the form, so I need an explanation for a 5 year old...
Don't worry about formulas for the moment, just create a copy of your excel, fill in some user values and then manually enter the values in the red cells, or write in the red cells what you want to happen. If the values that I've put 5, "+", 0, 0, etc don't make sense, fill in other values. Feel free to create different screenshots with different scenarios.
Iam sorry but my english to explain is not the best. I attach a excel sheet and filled the values into the label column. And write to each field a commentar to explain. Sorry but I will be back at sunday........
Thanks
mst_project-example-test.xlsx (18.7 KB)
@Maggi.
If you use the mod option, you would also need to add the min/max limits to the constraint: ... and . >= 0 and . <= 100. If your number field is optional, you would need to include '' (empty) as valid in the constraint.
You can use type integer (or text with appearance number). If you want to restrict the valid characters further, e.g. only "+" (but not "-") you would need to add a regex constraint too.
The select type proposal may be preferable as you can directly control the entry instead of a constraint check/message afterwards.
@Maggi, can you explain, please, what is the meaning of the choice
and/or "+"
From the file, I think you want the sum to just ignore the "+" sign.
You now have
coalesce(${megalithal_stufen}, '0') + coalesce(${makrolithal_stufen} , '0') + ...
I think it could be if the variable is "+" then use '0', else use coalesce(...):
if(${megalithal_stufen} = "+", '0', coalesce(${megalithal_stufen}, '0')) + if(${makrolithal_stufen} = "+", '0', coalesce(${makrolithal_stufen} , '0')) + ...
(check if() documentation)
Also, I think E17 and E18 can be empty (required column), I think you only need L17 and L18 (relevant)
Hi, thank you for your answers...
@wroos: the + mean that it is not so much to identify as a % stage, like beetween 0-5% it is only "available".
@nmambre : Thanks for the hint to the" if" calculation, I integrate it now, but at the moment there is no difference. The calculation works if I enter only numbers and shows at the end the actual value.... see attached pictures
if it to less with the text that it is not 100 and if I enter exact 100, than only the text that is 100 . Thats okay.
But if I enter also the "+" no text is visible anymore. see
attached also the actual file:
mst_project-example-test.xlsx (18.7 KB)
Thanks for help
@Maggi
seems ${megalithal_stufen} = "+" is not working
I changed (only the 1st one) with contains(${megalithal_stufen},’+’)
contains() documentation
I think it should work now, but not sure if we introduced more complexity that is not needed. So maybe someone else will have a better solution.
mst_project-example-test.xlsx (16.9 KB)
@nmambre thanks for help, I changed all"+" to '+' but it is the same, after I enter an + the sum and text would not show anymore..... Maybe a idea, to solve it a other way?
Thanks and regards
mst_project-example-test(2).xlsx (18.0 KB)
It's not only changing to '+', you need to use the contains function each time, so each ${var} = '+' must be changed to contains(${var},'+')
Ohhh, thanks, now I understand....
Now it works perfect.......thank you so much.
Best regards
Maggi
A basic problem is that you use a strange choice naming: '+'. So, your choice name is '+', not +. You should better use something like plus here for this choice name. In general, also for choices, preferably follow the rules for variable names, please, see https://xlsform.org/en/#setting-up-your-worksheets
The name column specifies the unique variable name for that entry. No two entries can have the same name. Names have to start with a letter or an underscore. Names can only contain letters, digits, hyphens, underscores, and periods. Names are case-sensitive.
If you change this choice name to plus, the original solution of nmambre (Defined combination of numbers and text - #13 by nmambre), should work, with just adapting: if(${megalithal_stufen} = "plus", '0', ...