Formatting Text String with Percentages and Calculated Time Frame

jski21

Board Regular
Joined
Jan 2, 2019
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,722
Office Version
  1. 365
Platform
  1. Windows
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.
 

jski21

Board Regular
Joined
Jan 2, 2019
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
1,722
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

jski21

Board Regular
Joined
Jan 2, 2019
Messages
50
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,141,496
Messages
5,706,682
Members
421,464
Latest member
apicsofcake

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top