Format a value as positive or negative within Excel formula

JennyHoA20181 

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:

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.