 |
|
|
|
|
|
|
Gauges & Progress Meters
Microsoft Excel - From Beginner to Expert in 6 Hours
/ EXCEL DASHBOARD REPORTS
Crystal Xcelsius Gauges & Progress Meters Dynamic Maximum Limit
Gauges Without Limits
How to give your gauge a dynamic maximum limit
By Ryan Goodman, Product Consultant, Business Objects
Introduction
This article will show how you can give gauges, progress bars and other Crystal Xcelsius �single value� components a dynamic maximum limit � that is, a maximum value that will automatically adjust to fit the possible range of values in the underlying data. This is important, for example, when you are creating a visual �what-if� analysis and there is no pre-defined maximum for a particular data value you want to display.
Problem
Crystal Xcelsius designers often use single value components, such as gauges or progress bars, to show visually the change in a certain data value when some underlying assumptions are modified For example: a gauge can show me how much money I�ve spent on advertising during a month, and how much more I�m able to spend before going over budget. Part of configuring a single value component in Crystal Xcelsius is setting its maximum value in the properties panel (i.e. I have a monthly marketing budget of $5,000). In most cases, the designer would link the maximum value of the gauge to the cell containing a static number that represents that limit..
However, how do you handle it when there is no absolute maximum value, but rather a large range of possible values that might be displayed? Using a standard single value component (such as a gauge) to display a data value that might exceed the pre-defined maximum value could result in an improperly functioning model.For example, click on Products 2 or 4 below. The gauge indicates 10,000 production units for each. However, the actual units produced were 1,000,000 and 800,000. Since these values exceeded the pre-defined maximum, they couldn�t be displayed by the gauge.
Solution
What we need is a gauge (or other single value component) with a dynamic maximum value, that will increase in keeping with the actual value of the underlying data. This allows for a gauge or progress bar to more effectively display the true data values.By combing the power of three Excel functions (LEN, POWER, and ROUND), you can easilly create a single value component with a dynamic maximum value.
LEN Function
The LEN function returns the number of characters in a text string. In other words, it simply counts how many characters (numbers or letters) are present within a specific cell.
To use the LEN function, enter the following syntax into a cell:
Syntax:LEN(text)Text is the text or cell in which you want to count the number of characters.
Cell B3 formula: =LEN(A3) Expected result is �4�
In this example we are counting how many characters are present in cell A3. In this case, there are four.
POWER Function
The POWER function returns the result of a number raised to a power.
To use the POWER function, enter the following syntax into a cell:
Syntax:POWER( number,power) or number^power
Number is the base number. It can be any real number.
Power is the exponent to which the base number is raised.
Cell C3 formula: =POWER(10,B3) Expected Result (1000)
In this example, we are simply taking the number 10, then multiplying it exponentially by the number present in cell B3 (�4�). In other words, we are calculating: 10^B3 or 10^4 or 10x10x10x10. This calculation will ensure that the data value in C3 is always larger than the data value in cell A3.
Now, let�s take this one step further and combine the two formulas.
Combining LEN and POWER
With an understanding of how the LEN and POWER functions work, it is now time to combine the two into a single cell.
To combine the LEN and POWER functions, enter the following syntax into a cell:
Syntax: =POWER(number,LEN(text))
Cell B7 formula: =POWER(10,LEN(A7)) Expected Result is �1000�
In this example we have combined the two functions to get the same value. We are calculating 10^the number of characters in A7.
At this point you could link the gauge�s maximum value to cell B7, but only if you are working with whole numbers. Let�s see how we can do this so it applies to all numbers.
Non-Whole numbers
The combination of the POWER and LEN functions will work properly only if your data contains entirely whole numbers. You will run into issues when working with values containing decimals. Let�s analyze the scenario below:
The maximum value (B3) is too large because the LEN function is including the numbers to the right of the decimal in cell (A3) in its count. In this case, the LEN function is counting six text characters in cell A3, and returns the result of 1000000. You will now learn how to combat this issue by using the ROUND function.
ROUND
To use the ROUND function, enter the following syntax into a cell:
Syntax:ROUND(number ,number of digits)
Cell B7 formula =ROUND(A7,0) Expected Result is �3000�
In this example, you are simply rounding the all values right of the decimal to the nearest whole number. This conversion to a whole number lets you calculate a more relevant maximum value using our combined LEN and POWER function. The final function looks like this: Cell C7 formula: =POWER(10,LEN(B7)). And this is the cell you want to link the gauge�s maximum value to in Crystal Xcelsius.
NOTE: using cell formatting in Excel will not make a difference, as Crystal Xcelsius reads the entire value present in a cell no matter how it is formatted.
Using the ROUND function is important when there are any non-whole numbers in your visual model. It�s always a good practice to use this formula for determining the maximum value for a single value component.
Conclusion
Now, with this new trick in your Crystal Xcelsius �knowledge base� you will be able to create visual �what-if� analysis models using single value components that can more accurately display data values that might span a large range. This is one more example of how you can easily leverage simple Excel functions to create powerful logic within your Crystal Xcelsius projects.
Ryan Goodman is a Product Consultant at Business Objects and provides post sales training and consulting to existing customers. As a Crystal Xcelsius power user, Ryan collaborated on special projects as a previous Crystal Xcelsius customer. He leverages his customer insight along with his experience in data visualization and dashboard deployment to assist customers in maximizing their value from Crystal Xcelsius.
A free Crystal Xcelsius Trial is available for download. The trial is fully with the exception that you cannot save your work. You can also find additional example models, tutorials, and quick start guides to help you learn the program at the Crystal Xcelsius Learning CenterSee Also: Dashboards, Graphs, Charts, Financial Calculators of Crystal Xcelsius
Back to: Trading Software | Excel Add-ins | Excel Templates | Software Categories |
Search all Software
Software
Categories
Search
Software
Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software
