Compare positive and negative numbers

Tools: Function ABS

Level: advanced

Problem

Your company has a revolving credit line from its bank. It can borrow from bank up to a certain limit whenever your company needs cash. Every time your company has excess cash that exceeds one month of projected expenses, it pays this bank loan off – provided it still owes anything to the bank. You calculate or get from external source the loan balance and you have your company's cash position. However, the cash position is a positive number and loan balance is a negative number. If loan balance is zero, then there is no payment due, but if it is negative, then you pay the smaller of either your cash balance or sum needed to bring the loan balance to zero.

While it sounds and looks simple from a common sense standpoint, it is mind-twisting when it comes to setting up the math. How do you calculate what your company can pay the bank if it has to pay the bank at all?

Solution

We need to compare not cash and loan numbers but moduli of these numbers (modulus is a term for "absolute value" of the number without regard to the number sign). Then we need to select a smaller value, and this will be the payment.

1. Active cell B3.
2. Type or input =IF( function.
3. Enter ABS(B1)>ABS(B2) as "logical_test". ABS() is a function that returns the modulus of the argument.
4. If cash balance is greater than loan balance, the load balance can be paid off in full. Enter ABS(B2) as "value_if_true".
5. If cash balance is less that loan balance, cash balance goes to pay off the loan in full. Einter ABS(B1) as "value_if_false".
6. Close the bracket of IF function or press Enter, or click OK.
7. Copy cell B3 to range B3:G3.

You completed function will look as

=IF(ABS(B1)>ABS(B2),ABS(B2),ABS(B1))

Formula syntax

Similar tasks

If you have installed Analysis ToolPak, you can use a GESTEP function from Engineering collection. It compares two values A and B and checks if A>B.

In this case, your function will look as:

=IF(GESTEP(ABS(B1),ABS(B2)),ABS(B2),ABS(B1))

If you got as far as here, though, I would expect you will find the original version more obvious than this alternative.

Let formula read address name from an external cell

Tools: function INDIRECT

Level: advanced

Problem

You can take a variable out of formula into another cell and change the formula result by changing the variable in cell. What about taking a next step and feeding a formula a name instead of a value? This way, you could not only replace values with labels (see previous solution), but also feed a formula a different range name from a single cell.

Here is an example. There are three named ranges in the table, "Directors", "Associates" and "Analysts ", each spanning columns B to G and containing quarterly bonus payments for each employee group. Cell B7 shows the sum of bonus payments for all the quarter for the group which name is entered in A7. If a name changes, the sum changes.

What is the formula in B7?

Solution

We will use INDIRECT() function that converts its text in a cell or range reference.

1. Active cell B7.
2. Type =SUM(
3. Type or insert INDIRECT(A7)
4. Close bracket.

Your completed formula is =SUM(INDIRECT(A7))

Simple and works like a charm.

Formula syntax

Similar tasks

The possibilities of INDIRECT() and names are endless, especially for making dashboard. Some alternative use worth mentioning is INDIRECT() ability to convert text reference of a cell into an actual reference.

Task 1. A1 contains "B", A2 contain "1". Call a value from cell B1.

Solution.

=INDIRECT(A1&A2)

Ampersand (symbol "&") is "concatenation operator" that merges value from cell A1 "B" and value from cell A2 "1" into a text string "B1" that INDIRECT() then interprets as a reference to cell B2.

• ⇐ Назад
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 89