Revelwood

Your SUPER-powered WP Engine Site

Excel

IBM Planning Analytics Tips & Tricks: The Excel FORMULATEXT Function

by Leave a Comment

Have you ever looked at a value on a report and wanted to know the formula that was used, even though you may not have access to the report? Have you ever wanted to easily include a formula as part of the report? Have you ever wanted to show people how complicated your calculation was as an attempt to get them to better appreciate you?

Well, all of this can be done in Excel with the use of a simple formula: FORMULATEXT.

The FORMULATEXT function is used to return a formula as a string. The single parameter of the function is the cell to be converted to text.

The following example shows the use of the formula in cell C6.

IBM Planning Analytics Tips & Tricks: The Excel FORMULATEXT Function

Not only can you now show your co-workers how complicated your nested-if formulas have become, but you can also end those recurring nightmares about your middle school math teachers who always said “show me your work” (ok, maybe that’s just me).

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: The Excel DATE Function

IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP

IBM Planning Analytics Tips & Tricks: Excel’s IFS Function

Home » Excel » Page 2

IBM Planning Analytics Tips & Tricks: The Excel DATE Function

by Leave a Comment

Many of you already know about Excel’s DATE function.  The function is used to create a date by defining specific values for the year, month and day.

DATE(2020,7,20) gives a result of 7/20/2020.

And some of you know that you can also use formulas to calculate a date.

If a value of 5 exists in cell C4 then

DATE(2020,7,20+C4) gives a result of 7/25/2020.

But did you know that the formula will also check for valid dates and, if needed, shift the month automatically?

DATE(2020,7,35) gives a result of 8/4/2020

(since there are not 35 days in July!)

and

DATE(2020,7,30+C4) gives the same result

Not only can this formula be used to calculate variable dates, but it will also ensure that you don’t incorrectly calculate a value that doesn’t exist on the calendar.  After all, we wouldn’t want Julius Caesar coming back to question our use of “his” month!

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more IBM Planning Analytics Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Map Charts

IBM Planning Analytics Tips & Tricks: Sparklines

IBM Planning Analytics: Learn the Excel CELL Formula

Home » Excel » Page 2

IBM Planning Analytics Tips & Tricks: Excel’s CONCAT and TEXTJOIN Functions

by Leave a Comment

Microsoft Excel has recently introduced a new version of the CONCATENATE function called CONCAT. The purpose is similar in that the new function still combines text from multiple ranges and/or strings. CONCAT will eventually replace CONCATENATE, but Microsoft has not released plans to sunset the original function to ensure compatibility with older versions of Excel.

The CONCAT function expects at least one text parameter value, with a maximum of 253 arguments. For example:

IBM Planning Analytics Tips & Tricks: CONCAT and TEXTJOIN Functions
=CONCAT(A1,B1,C1,D1,A2,B2,C2,D2)

returns “Tobeornottobe.” The new function also allows you to combine text ranges. For example:

=CONCAT(A1:D2)

returns a string consisting of the values in cells A1, B1, C1, D1, A2, B2, C2, and D2. In this case, “Tobeornottobe” will once again be returned.

Similarly, the TEXTJOIN function joins text from multiple ranges and/or strings but includes two additional parameters. These parameters allow you to specify the delimiter and determine if empty cells are ignored. For example:

=TEXTJOIN(“ ”, TRUE, A1:D2)

returns “To be or not to be,” whereas

=TEXTJOIN(“ ”, FALSE, A1:D2)

returns “To   be or not  to be.” In the last example, the spaces have been doubled wherever there are empty cells because the second parameter has been set to FALSE.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Want to get our Planning Analytics Tips & Tricks delivered to your inbox every Tuesday? Sign up to get our weekly email of just the week’s tip!

IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

IBM Planning Analytics Tips & Tricks: Recalculating Excel Worksheets

Home » Excel » Page 2

IBM Planning Analytics Tips & Tricks: Learn the Excel CELL Formula

by Leave a Comment

Excel is, and always will be, a powerful tool for the financial team. With so much power, there are many great Excel formulas that rarely get used. I recently had a situation where I wanted to easily replicate a sheet and have each sheet use an MDX expression that referenced a different dimension name. I wanted to use a formula to determine the sheet name and used the CELL function.

The CELL function returns information about a specific cell within an Excel file. The syntax of the function is

CELL( info_type, reference )

The info_type defines the type of information to be returned and reference is associated with a specific cell. Some examples of info_type include “col” to define the column, “row” to define the row, “address” to define the cell refence and “filename” to return the full path and filename of the workbook and the worksheet.

The resulting format of the info_type called filename is

path[workbook.xlsm]sheetname

I took the approach of naming the sheet with the same name as the dimension. For example, one sheet was called “Account” and another sheet was called “Company.” By using a combination of the FIND function, the MID function and the TRIM function I was able to isolate the sheet name.

=TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename"))+1,99))

I created this formula in cell B10 and then used the result within my MDX expression.

="{ TM1DRILLDOWNMEMBER( { ["&B10&"].[Orphans] }, ALL, RECURSIVE ) }"

This approach allows me to replicate an Active Form report by simply copying the entire sheet and then renaming the new sheet to be the same as the dimension name.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

IBM Planning Analytics Tips & Tricks: Recalculating Excel Worksheets

IBM Planning Analytics Tips & Tricks: Workarounds for Unsupported Excel Functions in TM1 Web

IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP

Home » Excel » Page 2

IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP

by Leave a Comment

Did you know Excel has a new feature called XLOOKUP?

For those of you who read these blogs regularly, you know that much of the focus is placed on IBM Planning Analytics Tips & Tricks.  However, I periodically also like to pass along new information about other planning and reporting aspects that impact your day-to-day tasks.

The VLOOKUP function has been around since Excel released version 1.0 in 1985. While VLOOKUP is one of the most widely used functions in Excel, there are some limitations:

  • Finding an “approximate” match
  • How to easily add columns in the search range
  • Define a search value in the middle of the range (e.g., not as the first column)
  • Starting a search at the bottom of your list without having to re-sort the data table

Microsoft recently announced the addition of a new function called XLOOKUP that is designed to simplify the lookup approach while merging functions such as VLOOKUP, HLOOKUP, INDEX and MATCH into a single function. The function is written as:

XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])

lookup value: the value you are looking for (the same as VLOOKUP)

lookup_array: the lookup column (the same as VLOOKUP)

return_array: the results column (the same as VLOOKUP)

match_mode: This is an optional parameter that determines what kind of match to find.  The options include an exact match, an exact match or the next smaller item, an exact match or the next larger item, or a wildcard search.

search_mode: This is an optional parameter that allows you to determine whether the search happens from first-to-last or from last-to-first.

This new approach will make searching easier by not having to repeatedly modify your search table while also adding new functionality for tasks such as looking up a tax rate within a range of results.

There are many instances where the inclusion of Excel functions can help further your analytics. XLOOKUP is just one example.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Home » Excel » Page 2