Excel Functions
Feature Introduction
Select and fill-in-the-blank questions can set calculation formulas on questions/options to achieve automatic filling, judgment, calculation, and default option selection.
How to Use Calculation Formulas
1. Formula Input Rules
Support Excel Functions: Replace Excel cell references with corresponding question numbers.
Example:
Excel function: SUM(A1, A3) (sums cells A1 and A3).
Questionnaire formula: SUM([q1], [q3]) (sums Questions 1 and 3).
Support System Fields, URL Parameters, and Window Object Properties (in development).
2. Fill-in-the-Blank Question Formulas
(1) Setting Calculation Logic
Click the formula icon on the right side of a fill-in-the-blank question. Choose between two logics:
• Assignment Logic: Auto-fill the text box with formula results.
• Decision-making Logic: Check answers against formulas (return 1 for valid, 0 for invalid).
(2) Key Notes
• For "Decision-making Logic," return 1 for true and 0 for false. Other settings may cause incorrect judgment.
• The current question must be included in the formula when using validation logic.
(3) Hidden Questions
If a hidden question is used in formulas, set it to "Assign value to participate in calculation."
3. Selection Question Formulas
(1) Formula Types Options
• Question Expression: Matches the entire question text to the formula result.
• Option Expression: Checks if the option's formula evaluates to true.
(2) Modify Default Options
• Allow Modification: Users can manually override auto-selected options.
• Disallow Modification: Auto-selected options cannot be changed.
Formula Scenarios
Basic Arithmetic
[q3] = SUM([q1], [q2])
Conditional Judgment
When using the "Assignment" logic, different texts can be filled in by default according to the true and false values, and the "Decision-making" logic can report a prompt for a specified wrong question, for example:
Single condition:
IF([q1] > 60, "Pass", "Fail")
Multiple conditions (AND):
IF(AND([q1] > 60, [q2] > 60, [q3] > 60), "Pass", "Fail")
Multiple conditions (OR):
IF(OR([q1] > 60, [q2] > 60, [q3] > 60), "Pass", "Fail")
Keyword Matching
IF(OR(
ISNUMBER(SEARCH("上海", [q1])),
ISNUMBER(SEARCH("北京", [q1])),
ISNUMBER(SEARCH("广州", [q1])),
ISNUMBER(SEARCH("深圳", [q1]))
), "1", "0")
ID Card Parsing
Extract Birthday: MID([q1], 7, 8)
Calculate Age: YEAR(NOW()) - MID([q1], 7, 4)
Determine Gender: IF(MOD(MID([q1], 17, 1), 2), "Male", "Female")
Score Calculation
IF(SUM([q1_1], [q1_2], [q1_3]) < 100, SUM([q1_1], [q1_2], [q1_3]), "Total exceeds 100. Adjust scores!")
Price Rounding
Round Up: ROUNDUP([q1], 0)
Round Down: ROUNDDOWN([q1], 0)
AI Assistant
Generate formulas via AI . make sure to clearly label the question numbers as 'question n'. for example: calculate the average of question 1, question 2, and question 3, and round the result to two decimal places.
Field Introduction in Formulas
Ordinary Questions: [q1], [q2]
System Fields: Select from predefined options (e.g., [Password])
URL Parameters: [url.ParameterName]
Window Properties (in development):
• Current Province: [window.curProvince]
• Screen Width: [window.outerWidth]
• Timestamp: [window.nowTime]
Notes & Precautions
Excel Function Differences
• Does not support DATEDIF(), NOW(), etc.
• Returns 0 instead of #VALUE! for invalid FIND() operations.
Best Practices
• Finalize questionnaire structure before adding formulas.
• Avoid modifying question orders after setting formulas.
FAQ
Q1: What types of calculations does the Excel Function feature support?
A: The Excel Function feature supports setting calculation formulas on multiple choice and fill-in-the-blank questions, achieving automatic filling, judgment, calculation, and default option selection. This greatly improves the intelligence and automation level of questionnaires.
Q2: How to use Excel functions in questionnaires?
A: Replace Excel cell references with corresponding question numbers. For example: Excel function SUM(A1, A3) (sums cells A1 and A3), becomes SUM([q1], [q3]) in questionnaires (sums Question 1 and Question 3).
Q3: What are the two calculation logics for fill-in-the-blank questions with Excel functions?
A: Fill-in-the-blank questions support two calculation logics: 1) Assignment Logic: Automatically fill text boxes with formula results; 2) Decision Logic: Check answers against formulas (valid returns 1, invalid returns 0). Users can choose appropriate logic based on needs.
Q4: What formula type options do multiple choice questions have with Excel functions?
A: Multiple choice questions support two formula types: 1) Question Expression: Match the entire question text with formula results; 2) Option Expression: Check if the option's formula evaluates to true. This provides flexible formula application methods.
Q5: What common application scenarios do Excel functions support?
A: Supports various application scenarios: 1) Basic arithmetic: such as [q3] = SUM([q1], [q2]); 2) Conditional judgment: such as IF([q1] > 60, "Pass", "Fail"); 3) Keyword matching: logical judgment based on text content; 4) ID card parsing: extract birthday, calculate age, determine gender; 5) Score calculation and price rounding, etc.
