All or nothing

Recommended Posts

All or nothing

Do you design Excel solutions with calculations that depend on complicated business rules? If so, the "all or nothing" trick might help make your formulas easier to read and maintain.

Here's how it works: suppose you've got a simple SUM formula that totals the values in a given range, such as =Sum(A2:A100). You want to add certain values to that total, but only when certain conditions are true.

To illustrate, suppose your business rules state that you must add 10 to the total if the value in cell D10 is greater than 100; you must add an additional 15 to the total if the value in cell E10 is greater than 200, and you must add another 20 to the total if the value in cell F10 is greater than 300.

Instead of using a typical IF test, try using logical expressions instead. A sample formula might look like this: =Sum(A2:A100)+10*(D10>100)+15*(E10>200)+20*(F10>300).

If the value in cell D10 is 175, the expression (D10>100) evaluates to true, or one. That one is multiplied by 10 and added to the result from the SUM function. If cell D10 contains a value of 98, the expression (D10>100) evaluates to zero. Since zero times 10 is zero, that part of the equation always evaluates to either 10 or zero.

When the values in cells D10, E10, and F10 are too low, their logical expressions evaluate to zero. At the other extreme, when the values in these cells are large enough, then all three logical expressions evaluate to true. When that happens, 10 is added to the total for D10, 15 is added for E10, and 20 is added for F10. This adds a grand total of 45 to the sum of the range A2:1200.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.