Format a value as positive or negative within Excel formula

[JennySommet]

Hello,

I am adding automated commentary at the front of my excel report. Does anyone know how disply numbers with a "+" in front of them if it positive? The negative values come out as "-" using the formula below, but I want the positive numbers to add a "+" before them. Even better if the numbers came out as red or green.

Formula ="1. UG: "&TEXT(AR16,"#%")&" vs. LY ("&TEXT(AS16,"#%")&" LW); "&TEXT(AT16,"#%")&" vs target ( "&TEXT(AU16,"0")&" AFP+) "

Current Display: 1. UG: 5% vs. LY (-2% LW); -32% vs target ( -58 AFP+)

Wanted Display: 1. UG: +5% vs. LY (-2% LW); -32% vs target ( -58 AFP+)

Thanks so much for your help!

[#2]

A formula cannot return text with different formatting for individual characters I'm afraid.

[#3 · Accepted solution]

@JennySommet 

As @JKPieterse mentioned, it is not possible to color the numbers, but you can change the number format:

="1. UG: "&TEXT(AR16, "+0%;-0%")&" vs. LY ("&TEXT(AS16, "+0%;-0%")&" LW); "&TEXT(AT16, "+0%;-0%")&" vs target ("&TEXT(AU16, "+0;-0")&" AFP+)"

[#4]

@JennySommet 

Here are two ways to achieve the desired formatting for positive and negative values in your Excel formula:

1. Using Conditional Formatting:

1. Select the cells containing the values you want to format (AR16, AS16, AT16, and AU16 in your example).
2. Go to the Home tab and click on Conditional Formatting.
3. Choose New Rule.
4. In the Select a Rule Type window, choose Format only cells that contain.
5. Under Format only cells with, select Greater than and enter 0 in the value field.
6. Click Format and choose the desired formatting for positive values. You can add a "+" sign before the number or change the font color to green.
7. Click OK twice to apply the formatting rule.
8. Repeat steps 4-7, but choose Less than and enter 0 in the value field.
9. Apply the desired formatting for negative values, like adding a "-" sign or changing the font color to red.

2. Using a Custom Formula:

1. Modify your existing formula to incorporate an IF statement that checks the value and adds the "+" sign accordingly. Here's an example:

Excel

="1. UG: "&IF(AR16>0, "+" & TEXT(AR16,"#%"), TEXT(AR16,"#%"))&" vs. LY ("&TEXT(AS16,"#%")&" LW); "&TEXT(AT16,"#%")&" vs target ( "&TEXT(AU16,"0")&" AFP+) "

This formula uses the IF function to check if the value in AR16 is greater than 0. If true, it adds a "+" sign and then formats the number using the percent format code. If false, it simply formats the number without the "+" sign.

You can replicate this structure for the other cells (AS16, AT16, and AU16) within your formula, adjusting the cell references accordingly.

Choosing the Right Method:

• Conditional formatting is a good option if you want to visually distinguish positive and negative values directly in the cells. It's also flexible and allows you to apply different formatting rules to different ranges of cells.
• Using a custom formula is preferable if you need the formatted values to be used in further calculations within your spreadsheet. It provides more control over the formatting within the formula itself.

Remember to adjust the cell references and formatting options in these examples to match your specific needs.

[#5]

Thank you!

[#6]

Thank you 🙂