Chaining IF...ELSE

Lets take a look at what an IF...ELSE statement will look like in traditional spreadsheet. For example the following formula can be used to assign a grade.

=IF(C6 < 70, "F", IF(C6 < 75, "D", IF(C6 < 85, "C", IF(C6 < 95, "B", "A"))))

As you see all statements are nested and if you are reading this formula, it seems complicated. USolver offers more expressive way to write conditional statements. Though, the notation above is mathematically correct and will work, same formula can be written in the following way:

=IF(C6 < 70, "F").IF(C6 < 75, "D").IF(C6 < 85, "C").IF(C6 < 95, "B").ELSE("A")

Chaining

Chaining as a programming pattern has become popular in software engineering and especially in JavaScript. The idea behind is quite simple, if you chain another function (using dot notation), the result of previous expression will be passed into the next function as first argument. In USolsver you can chain anything. For example

=SUM(AVERAGE(7, 9), 3, 15)

Can be rewritten using chaining in the following way

=AVERAGE(7, 9).SUM(3, 15)

Or if you have a data set and want to perform a sequence of transformations you can do this in the following way:

=[1..10].sin().cos().sum()

Chaining is also useful when you query a data table. Lets say I have a data table in the cell D1 and I want to return all rows where column $B is more then 10. You can do it with chaining in the following way:

=D1(*, *).WHERE($B > 10)

IF...ELSE with AND/OR operators

In traditional spreadsheet there is no logical operators. You have to use AND(), OR(), XOR(), NOT() as functions. USolver introduces && (double ampersand) as logical AND and || (double pipe) as logical OR. Following two formulas result in the same outcome, but the second one is much easier to read.

=IF(AND(C6 < 70, C6 >= 75), "F")
=IF(C6 < 70 && C6 >= 75, "F")

=IF(OR(C6 = 1, C6 = 5), "Yes")
=IF(C6 = 1 || C6 = 5, "Yes")

For logical NOT and XOR you still need to use functions, arguably, these two functions are not as frequently used as AND and OR.