Nested Functions

Using a function as one of the arguments in a formula that itself uses a function is called nesting, and we’ll refer to that function as a nested function.

The IF function in GoFormz can be nested, when you have multiple conditions to meet. The false value is being replaced by another IF function to make a further test.

For example, by nesting the AVERAGE and SUM function in the arguments of the IF function, the following formula sums a set of numbers SUM([Table]![1][1]:[1][4]) only if the average of another set of numbers AVERAGE([Table]![2][1]:[2][4]) is greater than 50. Otherwise, it returns 0.

=IF(AVERAGE([Table]![2][1]:[2][4])>50, SUM([Table]![1][1]:[1][4]), "0")

Examples

Field Value	Formula	Description	Result
Field 1: 45	=IF([Field 1]>89, "A", IF([Field 1]>79, "B", IF([Field 1]>69, "C", IF([Field 1]>59, "D", "F"))))	Uses nested IF conditions to assign a letter grade to the score in "Field 1".	F
Field 2: 90	=IF([Field 2]>89, "A", IF([Field 2]>79, "B", IF([Field 2]>69, "C", IF([Field 2]>59, "D", "F"))))	Uses nested IF conditions to assign a letter grade to the score in "Field 2".	A
Field 3: 78	=IF([Field 3]>89, "A", IF([Field 3]>79, "B", IF([Field 3]>69, "C", IF([Field 3]>59, "D", "F"))))	Uses nested IF conditions to assign a letter grade to the score in "Field 3".	C

Question: In GoFormz, I need a formula for the following:

IF [Field 1] = PRADIP then value will be 100
IF [Field 1] = PRAVIN then value will be 200
IF [Field 1] = PARTHA then value will be 300
IF [Field 1] = PAVAN then value will be 400

Answer: You can write an IF statement as follows:

=IF([Field 1]="PRADIP",100, IF([Field 1]="PRAVIN",200, IF([Field 1]="PARTHA",300, IF([Field 1]="PAVAN",400,""))))

Question: In GoFormz, I need a formula that does the following:

IF [Field 1] + [Field 2] <= 4, return $20
IF [Field 1] + [Field 2] > 4 but <= 9, return $35
IF [Field 1] + [Field 2] > 9 but <= 14, return $50
IF [Field 1] + [Field 2] > 15, return $75

Answer: You can write a nested IF statement that uses the AND function as follows:

=IF(([Field 1] + [Field 2])<=4, "20", IF(AND(([Field 1] + [Field 2])>4, ([Field 1] + [Field 2])<=9), "35", IF(AND(([Field 1] + [Field 2])>9, ([Field 1] + [Field 2])<=14), "50", "75")))

Question: I have read your piece on nested IFs in GoFormz, but I still cannot work out what is wrong with my formula. Would you be able to help? Here is what I have:

=IF(63<=[Field 1]<80, 1, IF(80<=[Field 1]<95, 2, IF([Field 1]=>95, 3, 0)))

Answer: The simplest way to write your nested IF statement based on the logic you describe above is:

=IF([Field 1]>=95, 3, IF([Field 1]>=80, 2, IF([Field 1]>=63, 1, 0)))

This formula will do the following:

If [Field 1] >= 95, the formula will return 3 (first IF function)
If [Field 1] < 95 and [Field 1] >= 80, the formula will return 2 (second IF function)
If [Field 1] < 80 and [Field 1] >= 63, the formula will return 1 (third IF function)
If [Field 1] < 63, the formula will return 0