Using Key Excel Functions
By Ryan Goodman
This article focuses on some basic Excel spreadsheet functions you can leverage to streamline the creation of your Crystal Xcelsius visual models and spreadsheet-based dashboards. It is intended for Crystal Xcelsius users who want to familiarize themselves with conditional and lookup Excel functions.
The goal of this article is to ensure you have the best tools at your fingertips for creating visual models with Crystal Xcelsius. In it, we will look at a few simple Excel functions and show you how they work, so you can use them in your spreadsheet to develop more powerful Crystal Xcelsius models.
VLOOKUP
The VLOOKUP, or vertical lookup instructs Excel to search and return a value in any column, based on a user specified search term in the left-most column of a defined range. The most important aspect of this function is that the lookup values within the range must be in ascending order (1, 2, 3 or a,b,c).
The VLOOKUP works well in combination with Crystal Xcelsius Selector components (e.g. Menus) to lookup values based on a user’s selection.
A |
B |
C |
D |
|
1 |
West |
Central |
East |
|
2 |
Bob |
20 |
25 |
30 |
3 |
Frank |
5 |
10 |
15 |
4 |
Joe |
35 |
40 |
45 |
In this simple example, we want to see Bob’s sales in the Central Region
=VLOOKUP( “Bob”, A2:D4, 3, FALSE) (Our expected result is 25.)
= VLOOKUP( Search Term, Range, Column, Exact Match)
Search Term : “ Bob” is the specified search term that Excel is looking for in the left-most column. The search term can be a value (“Bob”), or a cell (A2).
Range : “ A2:D4” is the range of cells that Excel will perform the lookup within.
Column : “ 3” is the column containing the value Excel will lookup. In this example, since we want to see Bob’s sales in the Central region, we will use the number 3 because it is third from the left-most column in our defined range.
Exact Match : “ FALSE” will return an exact match for the VLOOKUP. If there is no match, an #N/A error will appear. If you type in “TRUE,” the closest match will be retuned. We recommend you use FALSE in most cases.
To better understand how this works, spend some time playing with the Column value and the Search Term.
HLOOKUP
The HLOOKUP function works in a similar fashion to the VLOOKUP function. HLOOKUP, or horizontal lookup instructs Excel to search and return a value in any row, based on a user specified search term in the top row of a defined range.
The HLOOKUP works great in similar scenarios as the VLOOKUP. The use for this function over the VLOOKUP is simply determined by how you set-up your data in the spreadsheet, and whether the desired output will be in rows or columns.
A |
B |
C |
D |
|
1 |
West |
Central |
East |
|
2 |
Bob |
20 |
25 |
30 |
3 |
Frank |
5 |
10 |
15 |
4 |
Joe |
35 |
40 |
45 |
In this simple example, we want to see Bob’s sales in the Central Region
=HLOOKUP( “Central”, A1:D4, 2, FALSE) (Our expected result is 25.)
= HLOOKUP( Search Term, Range, Row, Exact Match)
Search Term : “ Central” is the search tern that you are looking for in the top row. The search term can be a value (“Central”), or a cell (C1).
Range : “ A1:D4” is the range of cells that you are looking within.
Row : “ 2” is the row in your defined range of cells that contains the values you want to find. In this example, since we want to see Bob’s sales in the Central region, we will use the number 2 because it is second from the top row in our defined range.
Exact Match : “ FALSE” will return an exact match for the VLOOKUP. If there is no match, an #N/A error will appear. If you type in “TRUE,” the closest match will be retuned.We recommend you use FALSE in most cases.
To better understand how this works, spend some time playing with the Row value and the Search Term
IF Condition
An “IF” condition is a simple conditional function that can assist you in building simple logic. First, the IF statement will have you define a logical test. Then, based on whether the logical test is true or false, the function will proceed with the user defined formula or value
A |
B |
C |
D |
|
1 |
West |
Central |
East |
|
2 |
Bob |
20 |
25 |
30 |
3 |
Frank |
5 |
10 |
15 |
4 |
Joe |
35 |
40 |
45 |
For this simple example, we want to find out between Bob and Frank, who was the top seller within the West region.
= IF( B2>B3, A2, A3) (Our expected result is Bob)
=IF( Logical Test, Value if True, Value if False)
Logical Test : “ B2>B3 ” is the logical test that we are conducting, that is, the question we are asking. In this case we are asking if the value in B2 is greater than the value in B3. In this case our logical test is true, meaning the value in cell B2 (20) is greater than the value in B3 (5).
Value if True : “ A2” is the cell that Excel will use if the Logical test is true. In this case Excel will use the value within the cell A2 (Bob). You can use a cell (A2), a formula, or a value (“Bob”).
Value if False : “ A3” is the cell that Excel will use if the Logical test is false. In this case, Excel will not use this value because the statement was true. You can use a cell (A3), a formula, or a value (“Frank”).
There are multiple ways you can configure an IF statement to incorporate simple logic into an Excel worksheet. If this is a new concept to you, consider taking some time to review the MS Excel Help topic on IF statements and view some of the various configurations for more complex IF statements.
IF(AND Condition
Now, with an understanding of the simple IF condition, we will look at the AND function. The purpose of this function is to allow you to perform multiple logical tests before determining if a statement is true or false.
A |
B |
C |
D |
|
1 |
West |
Central |
East |
|
2 |
Bob |
20 |
25 |
30 |
3 |
Frank |
5 |
10 |
15 |
4 |
Joe |
35 |
40 |
45 |
For this example, we want to see if Frank’s sales were higher than Bob’s and Joe’s.
=IF(AND( B2>B3, B2>B4), "Frank is best", "Not the best")
=IF(AND ( Logical Test1, Logical Test2), Value if True, Value if False)
And Statement : The AND statement defines that there will be multiple logical tests to be performed.
Logical Test 1 : “ B2>B3” is the first logical test to be performed. In this example, we are asking if Frank’s sales in the West (B2) are higher than Bob’s (B3). We will add another logical test, so a single comma will allow for the second logical test to be entered. If this first logical test is false (Franks sales are not higher than Bobs), the “ Value if False” will be displayed. Otherwise the next logical test will be performed.
Logical Test 2 : “ B2>B4” is the second logical test to be performed. In this example we are asking if Frank’s sales in the West (B2) are higher than Joe’s (B4). If we wanted to add a third logical test, we would add another comma, then add the test. If this second logical test is false (Franks sales are not higher than Joes), the “ Value if False” will be displayed.
Value if True : “Frank is best” is the value that we will use if both logical tests are true. In this example both Logical Test1 and Logical Test2 were true, so “Frank is best” will be the cell value.
Value if False : “Not the best” is the value that we will use if either one of the logical tests are false.
CONCATENATE
The concatenate function combines characters or values from several cells together in a destination cell. You can leverage the concatenate function with other Excel formulas to create your own logic and lookup methods.
|
A |
B |
C |
D |
1 |
BobWest |
Bob |
West |
20 |
2 |
Bob |
Central |
25 |
|
3 |
Bob |
East |
30 |
|
4 |
Frank |
Central |
10 |
|
5 |
Frank |
East |
15 |
|
6 |
Frank |
West |
5 |
For this example, we will create our own unique identifiers for each line, so they can be looked up
=CONCATINATE( B1, C1) (Our desired result is BobWest in cell A1.)
=CONCATINATE( Text1, Text2)
Text1 & Text2 : B1 and C2 are concatenated together as “BobWest”. By separating the text values with a comma, you can add multiple fields together to create a single string of text. You can concatenate any combination of text and cell values.
You can also use the “&” symbol in place of “CONCATINATE” to achieve the same result. To do this, you simply insert the “&” symbol in between the two values you would like to concatenate. The above sample would be shown as “=B1&C1”
Other Examples of CONCATINATE
=CONCATINATE(“Test 1 “,B1) (Our desired Result: Test1 Bob)
=”Sales”&B2&C2 (Our desired Result: SalesBobCentral)
Conclusion
At this point you should feel comfortable with these basic lookup and conditional functions. With these new functions in your personal Excel “arsenal,” coupled with your own creativity, you will be able to create more powerful visual models and spreadsheet-based dashboards.