Format Number Within Text String

[jski21]

Good morning Mr. Excel Team,

Trying to format a number within a text string using what I believe to be the correct number format. Here's the formula (number format in red):

=TEXT(ROUND(B11,0)-ROUND(B30,0),IF(B11,"#,##0;(#,##0)","0")&""&IF(B11>B30,"▲",IF(B11=B30,"●","▼")))

I need the negative numbers formatted in parentheses using a 1000 separator. (I'd prefer the negatives to be in parentheses and red, but let me get this step out of the way first). Here's an Xl2bb snapshot:

Operating-Budget-Template.xlsx
	A	B
11	Total General Administration	-
12
13	Less Salaries:
14	Salary Cost 1	250,000
15	Salary Cost 2	250,000
16	Salary Cost 3	250,000
17	Salary Cost 4	250,000
18	Salary Cost 5	250,000
19	Salary Cost 6	250,000
20	Salary Cost 7	250,000
21	Salary Cost 8	250,000
22	Salary Cost 9	250,000
23	Salary Cost 10	250,000
24	Salary Cost 11	250,000
25	Salary Cost 12	250,000
26	Salary Cost 13	250,000
27	Salary Cost 14	250,000
28	Salary Cost 15	250,000
29
30	Total Salary Costs	3,750,000
31	Salary Cap Margin	-3750000▼
Operating Budget Template

Cell Formulas
Range	Formula
B11	B11	=SUM(B7:B9)
B30	B30	=SUM(B14:B28)
B31	B31	=TEXT(ROUND(B11,0)-ROUND(B30,0),IF(B11,"#,##0;(#,##0)","0")&""&IF(B11>B30,"▲",IF(B11=B30,"●","▼")))

Cell in question is B31 and is currently displaying as -3750000▼. Huh?


Thanks in advance for the early morning (here) assistance.


jski

 

[FormR]

Cell in question is B31 and is currently displaying as -3750000▼. Huh?

Hi, it's because you only apply that format pattern if B11 is not zero. as it is zero you are using the "0" format pattern.

=TEXT(ROUND(B11,0)-ROUND(B30,0),IF(B11,"#,##0;(#,##0)","0")&""&IF(B11>B30,"▲",IF(B11=B30,"●","▼")))

 

[jski21]

Too early in the morning. Thanks FormR!

 

[FormR]

Too early in the morning

Happens to the best of us

FWIW - I think your formula can be simplified a bit to.

=TEXT(ROUND(B11,0)-ROUND(B30,0),IF(B11,"#,##0▲;(#,##0)▼;0●","0"))

 

[jski21]

I do like this simpler format. Thanks for the instruction!