Revelwood

Your SUPER-powered WP Engine Site

excel tips

IBM Planning Analytics Tips & Tricks: Excel TEXT Function

by

Have you ever merged text and a calculated number into a cell and saw that the format of the number was hard to read? The TEXT function in Excel lets you convert the number into a more professional format.

The syntax of the TEXT function is:

=TEXT(value,format_text)

  • Value: the number to format
  • Format_text: the format to apply

The example below shows the basic use of TEXT where the text “Rent amount is” has been appended to the amount in C2. The use of “General” as the second parameter indicates no specific formatting has been applied.

IBM Planning Analytics Tips & Tricks: Excel Text

To apply formatting, you can replace the second parameter with a specific format string.

IBM Planning Analytics Tips & Tricks: Excel Text

The easiest way to find format strings is to get them from the Format Cells dialog box.

  1. In Excel, press CTRL+1
  2. Go to the Number tab
  3. Select Custom in the Category pane
  4. You can then copy the format string from the “Type” textbox
IBM Planning Analytics Tips & Tricks: Excel Text

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 Excel Tips & Tricks:

IBM Planning Analytics Tips & Tricks: Excel MAXIFS and MINIFS

IBM Planning Analytics Tips & Tricks: Excel OFFSET Function

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

Home » excel tips

IBM Planning Analytics Tips & Tricks: Manipulating Text in Excel

by Leave a Comment

Have you ever wanted to manipulate text within Excel? Excel has a series of functions that allow you to parse text strings based on whether you want to reference the left, the right, or the middle of the cell contents.

For example, assume cell A2 contains the text “Planning Analytics”. The functions LEFT and RIGHT will allow you to view characters at the start of the string or at the end of the string.

=LEFT(A2,3) will give a result of “Pla”

=RIGHT(A2,3) will give a result of “ics”

The function MID will allow you to look within the string by defining both a starting point and the number of characters in the result.

=MID(A2,3,3) will give a result of “ann”

But what if you didn’t know the number of characters? You can use functions to find a specific set of characters and to determine the overall length of the string.

=FIND(“A”,A2)-1 will give a result of “9”, which signifies that the string begins at the 9th character

=LEN(A2) will give a result of “18”, which signifies that the string contains 18 total characters

You can then put these pieces together to find a specific set of data, such as the second word in the string. Assuming that D6 contains the LEN function and D5 contains the FIND function:

=RIGHT(A2,D6-D5) will give a result of “Analytics”

These Excel functions will allow you to quickly parse out pieces of a text string which can then be used in your spreadsheets.

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 Excel tips in our IBM Planning Analytics Tips & Tricks series:

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

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

IBM Planning Analytics Tips & Tricks: Excel TYPE Function

Home » excel tips

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

by Leave a Comment

Have you ever wanted to define the number of working days between two calendar dates? Do you define “working days” simply as non-weekend days or do you also include holidays? Did you know that you can calculate this information using Excel’s NETWORKDAYS function?

The NETWORKDAYS function is used to determine the number of whole working days between start_date and end_date. Working days are defined as the exclusion of weekends, which in essence computes the number of Mondays through Fridays. The function also includes an optional parameter to exclude holidays and any other specific non-working days.

The syntax of the function is:

NETWORKDAYS (start_date, end_date, [holidays])
  • Start_date: This is required
  • End_date: This is required
  • Holidays: This is optional
    • This consists of a range of one or more dates to be excluded from the calendar. The list entails a range of cells that contains the dates.

Note: All dates are entered in date format or in serial number format

This information can be used to help calculate any/all of the following pieces of your model:

  • Benefit accruals and eligibility
  • Ratios such as “Revenue per day” or “Units per day”
  • Days remaining for end-of-service dates
  • Anything else that requires the number of days within the formula

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 Excel tips in our IBM Planning Analytics Tips & Tricks series:

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

IBM Planning Analytics Tips & Tricks: Excel TYPE Function

IBM Planning Analytics Tips & Tricks: Excel Keyboard Navigation

Home » excel tips