Formatting Text String with Percentages and Calculated Time Frame

[jski21]

Good day all,

I'm using the text string below to determine if a percentage (E27) falls between two percentages and if so, combine it with text. The text is a percentage (ChartData!C13) and a calculated time frame (ChartData!B14):

=IF(AND($E$27>=0,$E$27<=0.25),"Monthly spend to reach "&TEXT(ChartData!C13,"0%")& " ("&DATEDIF($A$2,ChartData!B14,"M")&" mnths)","-")

If E27 is between the percentages, the text message will be visible. If not, the cell will only display a dash. My conundrum is when I click in the cell with the formula, the entire formula appears. Here's the formatting on each of these cells:

E27 = Percentage
ChartDataC13! = Percentage
A2 = Date (mmmm, dd, yyyy)
ChartDataB14! = Date (*m/dd/yyyy)
Cell with this formula = Text

My assumption is the differing formats within the text string are conflicting and are gumming up the works. Any solutions or advice would be most helpful.

Cheers,

jski21

 

[Alex Blakenburg]

Change the Cell with the formula in it to General.
Then go into edit mode in the cell and just hit enter.
It should return the result of the formula.

Making a cell text will treat the formula as text instead of a formula.

 

[arthurbr]

Try one of the solutions proposed Excel Formulas Showing up as Text?

 

[jski21]

Thanks gentlemen. Thought I went through that cycle but must have missed it. There are four of these formulas (to cover everything from 0% to 100%) and the last one now show a #NUM! error:

=IF($E$27>0.8,"Monthly spend to reach "&TEXT(ChartData!C16,"0%")& " ("&DATEDIF($A$2,ChartData!B17,"M")&" mnths)","-")

 

[Alex Blakenburg]

The only thing that can cause that is if B17 is less than A2 ( and this includes if B17 is blank)

 

[jski21]

Got it. Thanks. I need to revisit the time frames on this; they're actually rolling 7 year time frames with 21 month increments for targets. Thanks for the help and direction. Very instructive!

 

[Alex Blakenburg]

No problem, glad we could help.